0845 643 64 63

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.

Power BI Sentinel
The Frog Blog

Team Purple Frog specialise in designing and implementing Microsoft Data Analytics solutions, including Data Warehouses, Cubes, SQL Server, SSIS, ADF, SSAS, Power BI, MDX, DAX, Machine Learning and more.

This is a collection of thoughts, ramblings and ideas that we think would be useful to share.

Authors:

Alex Whittles
(MVP)
Reiss McSporran
Jeet Kainth
Jon Fletcher

Data Platform MVP

Power BI Sentinel
Frog Blog Out
twitter
rssicon