Funny how you can work with a product for years and years and still discover new things, isn’t it? I was writing some scoped assignments on a cube the other day and found that when you’re using nested SCOPE statements, the order that you put those SCOPE statements in the MDX Script is significant, contrary to what I had believed.
Consider a simple date dimension with the following attributes and attribute relationships:
Now, if we add the following MDX to the script, to scope on every member (including the All Member) on the date dimension:
CREATE MEMBER CURRENTCUBE.MEASURES.DEMO AS 1;
SCOPE(MEASURES.DEMO);
SCOPE([Order Date].[Date].MEMBERS);
THIS = 2;
END SCOPE;
END SCOPE;
You’ll see that it has changed the values of the DEMO measure for the whole of the date dimension from 1 to 2:
Now, if we add a second nested SCOPE on all the members of the Month level of the Month attribute (ie so not including the All Member on Month) as follows:
CREATE MEMBER CURRENTCUBE.MEASURES.DEMO AS 1;
SCOPE(MEASURES.DEMO);
SCOPE([Order Date].[Date].MEMBERS);
SCOPE([Order Date].[Month].[Month].MEMBERS);
THIS = 2;
END SCOPE;
END SCOPE;
END SCOPE;
You’ll see that it now only changes month values, and nothing else:
This set of assignments gives the same result as the following, where there is no assignment on Date at all:
CREATE MEMBER CURRENTCUBE.MEASURES.DEMO AS 1;
SCOPE(MEASURES.DEMO);
SCOPE([Order Date].[Month].[Month].MEMBERS);
THIS = 2;
END SCOPE;
END SCOPE;
However, if you reverse the order of the two SCOPEs you get a different result. So:
CREATE MEMBER CURRENTCUBE.MEASURES.DEMO AS 1;
SCOPE(MEASURES.DEMO);
SCOPE([Order Date].[Month].[Month].MEMBERS);
SCOPE([Order Date].[Date].MEMBERS);
THIS = 2;
END SCOPE;
END SCOPE;
END SCOPE;
When you scope on all the members of Month except the All Member first, then all the members of Date including the All Member, the scope covers all dates and months:
Crossjoining these two sets in the same SCOPE has the same effect:
CREATE MEMBER CURRENTCUBE.MEASURES.DEMO AS 1;
SCOPE(MEASURES.DEMO);
SCOPE([Order Date].[Month].[Month].MEMBERS,[Order Date].[Date].MEMBERS);
THIS = 2;
END SCOPE;
END SCOPE;
What’s happening is that where you have two nested SCOPE statements using sets of members from different attribute hierarchies on the same dimension, the attribute relationships that exist between those attributes become significant. Without any SCOPE statements then the ‘current’ scope context in the MDX Script is the entire cube, and then each successive SCOPE overwrites the previous context to create a new context and that process of overwriting context is not commutative. This is called attribute overwrite; I’ve wrestled with it before and frankly it makes my head hurt, but you can find out more about it here.
I suppose, therefore, that it would be a good idea when you’re scoping on sets containing members from multiple attributes from the same dimension to crossjoin those sets together and use a single SCOPE, to avoid any potential confusion. While I can just about rationalise this behaviour I can’t guarantee I’d be able to predict how attribute overwrite worked on a real dimension with multiple nested SCOPEs… Remember, of course if you have multiple nested SCOPEs using sets of members from hierarchies on different dimensions then the order of nesting doesn’t matter because there’s no attribute overwrite going on.
Thanks to Tomislav and Akshai for their help in understanding this problem.
Thanks Chris !
Hi Chris,
In a scope statement,
SCOPE([Measure Item].[Measure Item].&[46 *EBIT]);
THIS = ([Measure Item].[Measure Item].&[40 *EBIT Adjusted])+
([Measure Item].[Measure Item].&[35 *EBIT]);
END SCOPE;
Currently this calculation is working for all members.
In another “Version” dimension table which is having 300 entries.
So the change required is, the above calculation should not happen for 10 members from “Version” dimension.
If it is for all the member
“SCOPE([Version].[Version].members)”
But the change is not required for 10 members.
Could you please help me with a function or logic to implement this logic.
Thanks in advance
Hi Chris,
Shall I use Except function inside a SCOPE?
SCOPE (EXCEPT{Set with all members}, {Set should not be present}).
Yes, that sounds right.
Thanks Chris
Will try to implement this MDX SCOPE
Hi Chris,
I have the below statement.
CREATE MEMBER CURRENTCUBE.[MEASURES].[Test Net Sales]
AS [MEASURES].[Net Sales]
, FORMAT_STRING = “#,#;(#,#)”, VISIBLE = 1;
SCOPE [Measures].[Test Net Sales];
SCOPE([Date].[Retail Week].&[FY15 Wk 4]);
this=NULL;
END SCOPE;
END SCOPE;
When I run a SELECT like
SELECT [Measures].[Test Net Sales] ON 0,
[Date].[Retail Hierarchy].[Retail Week].Members ON 1
FROM Cube
The output is supposed to show all the members of [Date].[Retail Hierarchy].[Retail Week] and [Test Net sales] measure except for the member [FY15 Wk 4] (It supposed to show NULL).
Example:
FY15 Wk 1 231315
FY15 Wk 2 548645
FY15 Wk 3 343434
FY15 Wk 4 (null)
FY15 Wk 5 784256
But its not working as expected. In my DEV environment I have 2 AS DBs one with full implementation and a test cube with less number of dimensions and measures but both the cube has the same Date dimension with the same hierarchies and everything. Strangely the above SELECT returns the expected result in test cube but not in the actual cube.
Given that both has the same DATE dimension, how could this give different results.
I agree that this should work. There must be some difference between the two cubes! Are all of the other calculations identical on both cubes? Did you put this code in the same place on the MDX Script on both?
Thanks for the reply Chris.
In my test cube, I had commented out some of the calculations that belonged to the dimensions/measures that I deleted to reduce the processing time. so that means there could have been some difference on the placement of the above script. Does that make any difference as long as there are no other Scoping on [Date].[Retail Hierarchy].[Retail Week].Members.
Just trying to understand what are the factors likely to cause this given that the Date hierarchy is identical.
It can make a difference if there are scopes on other hierarchies of the Date dimension, even if they aren’t on the Retail Week hierarchy. This video will help explain why: https://sqlbits.com/Sessions/Event8/Fun_with_Scoped_Assignments_in_MDX
Thanks Chris, will have a look at it.
Can you please provide the Scope statement for the Grand total issue.Currently measure is LastNonEmpty aggregation but In grand total if December month value is empty/null than Grnadtotal should be “empty” but unfortunately it’s showing last available value of the month.suppose if the Value is there in April and not in December than Grand total is giving April value ideally requirement is to get December value..Please help!!
Hi Chris,
Can you please help to resolve the Grand Total issue.Requirement is if the December Month value is “empty” then it should give empty even the measure is defined as LastNonEmpty aggregation in cube level.suppose if the value is available for April month and December is “empty” than in Grand Total it’s displaying April Month value instead of December(which is empty). Requirement is to get December Month value even if it’s empty not the other month values..Please help to resolve the issue using SCOPE statement.
Have you tried using the LastChild aggregation type instead of LastNonEmpty?
Thank you very much for your response Chris !!.I have tried using SCOPE assignment in MDX level but that has not worked.I will try in measure aggregation level..
on impelemting LastChilf aggregation type the results are not as per the expectations so created one more measure with SUM and written Scope statement to assign LastNonEmpty.here is the code goes
Scope
(
[Date Of Posting].[Date Of Posting].[All],
[Exchange Rate Type].[Exchange Rate Type].[Exchange Rate Type].Members, /*or Leaves(Customer)*/
{
[Measures].[Value GC SUM]
}
);
this = [Measures].[Value GC]; /*assign Last Sales to Sales to make it semi-additive */
End Scope;
Above code gives GrandTotal at rowlevel but if it’s cumulative than getting huge values at GrandTotals
Hi Chris,
Can you please help me to implement below requirement using SCOPE assigment
• Case 1: We are in calendar month of April 2018 and Actual data for 1-3/2018 is available for measure item A -> this same value should be displayed in the Grand Total
• Case 2: We are in calendar month of April 2018 and Actual data for 1-3/2018 is available for various measure items, however measure item B is empty -> empty value should be displayed in the Grand Total
1/2018 1-2/2018 1-3/2018 Grand Total
Case 1: Measure Item A 100 220 350 350 (= 1-3/2018)
Case 2: Measure Item B 50 (empty) (empty) (empty) (= 1-3/2018)