How to remove ALL as a dimension member

Is there a way to remove the ALL member that Analysis Services adds to each dimension?

thanks.

In Analysis Services 2000 you can change AllLevel property of the dimension from Yes to No.

In AS2005 you to navigate to the highest level in the hierarchy in your dimension and for the corresponding attribute you change IsAggregatable property from True to False.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Thanks Ed!|||

Setting the IsAggregatable propery to True does remove the 'ALL' dimension member but now my application only receives the first member from the Dimensions Level. Is there another property that I should change in relation to this? I've messed around with some of them to no avail.

BTW I'm using AS 2005 and accessing the cube from VS2005 and the Dundas charting tool.

Any suggestions are welcome!

thx,

-Marilyn

|||

This could be limitation of the client tool accessing Analysis Services.

Try to browse your cube in the Cube Browser, you should see all members of your dimension.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

When setting the IsAggregate property to 'False' the browser only shows the first member value in the dimension. The strange thing is that the 'All' value is grayed out (which is what I want) and all the checkboxes are checked...yet I only get results for the first member.

Any thoughts on how I can disable the 'ALL' and yet still retrieve data on all of the member values that I have checked in the drop-down box?

thx,

-Marilyn

|||

What browser are you using?

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Ed,

I'm using IE V6.0 sp2 on windows xp pro sp2.

thx, Marilyn

|||

Sorry, I overlooked you using Dundas.

Try and take a look in properties of you chart control and see if you can skip the "All" level.

If you remove the "All" member from the hierarchy what is happening is that some other member becomes a default member.

The checkboxes you talk about are the way Dundas is presenting you the dimension structure. It is hard to say what logic it applies when you disable the "All" level.

The bottom line is: looks like there is little more you can do on the server side. try posting how you can tweak your chart to fit your requirements.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi,

I have a similar problem but relating specifically to Parent-Child dimensions. I wan't the ability to store data against the All member of the All level. I am not sure how to do this because the All member has no dimension key for me to use in fact table. So far I have created my own All level, which works OK except that I can now see two All levels. So now I would like to hide the default all level but I can't seem to do that on a Parent-Child dimension.

Any suggestions appreciated!

|||

I'm still having this same problem as well. It's really gotten to the point where I need to fix this. I have a demo on Friday a.m. and the customer will not like the fact that it's there!

I'm not sure what else to try. I've done everything that was suggested above.

if anyone has any additional thoughts on this please respond. I'm sure this is a problem that many would have come across.

thx,

-Marilyn

|||

I finally got past this problem. It was related to the 'level' I was using within my OLAP cube. Thanks for the suggestions I received.

-marilyn

|||Marilyn, what did you do to fix the problem?

How to remove ALL as a dimension member

Is there a way to remove the ALL member that Analysis Services adds to each dimension?

thanks.

In Analysis Services 2000 you can change AllLevel property of the dimension from Yes to No.

In AS2005 you to navigate to the highest level in the hierarchy in your dimension and for the corresponding attribute you change IsAggregatable property from True to False.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Thanks Ed!|||

Setting the IsAggregatable propery to True does remove the 'ALL' dimension member but now my application only receives the first member from the Dimensions Level. Is there another property that I should change in relation to this? I've messed around with some of them to no avail.

BTW I'm using AS 2005 and accessing the cube from VS2005 and the Dundas charting tool.

Any suggestions are welcome!

thx,

-Marilyn

|||

This could be limitation of the client tool accessing Analysis Services.

Try to browse your cube in the Cube Browser, you should see all members of your dimension.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

When setting the IsAggregate property to 'False' the browser only shows the first member value in the dimension. The strange thing is that the 'All' value is grayed out (which is what I want) and all the checkboxes are checked...yet I only get results for the first member.

Any thoughts on how I can disable the 'ALL' and yet still retrieve data on all of the member values that I have checked in the drop-down box?

thx,

-Marilyn

|||

What browser are you using?

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Ed,

I'm using IE V6.0 sp2 on windows xp pro sp2.

thx, Marilyn

|||

Sorry, I overlooked you using Dundas.

Try and take a look in properties of you chart control and see if you can skip the "All" level.

If you remove the "All" member from the hierarchy what is happening is that some other member becomes a default member.

