Chris Webb's BI Blog

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

Aggregating the Result of an MDX Calculation Using Scoped Assignments

with 12 comments

I don’t usually like to blog about topics that I think other people have blogged about already, but despite the fact that Mosha blogged about this several years ago (in fact more than eight years ago, blimey) this particular problem comes up so often with my customers and on the MSDN Forum that I thought I should write something about it myself. So apologies if you know this already…

Here’s the problem description. If you define a calculated measure in MDX, that calculation will take place after the real measure values have all aggregated. For example, consider a super-simple cube with a Year dimension, two real measures called A and B and a calculated measure called [A * B] that returned the value of A multiplied by B. In a PivotTable you’d see the following result:

image

Note how the Grand Total for the [A * B] calculated measure is 12*16=192, and not 12+12+12+12=48. This is expected behaviour for calculated measures, and indeed the way you want your calculations to behave most of the time.

However, there are some scenarios where you want to do the calculation first and then aggregate up the result of that calculation; in our previous example that means you’d get 48 for the Grand Total instead. Currency conversion and weighted averages are common examples of calculations where this needs to happen. How can you handle this in MDX?

Let’s look at a slightly more complex example than the one above. In the following cube, based on Adventure Works data, I created a Date dimension that looks like this:

image

image

…and a Product dimension that looks like this:

image

image

I also created two measures on a fact table called A and B:

image

Now, let’s say that once again you want to calculate the value of A*B at the Date and Product granularity, and aggregate the result up. Probably the easiest way of handling this would be to do the calculation in the fact table, or in the DSV, and then bringing the result in as a new real measure. However this may not be possible with some types of calculation, or if the granularity that you want to do the calculation is not the same as the granularity of the fact table.

One way of approaching this in MDX would be to create a calculated measure like this:

CREATE MEMBER CURRENTCUBE.MEASURES.[CALC] AS
SUM(
DESCENDANTS([Date].[Calendar].CURRENTMEMBER, [Date].[Calendar].[Date])
*
DESCENDANTS([Product].[Category - Product].CURRENTMEMBER, [Product].[Category - Product].[Product])
, [Measures].[A] * [Measures].[B]);

image

The big problem with this approach (apart from the fact that it may break when you do a multi-select in certain client tools – but you could work around that) is that it is usually very, very slow indeed. Depending on the calculation, it may be unusably slow. So you need a different approach.

This is where scoped assignments come in. If you make a scoped assignment to a real measure, as opposed to a calculated measure, then the value of the assignment will aggregate up outside of the original scope. So, in this case, since you want the calculation to take place at the Date and Product granularity, if you scope on a real measure at that granularity the result of the calculation will aggregate up automatically.

The first step here is to create a new real (ie not calculated) measure for the calculation. This can be done in the DSV by creating a named calculation on your fact table which returns the value NULL:

image

You then need to create a new real measure on your measure group from this new named calculation:

image

In this example, I’ve left the AggregateFunction property of the measure to be the default of Sum, but you could use a different setting if you wanted a different type of aggregation. The next step is to process the cube, and once you’ve done that you’ll see a new measure that only returns the value 0:

image

Next, you need 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 scope statement becomes:

SCOPE([Measures].[A Multiplied By B]);
    SCOPE([Date].[Date].[Date].MEMBERS);
        SCOPE([Product].[Product].[Product].MEMBERS);
            THIS = [Measures].[A] * [Measures].[B];
        END SCOPE;
    END SCOPE;
END SCOPE;

image

One very important thing to notice: the sets I’ve used for scoping on the Dates and Products do not include the All Member: for example, [Date].[Date].[Date].MEMBERS. If you use a set that includes the All Member, such as [Date].[Date].MEMBERS, the calculation will not aggregate up correctly.

Here’s the result:

image

This is going to be much more efficient than the pure MDX calculated measure approach, though just how well the calculation performs will depend on the complexity of the calculation and the size of the area that you are scoping on.

Written by Chris Webb

May 29, 2013 at 5:11 pm

12 Responses

Subscribe to comments with RSS.

  1. Hi Chris,

    Checking your blog is my daily work just like checking emails. I would like to see all your posts whether it is a new topic or a repeated one. Personally, I think your explanation is straight forward, clear and precise. It helps me learn new things as well as remember “already know but nearly forgot” stuff. I’m looking forward to seeing your MDX course in Sydney.

    Regards,
    George

    George Qiao

    May 30, 2013 at 2:05 am

  2. Hi chris

    I have calculated memebrs in my cube like this.

    - I created dummy columns in my named queries like “calc memb profit”, “calc memb profit2″, “calc memb profit3″ etc.. with value as 0.

    - Then I calculated values for these columns through scope statement like this

    SCOPE ([MEASURES].[CALC MEMB PROFIT]);

    THIS = ;

    END SCOPE;

    this way i have created many calculated measures… and referred them in other calculated measures for example

    SCOPE ([MEASURES].[CALC MEMB PROFIT2]);

    THIS = iif([MEASURES].[CALC MEMB PROFIT] > x,0,[MEASURES].[CALC MEMB PROFIT1]-[MEASURES].[CALC MEMB PROFIT];

    END SCOPE;

    some thing like this…

    Now these calculations are doing fine, but they take really long time while retrieving results.. (more than 5 mins some times)

    Can you please suggest how can I improve performance for these queries.

    Thanks in advance

    sri

    May 30, 2013 at 6:21 am

    • It’s going to be all down to tuning the expressions in the calculated members now, I’m afraid. One thing though: in your IIF you’re returning 0 and you should really be returning null, which may improve performance. Try something like:

      THIS = iif([MEASURES].[CALC MEMB PROFIT] > x,null,[MEASURES].[CALC MEMB PROFIT1]-[MEASURES].[CALC MEMB PROFIT];

      Chris Webb

      May 30, 2013 at 6:45 am

      • Thanks a lot for your response chris. I have defined dummy columns in named query SQL, not used new named calculation wizard. If define them as new named calculation, will there be any improvement in performance or both are one and the same.

        sri

        May 30, 2013 at 7:19 am

      • Hi chris, Thanks a million.. Your null suggestion has improved performance by 80%. I have been struggling with this issue for the past 2 weeks and now its almost fixed. Thanks to you from the bottom of my heart.

        sri

        May 31, 2013 at 6:43 am

  3. Thanks Chris.
    I like the idea of using named calculations in the DSV for the new measure; in the past I have created an empty table in the RDB as a source for empty measures to use in scope assignments, but I think the DSV is a better approach. I also concur with George Qiao’s comments.

    Philip Taylor

    May 31, 2013 at 1:18 pm

  4. So glad you took the time to blog this Chris. I have a member from 2 big fact table measures that, I thought, was going to require pre-aggregating and consolidating in the RDBMS (complete redesign). This is a much better solution than the ones I’ve (unsuccessfully) tried implementing the past few days. Most were similar to your calculated member example and were either SLOW or didn’t cover all scopes. This is simple and it preforms extremely well!

    brian

    October 30, 2013 at 2:18 pm

  5. Good evening to Mr.Webb and all people reading my post…

    I have the same problem Mr. Webb explained here, getting wrong values with grand total with calculated member…(I have a calculated member depending on a PARENT-CHILD Dimension..all works fine except GRAND TOTAL…Both in Excel and Browsing cube…)

    I tried to do a test creating a NAMED CALCULATION in DSV and then a real measure basis on it.

    But i have problems with creating NAMED CALCULATION..
    I have SQL SERVER 2008 R2 Standard Version..
    Is it possibile to creare a NAMED CALCULATION in DSV also with this version ???
    I do not think so ,,,,Icon in DSV Designer is not active..!!!!
    So I tried to insert that named calcolation manually inside the query of fact table…as below..

    Select , null AS
    from

    I was able to deploy all successfully but when i run OLAP CUBE , it gets an error and tell that
    the name of calculated name i created is invalid…but the name is very simple is CAMPO1 …(what’s wrong?)
    Is it a problem of SSAS ‘Version ???

    Thanks in advance.

    mauro

    November 9, 2013 at 10:10 pm

    • No, it’s not a problem with the version of SSAS – all versions and editions allow you to create named calculations in the DSV. Something else must be wrong?

      Chris Webb

      November 9, 2013 at 10:17 pm

  6. Hi, Chris. Great post.
    Do you know if there is an easy way to control solve order if you have a number of these named calculations?

    Eric

    December 2, 2013 at 9:16 pm

    • The solve order will be dictated by the order that these calculations appear on the Calculations tab of the cube editor

      Chris Webb

      December 2, 2013 at 11:19 pm

  7. Hi Chris, I have a question.

    If I create a calculated measure then the totals are wrong. Now I understand why this is but if I scope the calculation then I can only view the correct aggregation at that scoped level, which makes sense. So how can I create custom calculated measures where I can get the correct total but avoid having to restrict users to slicing by specified attributes to get the total to work. It seems to me like Microsoft have really fallen short of the mark here in terms of implementation of totals. They should really offer the ability to do this special scoping for specific cases but also offer the ability to calculate the total based on the data delivered to the cube ( making the total separate from the cube space ). I don’t know if I am missing something crucial but it just seems as though a calculation involving various measures is far too complicated that what it should be.

    Wayne

    March 13, 2014 at 9:27 pm


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 2,868 other followers

%d bloggers like this: