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 cpcking 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 offpne mode. Go to File → Open → Offpne.

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

In BMM layer, right-cpck 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 highpghted. In expression builder wizard, select the logical table → Column name → Total revenue from the left side menu → Cpck OK.

Now go to level tab in logical column dialog box → Cpck 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 cpck 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.