The checkboxes you talk about are the way Dundas is presenting you the dimension structure. It is hard to say what logic it applies when you disable the "All" level.

The bottom line is: looks like there is little more you can do on the server side. try posting how you can tweak your chart to fit your requirements.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi,

I have a similar problem but relating specifically to Parent-Child dimensions. I wan't the ability to store data against the All member of the All level. I am not sure how to do this because the All member has no dimension key for me to use in fact table. So far I have created my own All level, which works OK except that I can now see two All levels. So now I would like to hide the default all level but I can't seem to do that on a Parent-Child dimension.

Any suggestions appreciated!

|||

I'm still having this same problem as well. It's really gotten to the point where I need to fix this. I have a demo on Friday a.m. and the customer will not like the fact that it's there!

I'm not sure what else to try. I've done everything that was suggested above.

if anyone has any additional thoughts on this please respond. I'm sure this is a problem that many would have come across.

thx,

-Marilyn

|||

I finally got past this problem. It was related to the 'level' I was using within my OLAP cube. Thanks for the suggestions I received.

-marilyn

|||Marilyn, what did you do to fix the problem?
sql

How to refer to the whole cube while using the filter of the cube browser?

Hello!

I've got an accounting cube with balances as measures and amongst others a time dimension. There's also a calculated member which should show the balance of the previous year. This calculated member uses the PARALLELPERIOD-function.

So far everything works fine! But while using the filter of the cube browser and selecting a certain year, I can't refer to other years than the selected year.

So how can I get the balance of the previous year?

Thanks for any help!

From the information you provided I infer that the calculated member is defined inside your Time hierarchy. The solution to your problem could be one of the following:

1. Define your calculated member in a separate dimension, you can call it [Time Series] for example.

2. Alternatively, do not put Time hierarchy into the Filter of the cube browser, put it on Rows or Columns instead, and then filter only the current year and the calculated member.

3. Instead of calculated member use a calculated measure.

|||

Hello Tigran!

Thanks for you advice! But I defined the calculated member inside the Measure Group. So I think that it's called calculated measure. Am I right? So do you know any solution in this case?

I have to use the filter of the cube browser, because I have to use this kind of filter also in the KPI's! There's no alternative.

|||

Yes, it is a calculated measure in that case.

I think the complete answer to your question would depend on what client tools you will use to display all this data (or in case of reporting website - the application suite used to create the reports). Every application can have its own method of querying for the data, including the Cube browser inside BI Development Studio.

In your particular case (using Cube Browser) try to drag your Time hierarchy in a small spot that says 'Drop Filter Fields here'. It may give you a different behavior.

If that turns out to fix the problem, I would say that the original problem is likely to be caused by Cube Browser's use of Subcubes to restrict the data to a particular slice of the cube. Alternative query method using WHERE clause of MDX may give you different results.

|||

Yes, you're right. If I drag the Time hierarchy into 'Drop Filter Fields here', the calculated measure works properly.

But as I don't know so far which client tool I'll use, I want to fix the problem caused by the filter of the cube browser.

My intention is to refer to the whole cube (and not to any slice) within the MDX statement.

Now the statement looks like that:

CREATE MEMBER CURRENTCUBE.[MEASURES].LastYearBalance

AS

(

[Measures].[Balance],

PARALLELPERIOD

(

[Time].[Hierarchy].Levels(1),

1,

[Time].[Hierarchy].CurrentMember

)

),

VISIBLE = 1;

So is there any possibility to add a statement which makes a reference to the whole cube?

I think about creating something like a Session Cube and then refering to this Session Cube within my MDX statement!

But neither I don't know how to do that within the MDX skript nor if it's possible.

|||

I wish there was such statement indeed!

Unfortunately, I don't think there is anything you can do to make your calculation statement more 'correct'. The only bulletproof solution is having a regular measure (not a calculated one), that carries last year's data. For that, you will need to modify the underlying relational database.

But I would recommend studying what client tools will be used first. Because you may find that this is not even an issue with a lot of existing applications out there (as most of them use WHERE clause for stuff you put in Filter area). For example, OWC should work just fine.

|||

Ok, there seems to be no solution which makes my calculated measure working properly.

Right now I'm testing a beta version of Excel 2007 as BI frontend tool to see how it deals with this problem.

Thank you very much for help!