none
Create a banding dimension that groups by a calculated measure RRS feed

  • Question

  • I want to show a count of orders banded into different amount ranges., ie <1k, 1k-5k,6-10k, etc.  The cube  (SSAS 2008 r2 enterprise) has a reporting currency dimension so I need to do the banding dynamically as each order amount is converted into the selected currency before being grouped into the appropriate band.   I also want to filter by other dimensions such as product or region.  Given this, it is not viable to do the banding calculation in the underlying data source and I assume a calculated measure is most appropriate.

    Your help on what this would look like would be greatly appreciated.


    David
    Wednesday, October 27, 2010 10:46 AM

Answers

  • your collegue is right, adding the All-members is a good idea, otherwise you could be possible that you run into infinite recursions

    as the banding-dimension is not linked to the measuregroup, the all-member (and all other members that are not overwritten using SCOPES) shows the original values and we want to base our banding on the original values that come from our measuregroup

    regarding performance:
    this is a rather complex scenario and every calculation is done at runtime
    first of all you do the currency-conversion which is kinda expensive regarding queryperformance
    then, adding the branding on top of course slows down the performance even more

    i do not know your excact implementation of currency conversion and how many local currencies and/or reporting currencies you have
    but if it is only a small number then you may try to calculated it in the relational DB so you do not need currency conversion in the cube anymore
    this would also solve the branding-problem as you could also do it in the relatinal DB and you would not need any calculations in the cube at all

    greets,
    gerhard


    - www.pmOne.com -
    • Proposed as answer by jaxxnz1 Tuesday, November 2, 2010 11:31 AM
    • Marked as answer by jaxx123 Wednesday, November 3, 2010 9:06 AM
    Tuesday, November 2, 2010 8:39 AM
    Answerer

