Role-playing measure groups - by Christopher Webb

Status : 


Sign in
to vote
ID 436081 Comments
Status Active Workarounds
Type Suggestion Repros 0
Opened 4/29/2009 7:53:49 AM
Access Restriction Public


We've got role-playing dimensions, but there are also some scenarios where you want the dimensions in the cube to stay the same but have multiple copies of the same measure group joining to these dimensions on different key columns, and where you only have to process the measure group once.

I've come across this scenarion a few times. A good example would be a fact table containing travel data for airlines. You would have an Airport dimension, and each row in the fact table would represent a single journey with a Start Airport and an End Airport. If the requirement was to count all the journeys that either started or ended at a particular Airport, one solution would be to create three measure groups: one where your Airport dimension was joined to the Start Airport column, one where your Airport was joined to the End Airport column, and one where your Airport dimension was joined to both the Start and End Airport columns for flights that started and ended at the same airport. You'd then create count measures on all three measure groups, and create a calculated measure that was Starts + Ends - StartsAndEnds to find the overall number of journeys including the airport. This performs quickly, but it effectively means that you have to process the same measure group three times.

I can think of other solutions to the problem, eg using MDX and LinkMember, but none are particularly elegant or good from a performance point of view.
Sign in to post a comment.
Posted by Microsoft on 7/28/2009 at 4:35 PM
Thanks a lot Chris.
This is interesting idea.
We will consider this for the future version of Analysis Services.

Edward Melomed.