locked
Measure which should be not aggregatable RRS feed

  • Question

  • Hi,

    I have created a measure with Aggregation Properties to "None". This measure is linked with 2 dimensions (Time and Countries which is Parent/Child Hierarchy).
    When I browse the cube, sub-totals still appear except for the first level of the hierarchy.
    Does anyone know why ?
    Should I create a custom rollup member for this ? I don't want any total for this measure.

    thanks
    Sandrine
    Monday, June 29, 2009 3:27 PM

Answers

  • Hi Sandrine,

     

    I think you need to set the IsAggretable property for the parent-child hierarchy, not setting the aggregate function for the measure.

     

    Double click the Countries dimension, right-click Parent/Child hierarchy in the Attributes pane of the Dimension Structure tab, and then click Properties. Scroll to the IsAggregatable property, and then click False in the property’s drop-down list. After that, the subtotal/total will not appear.

     

    Aggregate function for a measure determines how to display the value along with the dimensions, it will change the fact value aggregate behavior, has no relationship with subtotal/total of a dimension. And if you select None for the aggregation, no aggregation is performed, and all values for leaf and nonleaf members in a dimension are supplied directly from the fact table for the measure group that contains the measure. If there is no value, it will show NULL. More information, see:

    http://msdn.microsoft.com/en-us/library/ms175623.aspx#AggFunction

     

    Hope this helps.

    Raymond

    • Marked as answer by Raymond-Lee Friday, July 10, 2009 6:21 AM
    Thursday, July 2, 2009 2:42 AM
  • Setting Aggregatable to false will remove the "All" member and level, but is it not clear if that is what you are asking about.

    With Parent-Child hierarchies you have leaves at every level and you can assign data at every level. So if you have a hiearchy that goes

    Country
      - State
        - City

    It is possible to assign rates at all three levels, not just the City level. So this issue may have something to do with the way that you are loading your fact table. Can you post a simple example of what you are seeing and what you would like to see?
    http://geekswithblogs.net/darrengosbell - please mark correct answers
    • Marked as answer by Raymond-Lee Friday, July 10, 2009 6:21 AM
    Thursday, July 2, 2009 2:56 AM

All replies

  • Hi,
    do not know if I am the best to help but I give it a try

    check that there is no other calculation interfering with your query.
    check that there is no data on aggregated levels ofr your parent child hierarchy.
    Did you hand write the query in management studio - cube browsers mdx introduces visual totals which could generate those values.

    Aggregation None means there is not aggregated value - you just get data on the leave of the measuregroupdimensions (leaves of your two dimensions). If this is your requirement then none is the best selection. If you just want no aggregation of the parent child but want an aggregation over the time (year - quater - month)  then you have to go by custom roleup (better unary operators) or mdx script.

    Best regards
    Hannes
    http://www.hmayer.net/
    Monday, June 29, 2009 5:07 PM
  • Hi,

    Thanks for your answer. I used the cube browser not a query in management Studio. I will check with a query or a report through reporting services.
    Tuesday, June 30, 2009 2:22 PM
  • Hi Sandrine,

     

    I think you need to set the IsAggretable property for the parent-child hierarchy, not setting the aggregate function for the measure.

     

    Double click the Countries dimension, right-click Parent/Child hierarchy in the Attributes pane of the Dimension Structure tab, and then click Properties. Scroll to the IsAggregatable property, and then click False in the property’s drop-down list. After that, the subtotal/total will not appear.

     

    Aggregate function for a measure determines how to display the value along with the dimensions, it will change the fact value aggregate behavior, has no relationship with subtotal/total of a dimension. And if you select None for the aggregation, no aggregation is performed, and all values for leaf and nonleaf members in a dimension are supplied directly from the fact table for the measure group that contains the measure. If there is no value, it will show NULL. More information, see:

    http://msdn.microsoft.com/en-us/library/ms175623.aspx#AggFunction

     

    Hope this helps.

    Raymond

    • Marked as answer by Raymond-Lee Friday, July 10, 2009 6:21 AM
    Thursday, July 2, 2009 2:42 AM
  • Setting Aggregatable to false will remove the "All" member and level, but is it not clear if that is what you are asking about.

    With Parent-Child hierarchies you have leaves at every level and you can assign data at every level. So if you have a hiearchy that goes

    Country
      - State
        - City

    It is possible to assign rates at all three levels, not just the City level. So this issue may have something to do with the way that you are loading your fact table. Can you post a simple example of what you are seeing and what you would like to see?
    http://geekswithblogs.net/darrengosbell - please mark correct answers
    • Marked as answer by Raymond-Lee Friday, July 10, 2009 6:21 AM
    Thursday, July 2, 2009 2:56 AM
  • In addition to the helpful answers that have been sent your way, I found helpful guidelines on mssqltips, tip 1773:

    http://www.mssqltips.com/tip.asp?tip=1773 which may enhance the excellent, detailed comments that you have received thus far.


    Regards,


    Methody Myers
    • Proposed as answer by Raymond-Lee Tuesday, July 7, 2009 8:21 AM
    Thursday, July 2, 2009 4:33 PM