OBIEE – Level-Based Measures

Level-based measures are created to perform calculation at a specific level of aggregation. They allow to return data at multiple levels of aggregation with one single query. It also allows to create share measures.

Example

Let us say there is a company XYZ Electronics which sells its products in many regions, countries and cities. Now the company President wants to see the total revenue at country level - one level below region and one level above cities. So total revenue measure should be summed up to the country level.

These type of measures are called level-based measures. Similarly, you can apply level-based measures on the time hierarchies.

Once the dimension hierarchies are created, level-based measures can be created by double clicking on the total revenue column in the logical table and setting the level in the levels tab.

Create Level-Based Measures

Open the repository in offline mode. Go to File → Open → Offline.

Select .rpd file and click open → Enter repository password and click Ok.

In BMM layer, right-click on Total Revenue column → New Object → Logical column.

It will open the logical column dialog box. Enter the name of logical column total revenue. Go to column source tab → Check derived from existing columns using an expression.

Once you select this option, expression edit wizard will be highlighted. In expression builder wizard, select the logical table → Column name → Total revenue from the left side menu → Click OK.

Now go to level tab in logical column dialog box → Click on logical dimension to select it as grand total under logical level. This specifies that the measure should be calculated at grand total level in the dimension hierarchy.

Once you click OK → Total Revenue logical table will appear under the logical dimension and Fact tables.

This column can be dragged to presentation layer in the subject area to be used by end users to generate reports. You can drag this column from fact tables or from logical dimension.