I’ve had a lot of requests for more MDX content on my blog, so here’s something I’ve been meaning to write up for a long time: a worked example of how to use scoped assignments to implement two different types year-to-date calculation on two different hierarchies in the same dimension. Knowledge of how to use scoped assignments is the sign of a true MDX master (you can watch a video of a session I gave on the basics of scoped assignments at SQLBits here if you’re unfamiliar with them) but that’s because they can be very difficult to write and there’s surprisingly little information out there on the internet about them. They are incredibly powerful, though, and often they provide the most elegant and best-performing way to solve a problem.
Let’s start by looking at the Date dimension in the Adventure Works DW sample database, and more specifically the attributes, user hierarchies and attribute relationships:
Notice how we have two user hierarchies for Calendar Years (which start on January 1st) and Fiscal Years (which start on July 1st), called Calendar and Fiscal. Now, let’s say that you have a requirement to to show year-to-date values for a measure for both the Calendar and Fiscal hierarchies. It would be very easy to implement this as two separate calculated measures but what if you needed to show both types of year-to-date in the same calculated measure, showing Fiscal year-to-dates when the Fiscal hierarchy was used in a query and Calendar year-to-dates when the Calendar hierarchy was used in a query?
This is possible using scoped assignments. The first thing to point out, though, is that this is only going to be possible if you change the structure of the dimension. Why? Well, take a look at the Date levels of both hierarchies: they are both built using the Date attribute. If you were running a query with your YTD calculation on columns and only the Date attribute hierarchy on rows, would you expect to see Calendar or Fiscal YTD values? Certainly you couldn’t see both in the same cell, and this is the problem: if you expect to see Calendar YTD values at the bottom of your Calendar hierarchy, and Fiscal YTD values at the bottom of your Fiscal hierarchy, you need two separate Date attributes to do this. If you overwrite the values in the same cells twice using a scoped assignment, then you will only see the result of the second assignment.
Therefore, what we need to do is to create two new attributes, Calendar Date and Fiscal Date, to serve as the lowest levels of the Calendar and Fiscal hierarchies instead of the Date attribute. Here’s what the new attribute relationships look like:
From the end-user’s point of view nothing seems to have changed (these new attributes can have their AttributeHierarchyVisible property set to False) but this now means we have two attributes, two different slices of the cube, whose values we can overwrite separately instead of just one.
Now for the MDX. A good rule to follow when writing scoped assignments is to always use attribute hierarchies and never use user hierarchies; this is because there are rules you have to obey about the shape of the subcube of data you are overwriting with your scoped assignment. When defining a scope using only attribute hierarchies, you can only use the following types of set:
- Every single member on the attribute hierarchy
- Just one member on the attribute hierarchy
- Multiple members on the attribute hierarchy not including the All Member
I wrote up a detailed set of rules for defining scopes in MDX Solutions, if you’re interested; if you don’t follow these rules you’ll get the infamous “An arbitrary shape of the sets is not allowed in the current context” error (I know a joke about that, incidentally).
For this calculation, we need to make two scoped assignments on a single calculated measure called [YTD Sales]: one to show the Fiscal YTD calculation for the Fiscal Date, Fiscal Month Name, Fiscal Quarter, Fiscal Semester and Fiscal Year attributes; and one to show the Calendar YTD calculation for the Calendar Date, Calendar Month Name, Calendar Quarter, Calendar Semester and Calendar Year attributes. When scoping on ranges of attributes like this, there’s another easy rule to follow: scope on the set of every member, including the All Member, from the attribute hierarchy at the lowest granularity attribute, and the set of every member, not including the All Member, from the highest granularity attribute. These two sets also need to be in the same, rather than separate, SCOPE statements for reasons I explain here.
This results in the following MDX:
CREATE MEMBER CURRENTCUBE.MEASURES.[YTD Sales] AS NULL;
SCOPE(MEASURES.[YTD Sales]);
--Calendar YTD
SCOPE([Date].[Calendar Date].MEMBERS, [Date].[Calendar Year].[Calendar Year].MEMBERS);
THIS = AGGREGATE(
PERIODSTODATE([Date].[Calendar].[Calendar Year], [Date].[Calendar].CURRENTMEMBER),
[Measures].[Internet Sales Amount]);
END SCOPE;
--Fiscal YTD
SCOPE([Date].[Fiscal Date].MEMBERS, [Date].[Fiscal Year].[Fiscal Year].MEMBERS);
THIS = AGGREGATE(
PERIODSTODATE([Date].[Fiscal].[Fiscal Year], [Date].[Fiscal].CURRENTMEMBER),
[Measures].[Internet Sales Amount]);
END SCOPE;
END SCOPE;
Now you can see the YTD Sales calculated measure returns Calendar YTD values for the Calendar hierarchy:
…and it returns Fiscal YTD values for the Fiscal hierarchy:
There’s one last trick I want to show. It can be very difficult to know that your scoped assignment is covering the subcube you want it to cover, so while debugging scoped assignments I find it helps to assign values to the BACK_COLOR cell property so I can see exactly where I’m scoping. Here’s the MDX above with extra assignments to set the cell background colour to orange for the Calendar YTD calculation and blue for the Fiscal YTD calculations:
CREATE MEMBER CURRENTCUBE.MEASURES.[YTD Sales] AS NULL;
SCOPE(MEASURES.[YTD Sales]);
--Calendar YTD
SCOPE([Date].[Calendar Date].MEMBERS, [Date].[Calendar Year].[Calendar Year].MEMBERS);
THIS = AGGREGATE(
PERIODSTODATE([Date].[Calendar].[Calendar Year], [Date].[Calendar].CURRENTMEMBER),
[Measures].[Internet Sales Amount]);
BACK_COLOR(THIS) = RGB(255,128,64);
END SCOPE;
--Fiscal YTD
SCOPE([Date].[Fiscal Date].MEMBERS, [Date].[Fiscal Year].[Fiscal Year].MEMBERS);
THIS = AGGREGATE(
PERIODSTODATE([Date].[Fiscal].[Fiscal Year], [Date].[Fiscal].CURRENTMEMBER),
[Measures].[Internet Sales Amount]);
BACK_COLOR(THIS) = RGB(64,128,255);
END SCOPE;
END SCOPE;
This now shows up in an Excel PivotTable as you can see below:
It can also help you understand what’s going on in the scenarios where the scopes overlap, for example if you put the Calendar and Fiscal hierarchies on rows and columns in the same PivotTable: the Fiscal hierarchy takes precedence, because it’s defined second.
Ouch if only I thought to the color trick many, many, many times I had to debug some complex MDX script…
Thank you, Chris!!
Marco
Great job on this Chris! Very useful!!!!!
That, my friend, is going to be an all time classic post. I’m wrestling with some scoping right now, and that colouring trick is a gem. Cheers Chris.
Indeed the color trick,… that would have saved me some time. Thanks Chris.
Awesome. This is the kind of article about scope statements I wish I’d had when I was starting out with SSAS….. 🙂
Wonderful, simply solves lots of MDX scripting.
I have never thought of using the back color to visually define the scoping. that is an excellent tip. thanks
L’ha ribloggato su Marchino BI Land.
Two great tips there, thanks Chris – both the back colour to debug scoping and the multiple YTD values trick.
Regarding the latter, though, I was banging my head against a wall trying to implement it in my cube, convinced I had it exactly as per your example yet it wasn’t working – the second nested scope statement seemed to be overriding the first (well, it overrode the value, but NOT the back colour!!).
Finally decided the only difference was that my measure was created as a null value in the DSV, rather than in the MDX script (so I could hide it from users in certain roles) so I figured I might as well try changing that and hey presto it now works! Any thoughts on why the difference?
I now have the target measure defined in the DSV and an intermediate measure defined in the MDX script with “visible – 0” and your scoped assignment applied to it. I then have another scoped assignment to pass the result from the intermediate measure to the target one which has the appropriate security applied to it…
Creating a real measure from a null column in the DSV will behave differently from a calculated measure, because on the real measure the values that you are assigning will aggregate up beyond the original scope. See this blog post for a good example: http://cwebbbi.wordpress.com/2013/05/29/aggregating-the-result-of-an-mdx-calculation-using-scoped-assignments/
Well, I take your point about rolling up but this seems to misbehave at the level of the original scoped assignment too. Perhaps it’s down to my lack of expertise with MDX, but I got it to work by simply creating a new measure in the MDX script and substituting the name of that measure for the DSV-defined one.
Anyway, my workaround seems to be doing the job so thanks again for the tips!
Hi Chris,
I am trying to implement the same and facing issues while doing so.
Could you kindly tell if the Calendar Hierarchy has Calendar Date as lowest level or the Date as the Last level?
If it CalendarDate is the Last level, how come there wont be any difference in end user’s point of view
(same for Fiscal Hierarchy)
Referring to your explaination
” From the end-user’s point of view nothing seems to have changed (these new attributes can have their AttributeHierarchyVisible property set to False) but this now means we have two attributes, two different slices of the cube, whose values we can overwrite separately instead of just one. ”
-Thanks,
Dhruvil Shah
It should have Calendar Date as the last level. The end user will just see a level that contains dates at the bottom of both the Calendar and Fiscal hierarchies – the levels will look exactly the same as they did before, just a list of dates. However these levels will be based on the two new attributes you’ve created. It’s only the calculated values that will be different.
Thanks Chris for confirming :).
Also wanted to know about multiselection is not working with the Scope Assignments.
Test 1 without Scope:
———————
CREATE DYNAMIC SET CURRENTCUBE.[Days Set]
AS {[DimTime].[Year].currentmember*[DimTime].[GregorianDate].[GregorianDate]};
CREATE MEMBER CURRENTCUBE.[Measures].[Sales Value PY Test1] AS
SUM(
Generate
(
existing [Days Set],
{ParallelPeriod([DimTime].[Gregorian Calendar].[Year], 1, [DimTime].[Gregorian Calendar].currentmember)}
)
,[Measures].[Sales Value CY]
);
Test 2 with Scope:
——————
SCOPE([DimTime].[GregorianDate].Members,[DimTime].[Year].[Year].MEMBERS);
CREATE DYNAMIC SET CURRENTCUBE.[Days Set 1]
AS {[DimTime].[Year].currentmember*[DimTime].[GregorianDate].[GregorianDate]};
[Measures].[Sales Value PY Test2] =
SUM(
Generate
(
existing [Days Set 1],
{ParallelPeriod([DimTime].[Gregorian Calendar].[Year], 1, [DimTime].[Gregorian Calendar].currentmember)}
)
,[Measures].[Sales Value CY]
);
***
When Tried above 2 approaches, Test 1 provides correct results for Multiple years, but Test 2 does not.
Does this mean that Scope assignment dose not work with Existing?
Any Inputs?
Hi Chris,
Thank you for great post!
I’ve created two parallel attribute relationships in Date dimension – fiscal and calendar. Just as described in your article in order to use different calculations depending on what the hierarchy is used in query.
Now I am trying to add to my cube sales quota measure group. Sales quota has a month granularity so I need to link it to month attribute in Date dimension. But to which one – Calendar Month Name or Fiscal Month Name?
If I select Calendar Month Name as granularity attribute I won’t be able to see sales quota by month in Fiscal hierarchy. And vice versa. Before we split Month Name attribute into Fiscal and Calendar it’s not a problem. But how can it be solved with two parallel month level attributes?
In both hierarcies we have the same month (e.g. December 2014) so it’s natural that we want to be able to see sales quota for this month in both hierarcies.
Thanks!
Aleksandr.
I suggest creating a single, new month attribute on your date dimension to join to the measure group on. Hide this attribute so that the end users can’t see it, and then create relationships between it and the existing Calendar Month Name and Fiscal Month Name attributes. Make sure that the new attribute is on the ‘many’ side and the existing attributes are on the ‘one’ side of the relationships.
Thank you for the answer, Chris!
I’ve created new month attribute and proper relationships and joined Sales Quota measure group to the new month attribute. But in cube browser I get the same Quota Amout value for every member of Calendar and Fiscal hierarchy. Maybe I’m doing something wrong.
I’ve uploaded a screenshot of attribute relationsheep – http://i.imgur.com/HXi45OT.png?1. New month attribute is YYYYMM. Fiscal Month, Month Year (calendar month) and YYYYMM have the same KeyColumn. As well as Date, Calendar Date and Discal Date,
And it seems that SSAS don’t like new relationships:
“Design hierarchies for each incoming relationship path where attributes have multiple incoming relationships or, if any of the incoming relationships are unnecessary, you should delete them.”
Try deleting the relationships that go from Calendar Date to Month Year, and from Fiscal Date to Fiscal Month, and create two new relationships: from Calendar Date to YYYYMM and from Fiscal Date to YYYYMM.
Yay! It works! ) Thank you, Chris!
Attribute relationships look like this now – http://i.imgur.com/vL9cJvL.png?1.
Sales Quota measure group and scope assigments works fine with both Calendar and Fiscal hierarchies! But SSAS are still unhappy with this diamond-shaped relationship. It bothers me slightly.
I’m thinking about adding to Sales Quota table new column “Last day of month” and then joining measure group to Date attribute using this new column. It seems it will work like month granularity and allow to keep attribute relationships plain and simple – http://i.imgur.com/FKj5QHK.png?1.
Trying to figure out pros and cons of each way method.
Hi Chris,
I am stuck in very similar situation where I want to use YTD for three different calendars of our company and don’t want to create three different YTD calculations. However in your example you have a particular measure but I want it to work for multiple measures
If I create one YTD and try to use in context of three calendars in SCOPE statements then it doesnot give my right results. Following is my syntax but It does not work. Please advise if it’s possible or not.
SCOPE([Billing Date].[SalesCalendar].MEMBERS);
( [Aggregate].[AGGREGATE CODE].[YTD] )
= Aggregate({[Aggregate].[AGGREGATE CODE].DefaultMember}*{PERIODSTODATE([Billing Date].[SalesCalendar].[Sales Calendar Year],[Billing Date].[SalesCalendar].CURRENTMEMBER) } );
END SCOPE;
SCOPE([Billing Date].[FinancialCalendar].MEMBERS);
( [Aggregate].[AGGREGATE CODE].[YTD] )
= Aggregate({[Aggregate].[AGGREGATE CODE].DefaultMember}*{PERIODSTODATE([Billing Date].[FinancialCalendar].[Financial Year],[Billing Date].[FinancialCalendar].CURRENTMEMBER) } );
END SCOPE;
However if I comment on one of SCOPE statement , other one works but both don’t work simultaneously in context of different calendars.
Regards,,
Haroon
It should work. You haven’t used attribute hierarchies in your SCOPE statements though – it looks like [Billing Date].[SalesCalendar] and [Billing Date].[FinancialCalendar] are user hierarchies. Can you try using attribute hierarchies as I did in my example?
Hi Chris,
In your example, are you scoping ‘Month of Year’ attribute?
I have a measure that is not linked to my time dimension, IgnoreUnrelatedDimensions=false and I want the measure to show values even when there’s a filter applied to the time dimension (the value for All dates). In other words, I want IgnoreUnrelatedDimensions=true for a particular dimension for that measure.
I’m scoping as you are and everything is working fine except my Month of Year attribute, so I don’t know if I’m making some mistake implementing your solution or if your solution doesn’t scope that attribute either (in that case, do I have to scope it specifically or is there a simple way to scope an entire dimension?).
Thank you.
Cheers,
mprost
No, in this example the Month of Year attribute isn’t included in the Scope – you would have to scope on that attribute specifically.
Thank you very much for taking the time to answer my question. Happy new year!
Hi Chris,
MDX newbie here 🙂
Great post. I have some questions regarding how to make your solution fit my situation.
My relationships are like this:
https://www.screencast.com/t/bW2XdJdef1
And my date dimension looks like this:
https://www.screencast.com/t/JyF7VCJMsIqa
The Date-dimension is role playing as [Posting Date], [Entry Date] and [Document Date], and has, as you can see, three hierarchies.
I want to create your YTD-measure that works no matter which of the hierarchies i pull in regardless the dimension.
I simply cannot figure out how the query should look and how i should reference dimension the right way. Maybe the “user hierarchies” vs. “attribute hierarchies” are confusing me, I don’t know.
Kind Regards
Hi Benny,
User hierarchies are the hierarchies in the “Hierarchies” pane in the dimension editor, for example “Calendar Y-Q-M-D” in your dimension. Attribute hierarchies are the hierarchies based on the attributes in the “Attributes” pane, for example “Calendar Date”.
Do you want the calculation to work on all three user hierarchies of all three instances of your Date dimension? This will be possible, but with this many combinations I worry that if users make selections from more than one user hierarchy across these three dimensions it will be difficult for them to understand what’s going on. My recommendation is to use the technique in the blog post on just one dimension and see how your users get on.
Hi Chris,
great post, I’m hoping you have a minute to help if possible. I’ve followed your instruction above to create a rolling total of enrolments split by academic year. My cube processes without error, but when I go to browse or connect via excel there are no rows of data showing.
My dimension is set up as:
http://imgur.com/dOYUbNi and http://imgur.com/hV2SUpy
and my calculation script is:
CREATE MEMBER CURRENTCUBE.MEASURES.[Enrolment Running Total]
AS NULL,
VISIBLE = 1 , DISPLAY_FOLDER = ‘Learner Records’;
SCOPE(MEASURES.[Enrolment Running Total]);
SCOPE([Calendar Record Creation].[Actual Date].MEMBERS, [Calendar Record Creation].[Academic Year].[Academic Year].MEMBERS);
THIS = AGGREGATE(
PERIODSTODATE([Calendar Record Creation].[Academic Calendar].[Academic Year], [Calendar Record Creation].[Academic Calendar].CURRENTMEMBER),
[Measures].[Enrolment Count]);
END SCOPE;
END SCOPE;
The normal [Enrolment Count] measure returns data fine, I think I’ve run out of webpages to search in google now, so you’re my last hope!
Thanks id advance,
Thomas
Have you done this? https://blog.crossjoin.co.uk/2011/10/20/why-has-all-the-data-in-my-cube-disappeared/
Thanks for getting back so quickly!
The calculate line hasn’t been commented out, all other measures are showing correctly after the cube has been processed, it’s just the measure created as part of the calculation that isn’t showing anything
Ok, next idea: are you testing this measure in a PivotTable on its own? If so, try dragging your existing Enrolment Count measure in first then adding the new calculated measure. PivotTables filter out empty rows and the calculated measure returns null at the top level; this means you have nothing visible to drill down on to get to the Year level to see data unless another measure is present too.
Hi Chris,
I was referencing the wrong measure! Unfortunately I’m now getting #VALUE! when running it through a pivot in excel. I’ve tried dropping in the normal enrolment count measure first(which populates fine) followed by the Enrolment running Total measure, which still brings out the value error.
The calendar dimension is populated from a table built in house that starts and ends on 1st august and 21st July each year. Are there settings I could be missing from the dimension itself or the table that could be hampering things?
Again, really appreciate your feedback!
Thomas
Can you try changing the Aggregate() function to Sum()? The error could be explained if the measure you’re referencing is itself a calculated measure
Hi Chris,
using Sum brought back the same results. As I only actually need the figures for this year and last year I got around it by putting in the script below. This will allow me to hit my deadline and I’ll continue to try and get your method working once I have more time.
I really appreciate your time and feedback on this! When I get it to work I’ll report back.
Current script:
CREATE MEMBER CURRENTCUBE.MEASURES.[Enrolment Running Total]
AS NULL,
VISIBLE = 1 , DISPLAY_FOLDER = ‘Learner Records’;
SCOPE(MEASURES.[Enrolment Running Total]);
SCOPE([Calendar Record Creation].[Academic Weeks].[Academic Year Name].&[2016/17].Children);
THIS = SUM({[Calendar Record Creation].[Academic Weeks].CurrentMember.Parent.FirstChild
:[Calendar Record Creation].[Academic Weeks].CurrentMember}
,[Measures].[Base Enrolment Count]);
END SCOPE;
SCOPE(MEASURES.[Enrolment Running Total]);
SCOPE([Calendar Record Creation].[Academic Weeks].[Academic Year Name].&[2017/18].Children);
THIS = SUM({[Calendar Record Creation].[Academic Weeks].CurrentMember.Parent.FirstChild
:[Calendar Record Creation].[Academic Weeks].CurrentMember}
,[Measures].[Base Enrolment Count]);
END SCOPE;
END SCOPE;