none
Non Additive Measures in SSAS 2008R2

    Question

  • Hi,

    We have defined a cube in SSAS 2008 R2 enterprise edition. We have 9 dimensions and 3 fact tables. In one of our fact table we have Invoice id is as a fact. So we are trying to define Invoice id as Non additive measure in cube and tried with aggregation function as NONE. But when ever we are trying ti query the Invoice ID it is showing NULL. At the same time if we change aggregation function to SUM, it is showing aggregated value. But for our detail reporting we should not get aggregated value for Invoice id in report. Please any one can help us on the same?

     

    Thanks & Regards,

    Chinni

    Sunday, March 13, 2011 6:29 PM

Answers

  • Hi Vinu,

    I am sorry for late reply. Thank you for your valuable support to resolve the issue. I have defined the degenerate dimension for Invoice ID and issue is resolved.

     

    Thanks & Regards,

    Chinni

    Monday, March 21, 2011 5:21 AM

All replies

  • 1. You need to use all the Key attributes when you use the measure with aggregation type as None.

    Example: If your Invoice Measure group is related to product and geography dimension using product id and geography id, then in the browser you should drop both the product id and geography id and then the measure. Then it show the values. Then additionally you can drop any other attributes, but the key attribute is important.

    Example: Create a measure and in the dimension usage just associate the product dimension with product id as the key. Now add the measure in the browser and also the product name. No value comes. Now drop the Product Id, the value comes.

    2. Also what i have observed is if the time dimension (server time) is added, i.e. In Dimension usage, link Measure's DateId with the Date attribute of Period Dimension, and then dropped the period dimension, it appears to not come or it shows NULL. Now when i do "Show Empty Cells" and then browse down against the date it appears for the product. You can drop on column axis and you will be able to see easily.

    So the key is you need to include all the key attributes that is used against the measure group in which the measure belongs to.

    To simplify, you can initially have only one dimension linked to the measure group and then see the behavior. Then keep adding one by one dimensions. Also make sure that Show Empty Cell is checked. (Right click on browser)


    vinu
    Monday, March 14, 2011 12:14 AM
  • Hi Vinu,

    Thank you for your reply. I have date dimension and Time dimension in my cube. If I add dateid and time id, then I am able to see the correct data, even I am using SUM aggregate function. But the problem is, if I add all the key attributes in the report, report is going to be hanged and I am getting error like the operation has been cancelled due to memory presure. Also I have tried with separete dimension for InvoiceID, But no luck. So I thought non additive measure concept will resolve the problem. But as per your reply, even non additive also we have to add all the key attributes to get the corerct result.

    But as per my observation if i add all the key attributes, we will get correct results in our scnario,  even if we are not using aggregation function NONE.

    In general if we have Invoice id as a measure and want to build the detail report like daily transactions , how we can define in cube?

    Thanks & Regards,

    Chinni

    Monday, March 14, 2011 7:33 AM
  • Mostly we end up having a degenerate dimension from the fact for cases like InvoiceId, Invoice number, order number, transaction number etc.
    vinu
    Monday, March 14, 2011 7:22 PM
  • Hi Vinu,

    I am sorry for late reply. Thank you for your valuable support to resolve the issue. I have defined the degenerate dimension for Invoice ID and issue is resolved.

     

    Thanks & Regards,

    Chinni

    Monday, March 21, 2011 5:21 AM
  • Good. Can you please mark the post as Answered appropriately.
    vinu
    Monday, March 21, 2011 12:39 PM