Friday, October 19, 2012

SSAS - Understanding Non Aggregate Measures


Recently I have been asked a question, I have defined my measure with aggregate function as “None” and all the values are returned as Blank. What is the reason for this? So I have created a below demo to explain the concept of “None” aggregate function and how it works. Most you may be having the same question. So I am documenting this here to help you to understand about this.

Below is the sample model which will be used for demonstrating this



I have created a cube using the wizard with the above data model.  Below is the structure of the cube

Dimension : Product 
Attributes :ProductId
Dimension : Region
                Attributes :RegionID
Dimension :Chk  (Dummy Dimension)
                Attributes :ChkId
Measures : Sum(SoldQty)
                BillNumber (No aggregate)
Measures :  Sum(Valx) (Dummy Measure)


The cube is deployed and processed. Now in the browser, let us take a look how this measure behaves.
Now when using Region Dimension alone measure BillNumber is returning blank.





Now let us try adding the Product Dimension also inside. But still we could see only blanks in the BillNumber.



But if you try expanding for each region you will start seeing the Billnumber values.



The reason for this is in the first two tries there was an attribute at the level “All” . As we have mentioned BillNumber as NoAggregate , there will not be any value available for a “All” member.
Now you may get a question that in the first screenshot we have used Region at the granular level only. But why BillNumber is still Blank ?  As RegionId has been used in the query , by default ProductId will be at the “All” level. In the below MDX query , the product dimension is not used in the query , But by default it “All” member has been  considered for getting the values.



But in the last screen shot , we have the leaf level of all the attributes available in the cube so the BillNumber measure displays the value.Does this means do we need all the dimension attributes in the query to display the no aggregate measure ? Not necessary if you notice we have one other dummy dimension DimChk and other dimension attribute Color also. Without using the chk dimension attributes they will be considered as All member. But still we are getting the BillNumber measure displaying the value.This makes clear that we need to have attributes which is having the IDs as keys which are linked to the fact table which has this measure. In the above example if we use the ProductID&RegionIDattributes , No aggregate measure BillNumber will display the value.






If we remove one of the key attribute values, the bill number will start displaying Blank.



Conclusion

No Aggregate Measures will display values only when attributes with all keys linked to the fact is available in the MDX query or browser as it displays the leaf level data.

I have uploaded the SSAS project and relevant scripts in the below link for your reference.



Hope this blog is helpful .Will meet you next week with another blog. So far I am happy that I could keep up my resolution on doing a blog per week and hope I will be able to keep it up JJJJ.

6 comments:

  1. Thanks that was exactly what I was looking for, now it's clear

    ReplyDelete
  2. Hi,
    Here the Bill Number is Unique for the Region and Product Combination. If we have multiple records for this combination (Region and Product), the bill number value is automatically summing up. Could you please help me if any other way to solve this

    ReplyDelete
  3. Thanks for this post. I changed the Key attribute for a dimension, and changed the aggregation setup to enable the business key rather than the surrogate key to be the root of all of the aggregations. I can now put the unique business key into the report and leave the surrogate key out and the non aggregating measure now shows correctly.

    ReplyDelete
  4. This comment has been removed by a blog administrator. our sclinbio.com

    ReplyDelete
  5. This is the good website and also i love this website https://sclinbio.com

    ReplyDelete