none
Wrong Total in Calculated Cube Measure MDX

    Question

  • I have three measures in my cube called LBs, Qty Each and Units.  Right now these are calculated in the datamart.  I am adding new functionality to the cube to calculated these three measures on the fly as they use a conversion rate that will change over time, and I always want the latest and greatest calculation.  I added a measure group with the conversion rates.  I am running into several issues and am hoping someone can help me out.

    My first approach to the calculation was to delete LBs, Qty Each and Units from my data source view and add three new calculated measures called LBs, Qty Each and Units. I used the following calculation to do this:

    Lbs:  [Measures].[Qty Case] * [Measures].[Conversion Rate] * [Measures].[Coffee Weight]

    This worked great, but then I ran into issues with the totals being incorrect because it was summing the rates and weights and multiplying them by the case amount. 

    So then I figured out if I left in the three fields in my data source view, and added a scope statement identifying a leave to do the calculation on, the totals came out just fine.  So I changed my calculation to be like this:

    Scope([Measures].[LBs]);

    Scope(Leaves([Product));

    This = [Measures].[Qty Case] * [Measures].[Conversion Rate] * [Measures].[Coffee Weight];

    End Scope;

    End Scope;

    And then my totals worked out perfectly.  I have a large stack of reports that use these measures. When I went to test them, I ran into more issues.  If I needed to write any MDX and bring in additional dimensions, the performance was horrible. I am assuming this is because those dimensions are referenced in my leaves part of the statement.  For example:

    This runs fine:

    SELECT NON EMPTY {[Measures].[LBs]} ON COLUMNS, NON EMPTY {([Product].[Product].[Product].ALLMEMBERS)} ON ROWS FROM [MyCube]

    This does not run fine:

    SELECT NON EMPTY {[Measures].[LBs]} ON COLUMNS, NON EMPTY {([Product].[Product].[Product].ALLMEMBERS * [Customer].[Customer].[Customer].ALLMEMBERS)} ON ROWS FROM [MyCube]

    So I figured I would add the Customer dimension to the Scope(Leaves()) section of the calculation and get an error about different levels of granuality.

    In the end all I want to do is have the measures calculate on the fly and have the grand totals be correct. I would like the calculated measure to be available against every dimension where a relationship exist in my DSV.  Any help is much appreciated

    Thursday, June 14, 2012 1:58 PM