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 54 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

54 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

  8. Hi Chriss, thanks for a useful explanation of SCOPE definitions in this scenario. I continue to be amazed at how hard it is in this day and age to properly control the timing of calculations. Since this is quite a common issue, making an option to “calculate at pre-aggregation time” should really be something Microsoft implemented. :-)

    Anyway.

    First I would like to comment that there is a third option as well that may fit some similar scenarios: Deploying hidden calculated members and then using SOLVE_ORDER. There is a decent article about it on TechNet: http://technet.microsoft.com/en-us/library/ms145539.aspx

    Secondly I would like to ask a question: Can you explain why your proposed solution here requires an “empty measure” in the physical data model? I have a scenario where the physical data model and DSV are outside of my control (at least directly; I can create new user-definded objects in the DSV but not edit existing ones). Is there another way I can use your solution, which does not require making changes to the source tabel or DSV?

    Jesper Sommer

    April 30, 2014 at 11:33 am

    • Hi Jesper,

      Actually there are a lot of good reasons not to pre-calculate calculations like this – it would make the cube very large and processing very slow in a lot of cases.

      This solution needs an empty, physical measure because it does not do any of the aggregation in MDX – it relies on way that physical measures aggregate up automatically. This is much faster than aggregating the result of the calculation in MDX. There is no way of using this approach without making changes to the source table or DSV, but if you can create new objects in the DSV you can create a named calculation that returns null and use that for your measure.

      Chris Webb

      April 30, 2014 at 11:41 am

      • Great. So I can use an object from a DSV object other than the actual datasource? For example create a new DSV object called “ChrissRules” and use that in my Scope definition? Does the object need to be related to the datasource object?

        Jesper Sommer

        May 1, 2014 at 9:40 am

      • I’m not quite sure what you’re saying here – you do not want to create a new Data Source VIew, but you can create a new named calculation in your existing DSV and build your measure from that. A DSV is not the same thing as a data source.

        Chris Webb

        May 1, 2014 at 9:43 am

  9. Sorry I wasn’t more clear :-) What I mean by “data source” is the source database from which a table in the DSV reads data. So what I am getting at is this: Can I create a new table in my existing DSV, name it “ChrissRules”, and insert my named calculation there? Or does my named calculation have to be inserted into the named query which actually reads my source data? All existing named queries in my setup are machine-generated, and any changes I make in them are routinely overwritten. Does this make more sense? :-)

    Jesper Sommer

    May 1, 2014 at 3:19 pm

    • Yes, I understand now, thanks! To add a new measure to an existing measure group, your named calculation needs to be on the named query that your measure group uses. If your DSV is machine generated, that will be a problem.

      Chris Webb

      May 1, 2014 at 4:12 pm

  10. Hi Chris

    Firs of all thanks for the help you are providing to community.

    While building a cube I need to calculate the cumulative sum with one exception. the measure should show the cumulative sum over all the dates except 19000101 which is used for not applicable. when I use
    AGGREGATE(
    {NULL:[Contract End Date].[Calendar Dates].CurrentMember}
    , [Measures].[AVL Amount])
    it gives me cumulative for all the periods including 19000101 which is wrong as we want to exclude the amounts for 19000101. I have modified it to look like this:

    CREATE MEMBER CURRENTCUBE.[Measures].[Cumulative AVL End Date Amount] as “NA”;

    SCOPE ( {[Measures].[Cumulative AVL End Date Amount]});
    SCOPE (EXCEPT([Contract End Date].[Calendar Dates].[Date].MEMBERS
    , {{[Contract End Date].[Calendar Dates].[Date].&[19000101]}}) );

    THIS = IIF(ISEMPTY([Measures].[AVL Amount])
    ,NULL
    ,AGGREGATE(
    {[Contract End Date].[Calendar Dates].[Date].&[20000101]:[Contract End Date].[Calendar Dates].CurrentMember}
    * {NULL:[Contract End Date].[Calendar Weeks].CurrentMember}
    , [Measures].[AVL Amount])
    );

    END SCOPE;
    END SCOPE;

    now it calculates cumulative correctly but only on the date level when I select another level it shows NA.

    Can you please advise how It can be done for all the date levels and all the time hierarchies.

    Thanks in advance.

    Regards

    Harris

    Harris

    May 16, 2014 at 10:51 am

    • Hi Harris,

      I think there’s an easier solution here. I recommend adding a new attribute to your Contract End Date dimension called IsFirstDate that is true for the date 19000101 and false for all other dates. You can avoid the scoped assignment and write your calculation as follows:

      CREATE MEMBER CURRENTCUBE.[Measures].[Cumulative AVL End Date Amount] as
      IIF(ISEMPTY([Measures].[AVL Amount])
      ,NULL
      ,AGGREGATE(
      {[Contract End Date].[Calendar Dates].[Date].&[20000101]:[Contract End Date].[Calendar Dates].CurrentMember}
      * {NULL:[Contract End Date].[Calendar Weeks].CurrentMember}
      , ([Measures].[AVL Amount],[Contract End Date].[IsFirstDate].&[False])
      );

      This should give you the cumulative sum without the 19000101 date and avoids the need for a scoped assignment.

      Chris Webb

      May 16, 2014 at 11:05 am

      • Hi Chris

        Thanks for a quick reply.
        I have created a flag on date dimension and set its value true for 19000101 and false for all the other dates.
        i have modified the calculated measure as:

        CREATE MEMBER CURRENTCUBE.[Measures].[Cumulative End Date AVL Amount]
        AS IIF(ISEMPTY([Measures].[AVL Amount])
        ,NULL
        ,AGGREGATE(
        {[Contract End Date].[Calendar Dates].[Date].&[20000101]:[Contract End Date].[Calendar Dates].CurrentMember}
        * {NULL:[Create Date].[Calendar Weeks].CurrentMember}
        , ([Measures].[AVL Amount],[Contract End Date].[First Date Flag].&[0]))
        );

        it is showing nulls every where for both Calendar Dates and Calendar Weeks hierarchies.

        Regards

        Harris

        Harris

        May 16, 2014 at 1:23 pm

      • Could be a number of things. Can you first try:

        CREATE MEMBER CURRENTCUBE.[Measures].[Cumulative End Date AVL Amount]
        AS IIF(ISEMPTY([Measures].[AVL Amount])
        ,NULL
        ,AGGREGATE(
        {NULL:[Contract End Date].[Calendar Dates].CurrentMember}
        * {NULL:[Create Date].[Calendar Weeks].CurrentMember}
        , ([Measures].[AVL Amount],[Contract End Date].[First Date Flag].&[0]))
        );

        Chris Webb

        May 16, 2014 at 1:34 pm

      • CREATE MEMBER CURRENTCUBE.[Measures].[Cumulative End Date AVL Amount]
        AS IIF(ISEMPTY([Measures].[AVL Amount])
        ,NULL
        ,AGGREGATE(
        {NULL:[Contract End Date].[Calendar Dates].CurrentMember}
        * {NULL:[Create Date].[Calendar Weeks].CurrentMember}
        , ([Measures].[AVL Amount],[Contract End Date].[First Date Flag].&[0]))
        );

        Tried this but the same output i.e. null

        Harris

        May 16, 2014 at 2:00 pm

      • Can you see what happens without the Calendar Weeks hierarchy in the calculation? ie

        CREATE MEMBER CURRENTCUBE.[Measures].[Cumulative End Date AVL Amount]
        AS IIF(ISEMPTY([Measures].[AVL Amount])
        ,NULL
        ,AGGREGATE(
        {NULL:[Contract End Date].[Calendar Dates].CurrentMember}
        , ([Measures].[AVL Amount],[Contract End Date].[First Date Flag].&[0]))
        );

        Chris Webb

        May 16, 2014 at 2:03 pm

      • I have already tried to remove the calender weeks hierarchy bu the result was same.

        I have also tried

        CREATE MEMBER CURRENTCUBE.[Measures].[Cumulative End Date AVL Amount]
        AS IIF(ISEMPTY([Measures].[AVL Amount])
        ,NULL
        ,AGGREGATE(
        {[Contract End Date].[Calendar Dates].[Date].&[20000101]:[Contract End Date].[Calendar Dates].CurrentMember}
        , [Measures].[AVL Amount])
        );

        It gives me correct cumulative numbers but shows the value for 19000101 equal to base measure i.e. [Measures].[AVL Amount], but the cumulative is only calculated on Date level when I roll up to Month, quarter or year level it shows nulls

        Regards

        Harris

        Harris

        May 16, 2014 at 2:31 pm

      • OK, it could be that you have some kind of attribute overwrite problem here that will be very difficult to debug remotely. Another approach could be this:

        CREATE MEMBER CURRENTCUBE.[Measures].[Cumulative AVL End Date Amount] as null;

        SCOPE ( {[Measures].[Cumulative AVL End Date Amount]});
        SCOPE (EXCEPT([Contract End Date].[Contract End Date].[Contract End Date].MEMBERS
        , {{[Contract End Date].[Contract End Date].[Contract End Date].&[19000101]}}) );

        THIS = IIF(ISEMPTY([Measures].[AVL Amount])
        ,NULL
        ,AGGREGATE(
        {[Contract End Date].[Calendar Dates].[Date].&[20000101]:[Contract End Date].[Calendar Dates].CurrentMember}
        , [Measures].[AVL Amount])
        );

        END SCOPE;

        SCOPE([Contract End Date].[Contract End Date].[All]);
        THIS = TAIL(EXISTING [Contract End Date].[Contract End Date].[Contract End Date].MEMBERS, 1);
        END SCOPE;

        END SCOPE;

        I’m assuming here that the attribute hierarchy that the date level of your Calendar Dates user hierarchy is based on is called [Contract End Date].[Contract End Date].

        Chris Webb

        May 16, 2014 at 8:59 pm

      • HI Chris

        I have tried this solution it gives correct cumulative on the date level but again nulls on all other levels.

        Meanwhile after a hit and trial I have found a solution which is working as expected. Here is the mdx:

        CREATE MEMBER CURRENTCUBE.[Measures].[Cumulative End Date AVL Amount]
        AS IIF(ISEMPTY([Measures].[AVL Amount])
        ,NULL
        ,AGGREGATE(
        {NULL:[Contract End Date].[Calendar Dates].CurrentMember}
        * {NULL:[Contract End Date].[Calendar Weeks].CurrentMember}
        * {[Contract End Date].[First Date Flag].&[False]}
        , [Measures].[AVL Amount])
        );

        Now I have to calculate cumulative on other role playing dimensions too. I can create separate calculated measures but then I have long list of measures. I want to calculate single measure for all role playing dimensions. if i use following mdx it gives wrong calculation:

        CREATE MEMBER CURRENTCUBE.[Measures].[Cumulative AVL Amount]
        AS IIF(ISEMPTY([Measures].[AVL Amount])
        ,NULL
        ,AGGREGATE(
        {NULL:[Create Date].[Calendar Dates].CurrentMember}
        * {NULL:[Approval Date].[Calendar Dates].CurrentMember}
        * {NULL:[Contract End Date].[Calendar Dates].CurrentMember}
        * {[Create Date].[First Date Flag].&[False]}
        * {[Approval Date].[First Date Flag].&[False]}
        * {[Contract End Date].[First Date Flag].&[False]}
        , [Measures].[AVL Amount])
        );

        I have seven role playing dimensions I just showed three above for understanding purpose.
        I have checked separate measures and they are working fine but not one measure for all.

        Do you see any possibility for this or its better to use separate measures for each role playing dimension.

        Regards

        Harris

        Harris

        May 17, 2014 at 10:23 am

      • I recommend you create separate measures for each role playing dimension – that will keep things simple, and you would get some potentially strange results if you tried to create one measure for all your dimensions.

        Chris Webb

        May 18, 2014 at 4:11 pm

  11. tHANKS Chris,
    I’m having he following problem. I have two fileds C8 and C9 and i have a calculation AvgAbandonTime=C8/C9. There is a location dimension and it gives me the values based off it. Please see the data set below. But in the grand total insted of the average its giving me sum(c8)/sum(c9). Then i followed your process. Created a real measure in the DSV and followed the above procedure, but still getting the same result.
    Location AvgAbandonTime C8 C9 C8DIVC9
    CITY 92 11588 126 92
    COUNTY 16 157 10 16
    DIV 73 62160 857 73
    GrandTotal 74 73905 993 74

    Bhargav Kandala

    May 19, 2014 at 5:46 pm

  12. Hi Chris,

    How it will work with Parent-Child Hierarchy?

    I have Country dimension (Parent – Child Hierarchy)

    Below sample code I m using :

    SCOPE ([Measures].[Converted Lost Value]);

    SCOPE([Countries].[Countries].Members);

    THIS = SUM(DESCENDANTS([Countries].[Countries].CurrentMember,[Countries].[Countries].[Level 05]),
    ([Measures].[Out of Conformity Value] * ([FE Date].[Fiscal].CurrentMember, [Measures].[Exchange Rate])));

    END SCOPE;
    END SCOPE;

    BJ

    May 26, 2014 at 2:17 pm

    • It should work fine with a parent-child hierarchy, you just need to scope on the key attribute of the dimension.

      Chris Webb

      May 26, 2014 at 9:30 pm

  13. Hi Chris,
    I need to add Customer dimension to have the same behaviour as item. How do I add this into the scope.
    Thanks

    Aleksandar

    May 26, 2014 at 9:49 pm

  14. Please disregard my previous question. I guess I need to just add

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

    Aleksandar

    May 26, 2014 at 9:54 pm

    • I think what you need is this:

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

      Chris Webb

      May 26, 2014 at 10:19 pm

      • Hi Chris and thank you for your quick answer. I really appreciate it since this is kind of an urgent issue.
        Your recommendation will work fine if user chooses date, client and product at the same time.
        What I want is to give them ability to use either date – client or date – item option and aggregate total on both levels. In this case it does not provide correct result.

        Aleksandar

        May 27, 2014 at 3:36 am

      • What my code should do is aggregate from the date, client *and* product granularity in all cases, regardless of what is chosen. I think what you want is to aggregate from client only if client is chosen, but this means you run into a common problem with advanced MDX calculations: something is always chosen from a hierarchy all the time! For this reason I always recommend to my customers that trying to write calculations that do something different depending on what has been chosen should be avoided: it is possible to do this up to a point, but the code is complex, usually slow, and the results turn out to be confusing for end users. It’s better to have different calculations for different scenarios.

        Chris Webb

        May 27, 2014 at 1:06 pm

      • Hm, I need exactly the same behaviour regardless of what user selected (date, client and / or item). I did some testing and it did not look right. Will probably need to do more testing.

        Aleksandar

        May 27, 2014 at 5:16 pm

      • I think I got you. I should have 2 calculations then. One for Date / Client scope and another for Date / Item scope right?

        Aleksandar

        May 27, 2014 at 5:21 pm

      • Yes, exactly

        Chris Webb

        May 28, 2014 at 9:57 pm

  15. Hi Chris,
    It does not work when I try to add more dimensions to have the same calculation:

    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;

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

    Is the second scope overwrites the first one.
    Thanks

    Aleksandar

    May 26, 2014 at 9:58 pm

  16. If measure A and measure B are in two different fact tables, such that

    Measure A is associated with Dimension X
    Measure B is associated with Dimension Y

    then the scope mdx doesn’t seem to work.

    Abhijit

    June 7, 2014 at 2:03 am

    • It is possible to write an assignment that does a calculation on two measures from different fact tables. What do you want to do though?

      Chris Webb

      June 7, 2014 at 10:27 pm

      • I also need product of measures available in the two fact tables. I can create a 3rd fact table based on the 2 existing fact tables but that would create a very big catesian product fact table, which is what I want to avoid.

        Fact1 table –> Country, State, Plan, Price
        Fact2 table –> Country, State, City, Street, Qty

        I need a calculated measure for Price*Qty

        Thanks,
        AJ

        Abhijit

        June 9, 2014 at 7:22 pm

      • This article might help you: http://consultingblogs.emc.com/christianwade/archive/2006/07/25/Measure-expressions_3A00_-how-performant-are-they_3F00_.aspx

        You can do what you want with scoped assignments, but as Christian shows a measure expression might be a better option (if you have Enterprise Edition)

        Chris Webb

        June 9, 2014 at 9:02 pm

  17. Hi Chris,

    Regarding the grand total issue,

    I have close to 15 dimensions in my cube and I have 7-10 calculated measures that are of the form
    [Measures].[A] * [Measures].[B] or [Measures].[A] / [Measures].[B]

    These calculated measures can be seen from all dimensions. Should the scope statement you have mentioned include all the attributes of all the dimensions as users will be using them from the pivot fields?

    If so, the calculation code will be so huge. Also the option where you have suggested to write specific scopes for specific combination also will turn out to be huge as combinations for 15 dimensions are so many

    Can you please suggest an approach?

    Thanks

    Uday

    Uday Kiran

    June 7, 2014 at 6:39 am

    • You could use the Leaves() function here, but in this case why don’t you just calculate the values in your fact table in SQL Server (or whatever RDBMS you’re using)? It would give you the same result.

      Chris Webb

      June 7, 2014 at 10:30 pm

      • Hi Chris,

        Thanks for your reply.

        But does every calculated measure need to be created in sql server or dsv. In my case the calculated measure depends on two different fact tables. Can the cube not handle this via MDX?

        Thanks

        Uday

        Uday Kirab

        June 8, 2014 at 7:54 pm

      • No, certainly not – it’s only in the special cases that this post describes that you need to create calculations in this way. Most calculated measures in MDX are calculated after the real measures have been aggregated – only a few types of calculation need to be aggregated after the calculation has taken place.

        Chris Webb

        June 8, 2014 at 8:15 pm

  18. I had the same issue where end users were publishing “sales price” and “quantity” to the cube. At the input level the “sales amount” = “Sales Price” * “Quantity”. This needs to be handle as a two pass calc. So all I did was scope at the child level of the Dimension ID that linked from the hierarchy table to the measure table. Did this calc then did the calculate. Worked great. Here’s what it looks like. If you have more dimension tables that are at a lower level of granularity I would assume you just add their attributes keys to this scope statement

    SCOPE ([Detail].[Forecast Id].children);
    [Measures].[Sales]
    = IIF([Measures].[Qty]=0,NULL,[Measures].[Selling Price] * [Measures].[Qty]);
    END SCOPE;

    CALCULATE;

    Matthew

    July 31, 2014 at 1:50 pm

  19. Chris, Thanks for this magnificent post as usual, I’ve really benefited from it.
    However I couldn’t understand why to use a real measure not a calculated measure. That part of the post was not clear to me. would you please explain it more? especially what do you mean by “the value of the assignment will aggregate up outside of the original scope” ? what is the original scope? can you please make it simpler?

    Thanks again :)

    Tabban

    August 8, 2014 at 12:59 pm

    • It has to be a real measure because only real measures aggregate up – calculated measures don’t. Think of a very simple hierarchy with an All Member and two members, A and B, beneath it. For a real measure, the All Member is the aggregated value of A and B; this is not the case for a calculated measure.

      To answer the second part of your question, using the same hierarchy as in the previous paragraph, if you create a scoped assignment on member A then for a real measure the All Member’s value will change so that it is still the aggregated value of A and B.

      Chris Webb

      August 8, 2014 at 9:07 pm

      • Thanks Chris, just to make sure that I’ve understood you correctly. Do you mean that if the wanted measure = [measure X]*0.5, and using your example hierarchy:

        – In real measure case, the All Member = (([A],[measure X])*0.5) + (([B],[measure X])*0.5)
        – While in calculated measure case, the All Member = ([ALL],[measure X])*0.5

        Is this what do you mean? and, does any case (real or calculated measure) has better performance?

        Tabban

        August 9, 2014 at 9:00 am

      • Yes, you are completely correct. Regarding performance, the calculated measure will perform better because it is doing less work.

        Chris Webb

        August 10, 2014 at 8:53 am


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,146 other followers

%d bloggers like this: