none
Measures for Additive vs Non-Additive Data. What is cube design best practice?

    Question

  • I'm looking more for SSAS 2008 best practice design advice, rather than for an answer to a specific question (although I have a specifc set of examples).

    First issue:  Creating a non-additive measure group and an additive measure group.  We have some fact data in our current cube that is additive, and some fact data that is non-additive; all stored in the same fact table.  We do not currently have measures implemented that reflect this aggregation distinction.  Question:  Is it considered good practice to segregate additive and non-additive fact data into a) different fact tables and/or b) different measure groups?  My thought is that it would be an acceptable design approach, but am looking for feedback.

    Second issue:  Non-additive fact data is only available at a non-leaf grain.  The example here is that we have non-additive fact data which is only available for the 4th or 5th levels of our 6-level geography dimension.  Our solution has been to create a custom geography branch, which now essentially serves as our 'aggregation treatment' for non-additive fact data.  I don't believe it's a good practice to have the geography dimension serve this function because we end up having to create a custom geo member for each non-additive fact data element.  Question:  What is considered best practice for storing non-additive fact data which is only available at a non-leaf level?  My thought is to create 'plug members' down to the leaf level within the geography dimension, but would like some feedback.

    Thanks for any advice.

    Chris

    Saturday, September 25, 2010 9:16 PM

Answers

  • First Issue: 

    It should be fine to have the non additive and additive facts in the same fact table as long the grain for both the fact element is the same. If the grain is different, then you might want to save this in separate table.

    With respect to the measure group, facts from different tables are placed in measure groups of their own. I would go ahead with placing the measures in measure group based on the functionality that business domain exposes. Like all sales in one measure group etc.

     

    Second Issue:

    In one of the similar cases, what i did was have the Geo dimension link the fact table as usual with SUM aggregation. And then had a calculated measure over the measure which had a case statement based on Levels of Hierarchy. For Level 4th and 5th output the value and for rest output as null. And then use the calculated measure rather than the original measure. The Aggregation type None, outputs the results as NULL for all and hence this approach.  

    some best practice links are cube design are

    http://msdn.microsoft.com/en-us/library/cc966399.aspx

    SSAS2005PerfGuide.doc


     


    vinu
    Sunday, September 26, 2010 4:05 PM

All replies

  • First Issue: 

    It should be fine to have the non additive and additive facts in the same fact table as long the grain for both the fact element is the same. If the grain is different, then you might want to save this in separate table.

    With respect to the measure group, facts from different tables are placed in measure groups of their own. I would go ahead with placing the measures in measure group based on the functionality that business domain exposes. Like all sales in one measure group etc.

     

    Second Issue:

    In one of the similar cases, what i did was have the Geo dimension link the fact table as usual with SUM aggregation. And then had a calculated measure over the measure which had a case statement based on Levels of Hierarchy. For Level 4th and 5th output the value and for rest output as null. And then use the calculated measure rather than the original measure. The Aggregation type None, outputs the results as NULL for all and hence this approach.  

    some best practice links are cube design are

    http://msdn.microsoft.com/en-us/library/cc966399.aspx

    SSAS2005PerfGuide.doc


     


    vinu
    Sunday, September 26, 2010 4:05 PM
  • Let me know if the explanation given clears ur doubts
    vinu
    Wednesday, September 29, 2010 12:38 PM
  • Vinu,

    Yes, this is helpful input.  We need to give more thought to the measure groups we create, really, which goes to solving my first issue.  I've raised your suggestion on the second issue with one of my engineers; we're in the process of coming up with all the different scenarios under which we have non-additive data in order to evaluate a generalized solution across a couple different cubes in our environment.

    Thanks,

    Chris

    Thursday, September 30, 2010 11:22 PM
  • cool. It would be great if you could mark the post accordingly and also let me know if you need any more help.

     


    vinu
    Friday, October 01, 2010 2:43 PM
  • Can you explain this with an example please of the dimension. 
    MG
    Wednesday, August 10, 2011 9:31 PM