Boyan Penev on Microsoft BI

When we build calculated members in our MDX queries we have to be careful not to make a few mistakes. The bahaviour of calculated members and scopes of the MDX expressions are well explained, but I would like to give a quick example of how mixing subcube expressions, WHERE clauses and calculated members can cause some headaches.

As an example I will use a Membership cube with a two dimensions, Gender and Age, and one measure – Person Count.

The scenario is very simple. We want to get the Male, Female and the Total person count from the cube broken down by Age. We also feel tricky and we want to get these measures using an approach which seems sophisticated (and is quite good for illustrative purposes only). The only thing we are not sure about is whether to use a subcube expression or a WHERE clause. First lets suppose we try with a WHERE clause:

WITH
MEMBER FemalePersonsCount AS
([Gender].[Gender].[F], [Measures].[Person Count])
MEMBER AllPersonsCount AS
(ROOT([Gender].[Gender]), [Measures].[Person Count])
SELECT NON EMPTY {
[Measures].[Person Count],
[Measures].[FemalePersonsCount],
[Measures].[AllPersonsCount]} ON COLUMNS,
NON EMPTY {
[Age].[Age].[Age].ALLMEMBERS} ON ROWS
FROM Membership
WHERE [Gender].[Gender].[M]

The query returns results where the Person Count and the FemalePersonsCount are correct, while the AllPersonsCount is incorrect, showing only the Male persons. It is quite expected behaviour as we have restricted the SELECT with a where clause and the ROOT expression also gets restricted. Even though the query is restricted by a WHERE clause, the FemalePersonsCount looks outside of the SELECT scope because a members outside it is specified in its definition tuple.

Discouraged from the failure, we decide to re-write the query with a subcube expression:

WITH
MEMBER FemalePersonsCount AS
([Gender].[Gender].[F], [Measures].[Person Count])
MEMBER AllPersonsCount AS
(ROOT([Gender].[Gender]), [Measures].[Person Count])
SELECT NON EMPTY {
[Measures].[Person Count],
[Measures].[FemalePersonsCount],
[Measures].[AllPersonsCount]} ON COLUMNS,
NON EMPTY {
[Age].[Age].[Age].ALLMEMBERS} ON ROWS
FROM
(
SELECT {[Gender].[Gender].[M]} ON COLUMNS
FROM Membership
)

This actually fixes the issue. The FemalePersonsCount now is correct as before and the AllPersonsCount is also correct. When using a subcube expressions the ROOT expression does not get restricted by the subcube as it does with a WHERE. Same is valid for a calculated member which specifies a cube area outside of the subcube scope.

If you enjoyed this post, make sure you subscribe to my RSS feed!

Related posts:

1. Passing unCONSTRAINED Set and Member parameters between reports in Reporting Services
2. Filtering Unneeded Dimension Members in PerformancePoint Filters
3. All Member Properties – Name, Key and Level

Boyan Penev SSAS MDX