SSAS Cube Calculation to summarise another measure at a coarser level

Question

I have run into performance problems with MDX measure calculations for a summary report, using SQL Server 2008 R2.

I have a Person dimension, and a related fact table containing multiple records per person. (Qualifications)

Eg [Measures].[Other Qual Count] would give me the number of qualifications of a certain type. Each person could have multiple, so [Measures].[Other Qual Count] > 1 for one person.

However on my summary report I would like to indicate this as 1 per person only. (To indicate the number of persons with Other qualifications.)

The summary report rolls up the values against some other dimensions including an unknown Region hierarchy (it can be one of 3 hierarchies).

I have done this as follows:

MEMBER [Measures].[Other Count2]
AS
SUM(
    EXISTING [Person].[Staff Code].[Staff Code].Members,
    IIF([Measures].[Other Count] > 0, 1, NULL)
)

However, I have to create several more derived measures - deriving from each other, and all at Person level to avoid unwanted multiple counts. The query slows down from <1 second to 1min+ (my goal is <3s).

The reason for all the derivations is a lot of logic to determine within which one of 6 mutually exclusive column a person will be reported in.

I have also tried to create a Cube Calculation, but this gives me the same value as [Other Count].

SCOPE (({[Person].[Staff Code].[Staff Code].MEMBERS}, [Measures].[Has Other Qual]));
    THIS = ([Person].[Staff Code].[Staff Code], [Measures].[Has Other Qual]).Count;
END SCOPE;

Is there a better MDX/Cube calculation that can be used, or any suggestions on improving performance?

This is unfortunately my first time working with MDX and ran into this problem close to a deadline, so I am trying to make this work if possible without changes to the cube.

hannesRSA hannesRSA · Accepted Answer · 2012-04-26T10:10:18

I have resolved the issue by changing the cube, which was simpler than expected.

On the Data Source View, I created a named query which summarizes the existing fact table at Person level. I also derive all the columns which I will need on my reports.

Treating this named query as a separate fact table, I added a measure group for it and that resolved all my problems.

SSAS Cube Calculation to summarise another measure at a coarser level

1 Answers