All Member Properties – Name, Key and Level

I just tried to find some more information about the All Member in SSAS dimension hierarchies and since it was not readily available, I had to experiment a bit, so I thought I may as well share my findings. For some these may be obvious, but for some they could as well be interesting.

So in brief, I will explore the Name, Key and Level of an All member in a dimension hierarchy. The one of choice was the Customer dimension and Customer Geography hierarchy in Adventure Works. There is an All member, called All Customers. As expected, .PROPERTIES(“MEMBER_NAME”) gives us “All Customers”:

WITH
MEMBER [Measures].[test] AS
  [Customer].[Customer Geography].CurrentMember.PROPERTIES(“MEMBER_NAME”)
SELECT
{
  [Measures].[test]
} ON 0,
{
  [Customer].[Customer Geography].Members
} ON 1
FROM [Adventure Works]

The first row shows us: All Customers.

Now, let’s see what its key is:

WITH
MEMBER [Measures].[test] AS
  [Customer].[Customer Geography].CurrentMember.PROPERTIES(“KEY”)
SELECT
{
  [Measures].[test]
} ON 0,
{
  [Customer].[Customer Geography].Members
} ON 1
FROM [Adventure Works]

This gives us 0.

And its level:

WITH
MEMBER [Measures].[test] AS
  [Customer].[Customer Geography].CurrentMember.Level.Name
SELECT
{
  [Measures].[test]
} ON 0,
{
  [Customer].[Customer Geography].Members
} ON 1
FROM [Adventure Works]

The result this time is: (All).

So far so good. Now let’s try using these to get only the All member:

SELECT
{
  [Customer].[Customer Geography].[All Customers]
} ON 0
FROM [Adventure Works]

This works. Now if we try the Key:

SELECT
{
  [Customer].[Customer Geography].&[0]
} ON 0
FROM [Adventure Works]

Interestingly, since the All member is a calculated member and has no physical key, if we try to use the one that SSAS gave us does not actually work – we get nothing on Axis 0.

Using the level works:

SELECT
{
  [Customer].[Customer Geography].[(All)].Item(0)
} ON 0
FROM [Adventure Works]

Also, after experimenting a bit further:

SELECT
{
  [Customer].[Customer Geography].[All]
} ON 0
FROM [Adventure Works]

This query also works even though the All member name is [All Customers], not just [All]. However, Analysis Services does recognise [All].

In summary, the most robust options for referencing the All member in a dimension hierarchy that I have found are:

1. [Dimension].[Hierarchy].[(All)].Item(0)
2. [Dimension].[Hierarchy].[All]

These will always work – regardless of the dimension and hierarchy names.

Another option is using [Dimension].[Hierarchy].[] – e.g. [Customer].[Customer Hierarchy].[All Customers]

And, one that does not work – referencing through its alleged key: [Customer].[Customer Hierarchy].&[0]

Please let me know if there are any better alternatives, or why it would give me a key of 0 for the All member and would not work when actually using this key.

Advertisements

8 thoughts on “All Member Properties – Name, Key and Level”

  1. Hi,I usually use [Customer].[Customer Geography].levels(0).item(0)this worked for all my need so far and is independet of any name or keygreets,gerhard

    Like

  2. The All member does not have a specific key. My guess is that your Key attribute for this dimension has a data type of Int or something like that. Therefore, because SSAS has to honor the datatype of the key, it returns 0

    Like

  3. Well, I did not go too far with testing all scenarios but I am wondering what will happen if I have a key of 0 for a key attribute. Seems like we would get two 0s in the result set.Also, wouldn't it be better if SSAS returns null on the All member to show that there is no key? Of course, using the key is not a good idea in most cases anyway, but assigning 0 could be confusing in my opinion.

    Like

  4. SSAS is not actually assigning it a value, it is just returning a value of 0, there is a subtle difference. If you had an actual member with a key of 0 and asked for the member by key you would only get the one that was specifically assigned 0.Returning null may have been a "less wrong" option, but I'm not sure if that is an option. I think that if the key of the underlying attribute is an int, it is internally mapped as C/C# int which is not nullable. But I have not played with this too much myself, I prefer to use non-null keys to avoid these sort of issues.

    Like

  5. Ok, so it is not getting assigned to the All member and that is why it yields no results when requested.I wrote about it with the intention to show a potential problem with using the All member key. There is not too much literature around about the All member in general and I am quite satisfied with the result – Gerhard showed a simple method of referencing All member (even though it may result in some unexpected results if the dimension is non-aggregatable) and now we have a better understanding of the All member key.I wish it was all a bit better documented in BOL.Thanks to both of you (Darren and Gerhard) for your insights! 🙂

    Like

  6. Hi Boyan

    This Helps clarify All Member Properties. Thanks for your the Post , it is very informative

    Is there a way to address the All Member Properties or All Members for the intermediate levesl of hierarchies?

    In above example intermediate levels will be Country, StateProvince ,City

    Thanks once again

    Regards

    Dave

    Like

  7. Hi Dave,

    You can use the same methods, just that you need to specify the attribute hierarchy instead of a user-defined one. Each attribute has its own hierarchy. In the Customer dimension in Adventure Works we have:

    [Customer].[Country]
    [Customer].[StateProvince]
    [Customer].[City]

    To get the All member in those you can use the same MDX statements as above:

    [Customer].[Country].[(All)].Item(0)
    or
    [Customer].[Country].Levels(0).Item(0)

    Same holds true for the other hierarchies.

    Like

Comments are closed.