Search code examples
ssasmdx

Which of the following MDX syntax is most 'correct'?

Of the following methods, which is preferred for referring to a member?

-- dimension.hierarchy.level.member
[Team].[Name].[All].[Detroit Lions],
[Team].[Name].[Name].[Detroit Lions], -- how does 'name' get auto-added as a level?
[Team].[Name].[Detroit Lions],
[Team].[Detroit Lions],
[Detroit Lions],

They all produce the same result for me in the SELECT clause. Additionally, why is it possible in the second statement to 'jump' directly from the Name (attribute) to the Detroit Lions (member) -- does the implicitly add resolve to:

[Name].[Detroit Lions] ==> [Name].[All].[Detroit Lions]

Or how does that 'work' exactly?

Solution

  • I presume you are using a user hierarchy here. Based on that here is the answer. The best way to refer would be by strong name, which would be like
    "[Team].[Name].[Name].&[Detroit Lions]". (The the exact strongname you will need to check the indivisual member). Why is this the best way, since MDX will not have to do any lookups to find this member.

    If you are not using strong name , "[Team].[Name].[Name].[Detroit Lions]" this would be efficient too.

    Now why do the rest work. They work because MDX will go through all your dimensions to find this member. On the first occurence of this members it will be picked, if you somehow wanted to refer the second occurence of "Detroit Lions" you are in trouble. Now what do the other clauses mean.

    1. "[Detroit Lions]" you are going through all dimensions
    2. "[Team].[Detroit Lions]" you are going through all members within TEAM dimension
    3. "[Team].[Name].[Detroit Lions]" you are going through all members of dimension TEAM, hierarchy NAME
    4. "[Team].[Name].[Name].[Detroit Lions]", You are going through all members of dimension Team,Hierarchy Name , Level Name.
    5. "[Team].[Name].[All].[Detroit Lions]"This is the same as 3