November 18, 2009
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.
Posted in 
Tags: 9 Responses to “Scope Problems with MDX Calculated Members”
Leave a Reply
I'm Alex Whittles.
I specialise in designing and implementing SQL Server business intelligence solutions,
and this is my blog! Just a collection of thoughts, techniques and ramblings on SQL Server, Cubes, Data Warehouses, MDX, DAX and whatever else comes to mind.
Hi,
ive used the above method in the following context:
- two time dimensions(A, B), one calculated measure
- for time dimension A, calc measure should display its value
- for time dimension B, calc measure should = NULL
- for time dimension A, the calc measure returns the correct values.
- for time dimension B, the descendant levels return NULL. the [All] level however returns the calc measure’s total.
How can I make this NULL also, without breaking the correct functionality as far as dimension A is concerned?
Many thanks,
Peter
Hi Peter
The problem you have is that if time dimension B is not selected as part of a query, it is essentially selecting the [All] level by default. So if you scope the calculation to be null when Time B is [all] then it will always result in null, no matter whether [all] is physically specified or not.
Does that make any sense?… Essentially you will have to allow the calculation to take place for [All], otherwise the calculation can never take place under any circumstances.
Alex
Hi,
this post was very helpful and it helped in resolving a similar issue in my project, and it has imporved the performance too. thank you very much for this.
I have a requirement where the grand total needs to be not affected by anyfilters applied in the WHERE clause. is this techinically feasible?
for e.g.
With member MEASURES.Test AS
SUM([Dispatch Country].[Country].Allmembers,Measures.[Value])
SELECT MEASURES.Test on 0 from [ots]
would give me the correct sum but when i filter it the value will only be the filtered one
With member MEASURES.Test AS
SUM([Dispatch Country].[Country].Allmembers,Measures.[Statistical Value])
SELECT MEASURES.Test on 0 from [ots]
where [Commodity].[Commodity Hierarchy].[HS4].&[Cheese];
in fact i am using the SCOPE stament in my calculated measure, i do calculations at each level to have the leaf level values, but the grand total should always include the true total without the filtered. any help much appreciated
thank you
rakesh
Hi Rakesh
If you’re filtering [Commodity].[Commodity Hierarchy] in the WHERE clause, you can make a calculated measure ignore this by specifying [Commodity].[Commodity Hierarchy].ALL in the measure calculation. The following does what you want inthe Adventure Works (2008R2) cube
With member [Measures].[Full Order Count] AS
([Product].[Product Categories].[All Products],[Measures].[Order Count])
SELECT {[Measures].[Full Order Count], [Measures].[Order Count]} on 0
FROM [Adventure Works]
WHERE [Product].[Product Categories].[Subcategory].&[1];
Even though the WHERE clause is filtering the Product Categories hierarchy, the Member expression is overriding this by specifying [Product].[Product Categories].[All Products].
Avoid using SUM(Hierarchy.AllMembers, Value) – you don’t need to use SUM, just use the single value of the ‘ALL’ member. It’s faster and more reliable as AllMembers will return the aggregated levels of the hierarchy as well as the leaf values, causing you to double count values.
Hi Alex,
thanks for the reply, my issue is we use a Pivot control from devexpress to browse the cube and it doesnt have options to send mdx query nd fetch the values. however i shall try adding a calculated measure like this – ([Product].[Product Categories].[All Products],[Measures].[Order Count]) and then use that measure, need to see how it could be integrated with the pivot though
also i was scope statements like this
Scope(DESCENDANTS([Date].[Fiscal],,AFTER));
[Measures].[Test Measure To Date] =
SUM(NULL:[Date].[Fiscal].CurrentMember,
[Measures].[Test Measure]);
End Scope;
there is a set of rule i need to apply on the leaf level for which the above query works fine, but i also need to have to apply a separate set of rule for calculation on the Total – ALL level if i add another SCOPE with Dimension.Members it will not work since all the leaf members too will apply the same rule. any suggestion on how this can be handled.
basically i want to have another scope that acts only on the ALL level, not on the leave Nodes
any help much appreciated
thanks
rakesh
I have tried adding the code (with small mod for my hierarchy defs) to the XMLA for my cube, but it always returns only the 2nd scope item correctly. If I change the order i.e. put [Year-Week_Day] as second block, the 2nd one is now OK while 1st is not. Here’s my modified code:
CREATE MEMBER CURRENTCUBE.[MEASURES].[Subscribers Test] AS “NA”,VISIBLE = 1;
SCOPE (DESCENDANTS([Dim Date].[Year-Week-Day],,AFTER));
[Measures].[Subscribers Test] = SUM(NULL:[Dim Date].[Year-Week-Day].CurrentMember,
(IIF(ISEMPTY([Measures].[Net Gain]),0,[Measures].[Net Gain])));
END SCOPE;
SCOPE (DESCENDANTS([Dim Date].[Year-Qtr-Mth-Date],,AFTER));
[Measures].[Subscribers Test] = SUM(NULL:[Dim Date].[Year-Qtr-Mth-Date].CurrentMember,
(IIF(ISEMPTY([Measures].[Net Gain]),0,[Measures].[Net Gain])));
END SCOPE;
What am I doing wrong? Also, when I try and build it within the BIDS environment, the calculated measure will fail.
BTW, I will be a very happy camper if I can get this to work!
Hi Paul
Sorry for the delay in replying, only just back after Christmas holidays.
The syntax looks ok so it seems like both hierarchies are being selected in the query – do you have default members set for the hierarchies?
Alex
I left blank – doesn’t that mean it will default to ‘ALL”? Or do I need explicitly set?
ALL is the default, so that’s not the problem. There must be something else interfering with it.
I’d need to take a more detailed look to investigate – I’ll happily investigate if you can get me a backup of the cube, drop me an email at Alex at purple frog systems dot com if you want me to take a look.
Alex