Level Based Measures in OBIEE

First,I would like to explain what is measure. A measure is a column in which aggregate function is applied means any column with an aggregation rule is a measure. eg Amount Sold,Total sales

A level base measure as name state are always calculated at a certain level of  aggregation.for example if  we have a measure called Amount Sold, we can create a Level Based Measure called CategoryAmoundSold which is Amount Sold for a Category . This measure will always return the Amount sold for the category level even if we drill down to a lower level like subcategory etc..

How to create Level based measures

First We need to create a  product dimensional hierarchy in our RPD which contains the level productTotal,ProdCategory,ProdSubcategory,ProductsDetail.

Next,we need to create a logical column categoryAmountSold in fact table in BMM layer.

Right click on fact table->New object->Logical Column

Then give a name to the measure CategoryAmountSold and then setting the level in levels tab, setting of content level is explained in below image.

So now we need to map this measure to the physical source,to map this measure ,below are the steps:

Go to data type tab double click on sales Logical Type sources

 then uncheck “show mapped columns”and  check “show unmapped columns”  ,In our case measure categoryamountsold is unmapped column so we will see this column as shown in below image:

Now click on three dot beside CategoryAmountSold, to map to physical source and then select the physical column from expression builder ,click on insert and then click ok.

So now,categoryAmountSold level based measure has been created and will always return the AmountSold at category level even if we drill down to lower level.

Now let's check ,create a report with level base measure column "CategoryAmountSold"

now,we drill from Category Level to the SubCategory Level(lower level ). But the values for CategoryAmountSold column remain the same as in the previous image even after drilling to a lower level

So this is all about Level Base Measures.

Please Share your Feedback and keep me posted.
Have a nice day.:)

Thanks