Hitachi Vantara Pentaho Community Forums
Results 1 to 10 of 10

Thread: Support for non-additive and semi-additive measures

  1. #1
    Join Date
    Mar 2008
    Posts
    5

    Default Support for non-additive and semi-additive measures

    We have non-additive and semi-additive Measures in our cube. We cannot aggregate these Measures because they are generally relative counts, percentages, ratios etc. or otherwise meaningless when aggregated by Time and/or Location (two of our primary dimensions).

    Ideally we'd want to do this in the cube definition e.g.
    <MyMeasure aggregator="none">

    However Mondrian forces a known aggregator (SUM, COUNT, AVG, ...) and doesn't support "none" (and the aggregator property is mandatory).

    The problem then is that the XMLA document produced for any MDX query includes aggregations. The aggregations are meaningless to our users and so we're obliged to parse them out (with custom code) so that the user doesn't see them. That's obviously not a good solution.

    Is there a cleaner (more appropriate) way to avoid having Mondrian generate *any* aggregates for Measures which are non-additive or semi-additive and just return us the raw values in the XMLA? (Ideally this would be reasonably transparent to the OLAP client).

    Thanks,

    - Paul




    [/font]

  2. #2
    Join Date
    Nov 1999
    Posts
    1,618

    Default

    As you have noted, Mondrian does not have built-in support for non- and semi-additive measures. However, you can build calculated measures that are based on stored measures and which roll up exactly how you want. Hide the stored measures and use the calculated measures in your reports.

    Here's an MDX query on the FoodMart schema that contains a non-additive and semi-additive measure.

    Code:
    with member [Measures].[Non Additive Unit Sales] as
      case when [Time].Level is [Time].[Month]
         and [Gender].Level is [Gender].[Gender]
         and [Customers].Level is [Customers].[Name]
         and [Product].Level is [Product].[Product Name]
      then [Measures].[Unit Sales]
      else null
      end
    member [Measures].[Semi Additive Unit Sales] as
      case when [Time].Level is [Time].[Month]
      then [Measures].[Unit Sales].Value
      else ClosingPeriod([Time].[Month]).Value
      end
    select
       {[Measures].[Unit Sales],
         [Measures].[Non Additive Unit Sales],
         [Measures].[Semi Additive Unit Sales]}
       * [Gender].Members ON COLUMNS,
      [Time].Members ON ROWS
    from [Sales]
    The non-additive measure only has a value when you are at the lowest level of every single dimension (I omitted a few dimensions to keep the example short, but you get the idea). In my experience MDX queries always have some degree of aggregation, and this measure would not contain values in those queries.

    The semi-additive measure adds on all dimensions except Time, and for values of Quarter and Year, it takes the value of the last Month in that period.

    You can take the same approach to build measures with more complex rollup behaviors.

  3. #3
    Join Date
    Mar 2008
    Posts
    5

    Default

    Ingenious. Thanks, this makes sense and should work for our case.

  4. #4
    Join Date
    Feb 2007
    Posts
    230

    Default Semi additive problem

    Hi,

    I am trying to calculate the "balance" measure that should be summed across all dimensions except time. Time dimension has months and days and the value for the month should be the average of balances across days.

    The problem is, for example: I have several accounts. The balances across the accounts should be summed but they are averaged instead. So I must be doing some stupid mistake. I have tried defining the calculated measure as follows:

    <Measure name="StanjeSum" column="stanjekn" visible="false" aggregator="sum" formatString="#,##0; -#,##0"/>
    <Measure name="StanjeAvg" column="stanjekn" visible="false" aggregator="avg" formatString="#,##0; -#,##0"/>
    <CalculatedMember name="Stanje" caption="STANJE" dimension="Measures">
    <Formula>CASE WHEN [Period].Level IS [Period].[Months] THEN [Measures].[StanjeAvg].Value ELSE [Measures].[StanjeSum].Value END</Formula>
    <CalculatedMemberProperty name="FORMAT_STRING" value="#,##0; -#,##0"/>
    </CalculatedMember>

    This is not it, right? I have tried with the following too:

    <Formula>CASE WHEN ([Period].Level IS [Period].[Months]) THEN AVG(DESCENDANTS([Period].CurrentMember,[Period].[Days]),[Measures].[StanjeSum].Value) ELSE [Measures].[StanjeSum].Value END</Formula>

    but the result is the same...

    Where am I wrong?

    thanks very much on any help

    regards, dejan

  5. #5
    Join Date
    Feb 2009
    Posts
    4

    Default Adding calculated members

    Hi,

    Is there a way to add CalculatedMembers when they are related to the "Measures" dimension? (I'm looking for something like the aggregator for Measures).

    In my case, i have some leaf nodes for wich i calculate a formula, but for the rest of the tree i want to add the child levels instead.

    Thanks,

    Fernando

    Quote Originally Posted by jhyde View Post
    As you have noted, Mondrian does not have built-in support for non- and semi-additive measures. However, you can build calculated measures that are based on stored measures and which roll up exactly how you want. Hide the stored measures and use the calculated measures in your reports.

    Here's an MDX query on the FoodMart schema that contains a non-additive and semi-additive measure.

    Code:
    with member [Measures].[Non Additive Unit Sales] as
      case when [Time].Level is [Time].[Month]
         and [Gender].Level is [Gender].[Gender]
         and [Customers].Level is [Customers].[Name]
         and [Product].Level is [Product].[Product Name]
      then [Measures].[Unit Sales]
      else null
      end
    member [Measures].[Semi Additive Unit Sales] as
      case when [Time].Level is [Time].[Month]
      then [Measures].[Unit Sales].Value
      else ClosingPeriod([Time].[Month]).Value
      end
    select
       {[Measures].[Unit Sales],
         [Measures].[Non Additive Unit Sales],
         [Measures].[Semi Additive Unit Sales]}
       * [Gender].Members ON COLUMNS,
      [Time].Members ON ROWS
    from [Sales]
    The non-additive measure only has a value when you are at the lowest level of every single dimension (I omitted a few dimensions to keep the example short, but you get the idea). In my experience MDX queries always have some degree of aggregation, and this measure would not contain values in those queries.

    The semi-additive measure adds on all dimensions except Time, and for values of Quarter and Year, it takes the value of the last Month in that period.

    You can take the same approach to build measures with more complex rollup behaviors.

  6. #6
    Join Date
    Feb 2009
    Posts
    4

    Default

    Thanks a lot !


    Quote Originally Posted by fembrioni View Post
    Hi,

    Is there a way to add CalculatedMembers when they are related to the "Measures" dimension? (I'm looking for something like the aggregator for Measures).

    In my case, i have some leaf nodes for wich i calculate a formula, but for the rest of the tree i want to add the child levels instead.

    Thanks,

    Fernando

  7. #7
    Join Date
    Jul 2008
    Posts
    23

    Default

    I tried this query and it doesn't work. The values are wrong to the second column.

    Code:
     
     
    with member [Measures].[Semi Additive Unit Sales] as 
    'CASE WHEN ([Time].Level IS [Time.Quarter].[Month]) THEN [Measures].[Unit 
    Sales].Value ELSE ClosingPeriod([Time.Quarter].[Month]).Value END'
     
    member [Measures].[Semi Additive Store Cost] as 'CASE WHEN ([Time].Level IS 
    [Time.Quarter].[Month]) THEN [Measures].[Store Cost].Value ELSE ClosingPeriod
    ([Time.Quarter].[Month]).Value END'
     
    select {[Measures].[Semi Additive Unit Sales],[Measures].[Semi Additive Store 
    Cost]} ON COLUMNS,
      [Time].Members ON ROWS
    from [Sales]
    The values from both columns are the same and should be different because the Measures where different.

    Thanks

    bsalvador

  8. #8

    Default

    I also have the issue of both columns displaying the same value instead of the value for each measure. Did you find a solution? Is the issue with the MDX?

    Quote Originally Posted by bsalvador View Post
    I tried this query and it doesn't work. The values are wrong to the second column.

    Code:
     
     
    with member [Measures].[Semi Additive Unit Sales] as 
    'CASE WHEN ([Time].Level IS [Time.Quarter].[Month]) THEN [Measures].[Unit 
    Sales].Value ELSE ClosingPeriod([Time.Quarter].[Month]).Value END'
     
    member [Measures].[Semi Additive Store Cost] as 'CASE WHEN ([Time].Level IS 
    [Time.Quarter].[Month]) THEN [Measures].[Store Cost].Value ELSE ClosingPeriod
    ([Time.Quarter].[Month]).Value END'
     
    select {[Measures].[Semi Additive Unit Sales],[Measures].[Semi Additive Store 
    Cost]} ON COLUMNS,
      [Time].Members ON ROWS
    from [Sales]
    The values from both columns are the same and should be different because the Measures where different.

    Thanks

    bsalvador

  9. #9
    Join Date
    Jan 2017
    Posts
    1

    Default

    Hi @dgambin,

    I have got the same problem for sum and avg. Did you find any solution for this? If yes can you please let me know how you have done this case. Thanks very much on any help.
    I will be very helpful if you have any material as am new to mondrian stuff.

    Thanks....Sai...


    Semi additive problem

    Hi,

    I am trying to calculate the "balance" measure that should be summed across all dimensions except time. Time dimension has months and days and the value for the month should be the average of balances across days.

    The problem is, for example: I have several accounts. The balances across the accounts should be summed but they are averaged instead. So I must be doing some stupid mistake. I have tried defining the calculated measure as follows:

    <Measure name="StanjeSum" column="stanjekn" visible="false" aggregator="sum" formatString="#,##0; -#,##0"/>
    <Measure name="StanjeAvg" column="stanjekn" visible="false" aggregator="avg" formatString="#,##0; -#,##0"/>
    <CalculatedMember name="Stanje" caption="STANJE" dimension="Measures">
    <Formula>CASE WHEN [Period].Level IS [Period].[Months] THEN [Measures].[StanjeAvg].Value ELSE [Measures].[StanjeSum].Value END</Formula>
    <CalculatedMemberProperty name="FORMAT_STRING" value="#,##0; -#,##0"/>
    </CalculatedMember>

    This is not it, right? I have tried with the following too:

    <Formula>CASE WHEN ([Period].Level IS [Period].[Months]) THEN AVG(DESCENDANTS([Period].CurrentMember,[Period].[Days]),[Measures].[StanjeSum].Value) ELSE [Measures].[StanjeSum].Value END</Formula>

    but the result is the same...

    Where am I wrong?

    thanks very much on any help

    regards, dejan

  10. #10
    Join Date
    Feb 2007
    Posts
    230

    Default

    Hi

    This was a long time ago, but the only thing I see is that I have a slightly different calculated member, something like:

    <Measure name="BalanceSum" column="balance_value" visible="false" aggregator="sum" formatString="#,##0; -#,##0"/>
    <CalculatedMember name="Balance" caption="Balance" dimension="Measures">
    <Formula>CASE [time].Level WHEN [time].[Days] THEN [Measures].[BalanceSum].Value ELSE Avg(Descendants(
    [time].CurrentMember, [time].[Days]), [Measures].[BalanceSum].Value) END</Formula>
    <CalculatedMemberProperty name="FORMAT_STRING" value="#,##0; -#,##0"/>
    </CalculatedMember>

    regards, dejan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.