Getting rid of the "All" level in query results

I have two seemingly identical cubes, one imported from AS 2000 and one
created using AMO in AS 2000 (they are both named new1 and are located in
separate databases). Yet, when issuintg the following simple query:
select {[RecvPay].[RecvPay].members} on columns from new1
I get only columns 1 and 2 on the imported cube, while I get an additional
"All RecvPay" column on the created cube. I have scoured the XMLA script
generated from each RecvPay dimension and how it is used in the new1 cube
without seeing any meaningful differences. I want to get rid of this "All
RecvPay" in my results. Am I missing something?

Thanks,
Boris Zakharin, MCAD
Metavante Risk and Compliance Solutions

You could just use select {[RecvPay].[RecvPay].Children} on columns from new1

|||I understand there are other ways to get it, but why the difference?
|||

There is an IsAggregatable property on each attribute which sets if it generates this automatic All member. If you don't want an All member in the attribute at all you can change this setting from the default of "true" to false. If you had the all member turned off in AS2000, this setting was probably kept when you migrated, if you generated the dimension in AMO without overriding the defaults, then it would automatically generate an All member.

If you only want the All member excluded from the query then there is a slight change in naming between AS2000 and AS2005. AS 2000 uses a <dimension>.<level>.members syntax, and was not truely multi-hierarchy aware. AS2005 is truely multi hierarchy aware and you need to use <dimension>.<hierarchy>.<level>.members. When you create an attritube it generates an implied single level hierarchy.

|||Thank you. I'll try it first thing tomorrow
|||

That does work in my example (and in the much more complicated originsl query this came from), but that is definitely not how the 2000 dimension was constructed since I can see "All RecvPay" when browsing that dimension. I am just wondering whether other queries might fail or produce unexpected results with this change.

|||

In this case, the difference in you queries is most likely explained by the fact that in SSAS 2005 you need to specify the heirarchy in the member/level references.

In AS 2000 [RecvPay].[RecvPay] refers to the RecvPay level of the RecvPay dimension

In AS 2005 [RecvPay].[RecvPay] refers to the RecvPay hierarchy of the RecvPay dimension

When you get the members of an entire hierarchy it includes the All member. To refer to just the level of RecvPay members (which does not include the all member) you need to use a [RecvPay].[RecvPay].[RecvPay] reference.