Level Based Measure in OBIEE

A level-based measure is a column whose values are always calculated to a specific level of  aggregation. For example, a company might want to measure its revenue based on the country, based on the region, and based on the city. You can set up columns to measure CountryRevenue, RegionRevenue, and CityRevenue.

To set up these calculations, you need to build a dimensional hierarchy in your repository that contains the levels Grandtotal, Country, Region, and City. This hierarchy will contain the metadata that defines a one-to-many relationship between Country and Region and a one-to-many relationship between Region and City. For each country, there are many regions but each region is in only one country. Similarly, for each region, there are many cities but each city is in only one region.

Next, you need to create the logical column CountryRevenue in fact table  in BMM layer.
Right click on the fact table -> New Object -> Logical Column.

Then, Give a name to the measure, then under Aggregation tab, select the appropriate aggregation rule(SUM is the Aggregation for the measure CountryRevenue, in my case), then under the Level tab, select the appropriate logical level (country is the logical level for the measure CountryRevenue, in my case). Then say OK.

Repeat the same process for the other tow measures (RegionRevenue, and CityRevenue).

Now map the these logical measures to the physical source (Revenue is the physical source for these measures in my case.) To map the column CountryRevenue to the physical source column Revenue, follow the below steps :

Double click on the measure, called CountryRevenue, goto Data Type.You will find the logical Table source, double click on it.

Uncheck the option Show Mapped Columns, then all the unmapped columns will be displayed. In our case CountryRevenue,RegionRevenue, and CityRevenue are the unmapped columns. So, you should be able see all these columns there.

Now click on the dotted button (highligted in above picture) beside the measure CountryRevenue to map to physical source column. Then select the appropriate phisical source column from the Expression Builder and say OK.

Repeat the same procedure for other two columns.

Final step is, make sure that all the three measures should be listed under the corresponding logical Level in the dimension. In my case, the CoutryRevenue must listed under Country logical level, RegionRevenue must listed under Region logical level, CityRevenue must listed under City logical level in the dimension Product.

These measures are specific to the defined logical level. So CountryRevenue measure will always gives us the Revenue to the Country level, even if you add the Region dimension column in between Country and CountryRevenue  columns. RegionRevenue measure will always gives us the Revenue to the Country level, even if you add the City dimension column in between Region and RegionRevenue  columns.CityRevenue measure will always gives us the Revenue to the City level.

Thank you,