MDX: How To Aggregate Hierarchy Level Members With Same Name

Question

Greetings,

I am new to MDX, and am having trouble understanding how to perform an aggregation on a hierarchy level with members that have the same names. This query is particular to Microsoft Analysis Services 2000 cubes.

I have a given hierarchy dimension with levels defined as follows:

[Segment].[Flow].[Segment Week]

Within the [Segment Week] level, I have the following members:

[Week- 1]
[Week- 2]
[Week- 3]
   ...
[Week- 1]
[Week- 2]
[Week- 3]

The members have the same names, but are aligned with a different [Flow] in the parent level. So, the first occurrence of the [Week- 1] member aligns with [Flow].[A] while the second occurrence of [Week- 1] aligns with [Flow].[B]. What I am trying to do is aggregate all the members within the [Segment Week] level that have the same name. In SQL terms, I want to GROUP BY the member names within the [Segment Week] level. I am unsure how to do this. Thank you.

Dave

Answer 1

I think your cube design is flawed.

The best way to design a dimension is so all its members are unique, and the aggregation path follows the natural hierarchy of the dimension.

You could remove the WEEK level from SEGMENT and create another dimension (if I remember correctly, you can't have more than one hierarchy in AS 2000), say WEEK, with the following structure:

[Week].[Week- 1]
[Week].[Week- 2]
[Week].[Week- 3]
etc

Then you just have to filter by SEGMENT and by Week. For example, to get all the Week1 of Flow A you'd do:

SELECT {[Measures].members} on 0 
FROM MYCUBE
WHERE ([Week].[Week- 1],[Segment].[A])

Answer 2

Could you use the key of the member?

[Week].&[1] ([Week].[Week - 1] (flow1)) 
[Week].&[5] ([Week].[Week - 1] (flow2))

For reference, in the 'Adventure Works DW Standard Ed' Cube for 2008, the key for Customer Aaron A. Allen is [Customer].[Customer].&[20075]

UPDATE: Sorry, just reread your question, it looks like you're not trying to get the specific week to a flow, you want to aggreagate them. What about a CASE Statement like this:

CASE
  WHEN [Week].CURRENTMEMBER.NAME='Week - 1'
  THEN [Week].CURRENTMEMBER
  ELSE 0
END

Not very generic or flexible, but it might be a start...