Search code examples
ssasmdxhierarchydimension

MDX Dimension Navigation

I am on an MDX adventure and I'm at a point where I need to ask some questions.

I have a very basic dimension named Car. The attributes which comprise Car are as follows-

-Manufacturer
-Make
-Color
-Year

My fact table contains a sales measure ([Measures].[Sales]). I would like to know , without explicitly defining a user hierarchy, how to sum the sales from a specific group in this hierarchy

For example, I want to sum the sales of all red Trucks made in 2002. My attempt errors out-

sum([Cars].[Make].[Make].&[Truck]&[Red]&[2002], [Measures].[Sales])

How can I navigate the attribute hierarchy in this way? I will be browsing the cube in excel

Thanks

Solution

  • If you open an mdx query in SSMS and drag a member from one of your attribute hierarchies into the query pain you will see the full name.

    You definitely cannot chain hierarchies like this ...].&[Truck]&[Red]&[2002]

    Each full name will likely be similar to what MrHappyHead has detailed but usually the attribute name is repeated e.g. for Make:

    [Cars].[Make].[Make].&[Truck]

    MrHappyHead have wrapped it all in the Sum function but this is not required - just wrap the coordinates in braces and a tuple is then formed which will point to the required area of the cube:

    (
[Cars].[Make].[Make].&[Truck],
[Cars].[Color].[Color].&[Red],
[Cars].[Year].[Year].&[2002],
[Measures].[sales]
)

    note: square brackets are pretty standard in mdx.