All replies

  • Hi David,

    In DSV, add a named calculation in DSV to calculate the banding. You can create a fact dimension base on that fact table, the key attribute could be the orderID, and create attribute base on that calculated column. After that, you can do analysis base on that attribute ‘banding’. Of cause, you can use calculated measure, but using named calculation can give you better performance.

     

    Hope this helps,

    Raymond                                          


    Raymond Li - MSFT
    Friday, October 29, 2010 9:20 AM
    Moderator
  • Thanks for your reply Raymond. 

    The field I want to band on is a calculated measure (it takes the order amount and converts it into the selected currency).  If I do it in the DSV I assume I can't use the calculated measure hence the need for it also to be done as a calculated measure.  My problem is I don't know what form the mdx should take.


    David
    Friday, October 29, 2010 9:41 AM
  • here is what i could think of:

    add a new Dimension called Banding in your DSV and add the Bands you need
    add it to your cube but do not link it to your cubes fact-table

    use scope-assignments overwrite the values as follows

    SCOPE([Banding].[Bands].&[<1000], [Measures].[Count of Orders]);

          This=Count(Filter([Order].[Order].[Order].members, [Measures].[Amount]<1000);

    END SCOPE;

     

    SCOPE([Banding].[Bands].&[1000-5999], [Measures].[Count of Orders]);

          This=Count(Filter([Order].[Order].[Order].members, [Measures].[Amount]>1000 AND [Measures].[Amount]<6000);

    END SCOPE;

    [Measures].[Amount] should already contain the amount converted to the selected currency

    in general it should work, but i have not tested it :)

    hth,
    gerhard


    - www.pmOne.com -
    • Proposed as answer by Raymond-LeeModerator Friday, October 29, 2010 10:05 AM
    • Unproposed as answer by jaxx123 Friday, October 29, 2010 11:56 AM
    Friday, October 29, 2010 9:56 AM
    Answerer
  • Gerhard, it seems to be correct apart from 4 flaws.  The 1st of which is very minor.

    1) Missing a closing paranthesis  This=Count(Filter([Order].[Order].[Order].members, [Measures].[Amount]<1000);

     I fixed as follows "This=Count(Filter([Order].[Order].[Order].members, [Measures].[Amount]<1000));"

    2)  You can slice by other dimensions but not by other attributes within the order dimension.  If I attempt this it repeats the values as if the slice did not exist.

    3) I have a band of = 0.  This however picks up nearly all orders regardless of slicing.  I think it is because zero and null are treated the same within AS.

    4) If I add another measure on the pivot, the value is not sliced by the band.  IE I also want to see [Measures].[Amount] alongside the [Measures].[Count of Orders]

    Your help on this is appreciated.

     


    David
    • Edited by jaxx123 Friday, October 29, 2010 11:58 AM spelling
    Friday, October 29, 2010 11:56 AM
  • 1) thx, just a typo by myself :)

    2) try adding EXISING keyword:
       This=Count(Filter(EXISTING [Order].[Order].[Order].members, [Measures].[Amount]<1000));:

    3) 0 and NULL are not treated the same within AS
       AS is optimized to work with NULL, you should only use 0 if it has a real meaning
       the problem you may be facing is the following: if you compare a Measure to a numeric value, the measure is convert to a numeric value to which means that NULL gets converted to 0 for this comparison
       so all orders with a NULL-value (no sales in current slice) get treated as they would have a value of 0
       to avoid this you have to filter out the NULL-values first:
       This=Count(Filter(NonEmpty([Order].[Order].[Order].members, [Measures].[Amount]), [Measures].[Amount]=0));" 
       (you may also have to add EXISTING keyword here)

    4) in the current solution we only SCOPE [MEasures].[Count of Orders]
       thats why you only see values there
       you may try this one:

    SCOPE([Banding].[Bands].&[<1000]);
          This=Aggregate(
                      Filter(
                            NonEmpty(
                                 [Order].[Order].[Order].members
                                 [Measures].[Amount]), 
                            [Measures].[Amount]<1000), 
                      [Measures].currentmember);
    END SCOPE;

     


    - www.pmOne.com -
    Friday, October 29, 2010 12:54 PM
    Answerer
  • Gerhard, I got this to work - thanks to you.  I believe the non-empty was killing the performance so I changed the formula slightly to that shown below.  My collegue suggested adding the [Sales Band].[Band].[All Band] part though I don't understand the purpose of this and whether it improves performance or not.  Can you shed light on this?  Performance is not fantastic but at least it works!  Please reply and I will mark your comment as the answer. 

    scope

     

    ([Sales Band].[Band].&[1]);

    this

     

    = aggregate(

    Filter

     

    (

    Existing

     

    [Order].[Order].[Order].Members,([Measures].[Sell Price],[Sales Band].[Band].[All Band]) = 0 ),measures.currentmember);

    end

     

    scope;

    scope

     

    ([Sales Band].[Band].&[2]);

    this

     

    = aggregate(Filter(

    Existing

     

    [Order].[Order].[Order].Members,([Measures].[Sell Price],[Sales Band].[Band].[All Band]) > 0 AND ([Measures].[Sell Price],[Sales Band].[Band].[All Band]) <= 1000 ),measures.currentmember);

    end scope;

    Saturday, October 30, 2010 4:01 AM
  • your collegue is right, adding the All-members is a good idea, otherwise you could be possible that you run into infinite recursions

    as the banding-dimension is not linked to the measuregroup, the all-member (and all other members that are not overwritten using SCOPES) shows the original values and we want to base our banding on the original values that come from our measuregroup

    regarding performance:
    this is a rather complex scenario and every calculation is done at runtime
    first of all you do the currency-conversion which is kinda expensive regarding queryperformance
    then, adding the branding on top of course slows down the performance even more

    i do not know your excact implementation of currency conversion and how many local currencies and/or reporting currencies you have
    but if it is only a small number then you may try to calculated it in the relational DB so you do not need currency conversion in the cube anymore
    this would also solve the branding-problem as you could also do it in the relatinal DB and you would not need any calculations in the cube at all

    greets,
    gerhard


    - www.pmOne.com -
    • Proposed as answer by jaxxnz1 Tuesday, November 2, 2010 11:31 AM
    • Marked as answer by jaxx123 Wednesday, November 3, 2010 9:06 AM
    Tuesday, November 2, 2010 8:39 AM
    Answerer
  • Thank you very much to share this approach. I have a similar problem, but there is one thing that I can't solve.
    Here you have to "hardcode" the ranges and descriptions in your mdx code. Do you think it's also possible to get those values from the underlying database?
    Thanks!
    Friday, January 20, 2012 7:54 PM