Pentaho Analysis - Mondrian
  1. Pentaho Analysis - Mondrian
  2. MONDRIAN-962

Create support for Non-Additive and Semi-Additive Measures

    Details

    • Type: Improvement Improvement
    • Status: Open
    • Priority: Severe Severe
    • Resolution: Unresolved
    • Affects Version/s: 3.6.x (5.0.0 GA Backlog)
    • Fix Version/s: Mondrian Backlog
    • Component/s: None
    • Labels:
      None
    • Customer Case:
    • Notice:
      When an issue is open, the "Fix Version/s" field conveys a target, not necessarily a commitment. When an issue is closed, the "Fix Version/s" field conveys the version that the issue was fixed in.

      Description

      Please create support for Non-Additive measure creation in the schema.

      It is very difficult to go creating measures that handle all the roll-up and non-roll up cases all along the hierarchies. This would be handled much better schema side by standardized ways of declaring them.

        Issue Links

          Activity

          Hide
          Diethard Steiner added a comment -

          I'd like to add another example:

          Imagine you have to prepare a subscriber base analysis for a telecom provider. You have to obvious date dimension, tariff plan dimension, etc.

          So you can sum the subscriber base on a daily level, but once you roll up to weekly, monthly, quarterly or yearly level, you have to take the first or last value of this period (whatever the client is happy with really - averages usually don't do the trick). There should be a way in Mondrian to define such a semi-additive measure as well.

          Show
          Diethard Steiner added a comment - I'd like to add another example: Imagine you have to prepare a subscriber base analysis for a telecom provider. You have to obvious date dimension, tariff plan dimension, etc. So you can sum the subscriber base on a daily level, but once you roll up to weekly, monthly, quarterly or yearly level, you have to take the first or last value of this period (whatever the client is happy with really - averages usually don't do the trick). There should be a way in Mondrian to define such a semi-additive measure as well.
          Hide
          Dan Keeley (codek) added a comment -

          I'd like to +1 Diddys comment above, i've had exactly the same situation also at a Telco (Different one to Diddy), and that was exactly how they wanted to do it.

          Show
          Dan Keeley (codek) added a comment - I'd like to +1 Diddys comment above, i've had exactly the same situation also at a Telco (Different one to Diddy), and that was exactly how they wanted to do it.
          Hide
          Vladislav Krakhalev added a comment -

          So guys, I can suggest you for some cases use function CLOSINGPERIOD couple with brackets () while this functionality haven't been made. For example : (CLOSINGPERIOD([date.daily].[day_of_month]), Measures.msr_balance_sum). At last example we take last balance for given period. And as I found on pentaho forums with mdx functions you can emulate semi-additive behavior in most cases.

          Full code of balance measure:
          <CalculatedMember name="balance_sum" formatString="#,##0.00" caption="%

          {balance_sum}

          " description="%

          {balance_sum.description}

          " formula="(CLOSINGPERIOD([date.daily].[day_of_month]), Measures.msr_balance_sum)" dimension="Measures" visible="true">

          Show
          Vladislav Krakhalev added a comment - So guys, I can suggest you for some cases use function CLOSINGPERIOD couple with brackets () while this functionality haven't been made. For example : (CLOSINGPERIOD( [date.daily] . [day_of_month] ), Measures.msr_balance_sum). At last example we take last balance for given period. And as I found on pentaho forums with mdx functions you can emulate semi-additive behavior in most cases. Full code of balance measure: <CalculatedMember name="balance_sum" formatString="#,##0.00" caption="% {balance_sum} " description="% {balance_sum.description} " formula="(CLOSINGPERIOD( [date.daily] . [day_of_month] ), Measures.msr_balance_sum)" dimension="Measures" visible="true">
          Hide
          Diethard Steiner added a comment -

          @ Vladislav Krakhalev: Excellent, thanks a lot! I just tested this and for the subscriber base scenario I mentioned above this works very well.

          Show
          Diethard Steiner added a comment - @ Vladislav Krakhalev: Excellent, thanks a lot! I just tested this and for the subscriber base scenario I mentioned above this works very well.
          Hide
          Diethard Steiner added a comment -

          Ok, as my scenario is working now thanks to Vladislav's suggestion, I thought it might be worth writing a blog post about this:
          http://diethardsteiner.github.io/mondrian/2014/08/12/Mondrian-Semi-Additive-Measures-Workaround.html

          Show
          Diethard Steiner added a comment - Ok, as my scenario is working now thanks to Vladislav's suggestion, I thought it might be worth writing a blog post about this: http://diethardsteiner.github.io/mondrian/2014/08/12/Mondrian-Semi-Additive-Measures-Workaround.html

            People

            • Assignee:
              Unassigned User
              Reporter:
              Brandon Jackson
            • Votes:
              24 Vote for this issue
              Watchers:
              27 Start watching this issue

              Dates

              • Created:
                Updated: