Level based measures are built with the help of the hierarchies in BMM layer. If we need to have aggregated values in reports for example Country Sales, Monthly Sales etc we need to have level based measures. Share can also be calculated with the help of level based measures. Following is the procedure to create a level based measure with the example of sales of a Country.
1) There should be hierarchy built for the level based measures.
2) Duplicate the column Sales in the fact and rename it as country sales.
3) Add new column to hierarchy (country>state>city) under the level country.
4) Transfer the new column to the associated table in the presentation layer.
In the front end whenever country sales is selected in a request the data for this column would be aggregated value of sales for country.
Thanks for this post, this is very short and crisp and understandable.
ReplyDeleteIf possible please let me know the answers of:
1. Why we use Level Based Measure and in which scenario?
2. Difference between Measures and Level Based Measures?
Thanks in advance!
Regards....Amitesh
1) We use level based measure to obtain aggregated values based on a dimension.
ReplyDelete2) Measures are the columns in a Fact table aggregated by Sum, Count etc.,
Please check this very good article by Gerard Nico for detailed explanation
http://gerardnico.com/wiki/dat/obiee/measure_level_based