MDX Dimension Navigation

1
votes

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

2
ssasmdxhierarchydimension

2 Answers

0
votes

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.

0
votes

Is it something like:

Sum(
Cars.make.&[truck],
Cars.color.&[red],
Cars.year.[2002],
Measures.sales
)