Monthly Archives: November 2009
Scope Problems with MDX Calculated Members
We were recently investigating a problem for a client regarding the use of Scope within MDX calculated members. The code in question was similar to this:
CREATE MEMBER
CURRENTCUBE.[Measures].[Test Measure To Date]
AS "NA", VISIBLE = 1;
Scope([Date].[Calendar].MEMBERS);
[Measures].[Test Measure To Date] =
SUM(NULL:[Date].[Calendar].CurrentMember,
[Measures].[Test Measure]);
End Scope;
Scope([Date].[Fiscal].MEMBERS);
[Measures].[Test Measure To Date] =
SUM(NULL:[Date].[Fiscal].CurrentMember,
[Measures].[Test Measure]);
End Scope;
Essentially the warehouse was providing a transaction table with credits and debits, this calculated measure was supposed to provide the current balance, summing all transactions to date (not just the current year/period etc, but the entire history). Scope is used to enable the calculation to work across two different date hierarchies, calendar and fiscal.
The problem was that even when the [Date].[Calendar] hierarchy was selected, the code still used the fiscal hierarchy to calculate the value.
This is caused by the fact that [Date].[Fiscal].MEMBERS includes the member [Date].[Fiscal].[All]. Consequently, even when the Fiscal hierarchy was not included in the query, its [All] member was effectively still within the scope. Thus the fiscal calculation was overriding the calendar calculation no matter what was selected.
The solution to this is to exclude [All] from the scope, which can be done by changing the code to the following:
CREATE MEMBER
CURRENTCUBE.[Measures].[Test Measure To Date]
AS "NA", VISIBLE = 1;
Scope(DESCENDANTS([Date].[Calendar],,AFTER));
[Measures].[Test Measure To Date] =
SUM(NULL:[Date].[Calendar].CurrentMember,
[Measures].[Test Measure]);
End Scope;
Scope(DESCENDANTS([Date].[Fiscal],,AFTER));
[Measures].[Test Measure To Date] =
SUM(NULL:[Date].[Fiscal].CurrentMember,
[Measures].[Test Measure]);
End Scope;
DESCENDANTS(xxx,,AFTER) is a simple way of identifying every descendent of the hierarchy AFTER the current member, which is [All] when not specified.
Problem solved, Frog-blog out.