Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Using Scoped Assignments To Show Calendar and Financial Year-To-Date Values In The Same Calculated Measure

with 22 comments

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:

image

image

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:

image

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:

image

…and it returns Fiscal YTD values for the Fiscal hierarchy:

image

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:

image

image

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.

image

Written by Chris Webb

May 23, 2013 at 3:27 pm

22 Responses

Subscribe to comments with RSS.

  1. 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

    Marco Russo

    May 23, 2013 at 4:03 pm

  2. Great job on this Chris! Very useful!!!!!

    Scott Barrett

    May 23, 2013 at 11:18 pm

  3. 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.

    Sam Loud

    May 24, 2013 at 8:48 am

  4. Indeed the color trick,… that would have saved me some time. Thanks Chris.

    Diederick Oskamp

    May 24, 2013 at 9:11 am

  5. Awesome. This is the kind of article about scope statements I wish I’d had when I was starting out with SSAS….. :)

    Nick Colebourn

    May 24, 2013 at 9:13 am

  6. Wonderful, simply solves lots of MDX scripting.

    Ritesh Gaur

    May 24, 2013 at 10:55 am

  7. I have never thought of using the back color to visually define the scoping. that is an excellent tip. thanks

    David Shroyer

    May 29, 2013 at 4:06 am

  8. […] to create the scoped assignment on the Calculations tab of the Cube Editor. If you remember in my post last week about scoped assignments, I recommended avoiding writing scopes using user hierarchies; using only attribute hierarchies the […]

  9. L’ha ribloggato su Marchino BI Land.

    blogchino

    June 1, 2013 at 6:09 pm

  10. 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…

    NigelH

    February 5, 2014 at 4:52 pm

    • 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: https://cwebbbi.wordpress.com/2013/05/29/aggregating-the-result-of-an-mdx-calculation-using-scoped-assignments/

      Chris Webb

      February 5, 2014 at 4:57 pm

      • 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!

        NigelH

        February 6, 2014 at 4:08 pm

  11. […] people love it. However I do use these properties a lot when debugging scoped assignments as seen here. BACK_COLOR is retrieved if you have the Fill Color box checked on the connection properties […]

  12. 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

    Dhruvil Shah

    December 1, 2014 at 11:53 am

    • 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.

      Chris Webb

      December 2, 2014 at 1:35 pm

      • 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?

        Dhruvil Shah

        December 8, 2014 at 4:29 am

  13. 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.

    Aleksandr Merlov

    December 19, 2014 at 5:14 pm

    • 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.

      Chris Webb

      December 20, 2014 at 6:15 pm

      • 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.”

        Aleksandr Merlov

        January 12, 2015 at 1:59 pm

      • 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.

        Chris Webb

        January 13, 2015 at 9:18 am

      • 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.

        Aleksandr Merlov

        January 13, 2015 at 1:43 pm

  14. […] Using Scoped Assignments To Show Calendar and Financial Year-To-Date Values In The Same Calculated M… […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,894 other followers

%d bloggers like this: