Level Based Measures (LBMs)

Post date: 13-Apr-2010 14:07:50

Now that i am back from quite a good weekend, i thought i would blog about something which is pretty interesting i.e. Level based Measures. Lets try to understand this first. This is what the documentation has got to say about this “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. “ As stated above Level based measures have their aggregation set based on a level in the hierarchy. Lets try to understand this using a simple example. We will start with a simple hierarchy as shown below.

Basically, we have a hierarchy and we would like to have the Sales measure aggregated over 3 levels i.e. Total Level, Region Level and the Country level. Now lets start with creating 3 logical measure columns Sales By Region, Sales By Country and Total Sales. Easch of these measures are exact replica of the Sales Measure which has a default aggregation of SUM.

Now, in order to make the 3 columns to aggregate over Region, Country and Total levels just drag and drop the corresponding columns to their associated levels as shown below.

Now, try creating a report with all the 4 columns(including the base SUM aggregated Sales measure) and see what happens.

As shown above what happens is Sales By Country calculates the sales across all the countries. Sales By Region calculates the sales across all the regions. And Total sales gives total sales across all the regions and countries. But you would see a lot of line items because of the granularity at which the Sales By Country Works. Since there are 17 different countries you would see 17 different line item sums. Now, if you remove the Sales By Country column the number of line items gets reduced to the number of regions i.e. 5

This could be a lot useful in situations where you want to a division of different LBM’s for example, Sales By Region/Sales By Country.

Ref: http://gerardnico.com/wiki/dat/obiee/bi_server/design/fact_table/level_based_measure_calculations

About

A level-based measure is a column whose values are always calculated to a specific level of aggregation.

Level-based measures allow :

• • to return data at multiple levels of aggregation with one single query

  • to create share measures, that are calculated by taking some measure and dividing it by a level-based measure to calculate a percentage. For example, you can divide salesperson revenue by regional revenue to calculate the share of the regional revenue each salesperson generates.

How to create a level based measure

To create a measure as the amount sold by region, you need to create one logical column. These column uses the logical column Amount Sold as its source. So, you can make a copy of the Amount Sold logical column. The Amount Sold column has a default aggregation rule of SUM and has sources in the underlying databases.

You then drag the logical column into its level or set it in the level tab of the logical column property.

An answer with a lowest level

Each query that requests these column will return the amount aggregated to its associated levels (in our case by region)

How to have a good total ?

To obtain the good total, you have to uncheck the aggregate option : Report-Based Total (when applicable)

In a table view :

In a pivot view :

Query Performed

And only ONE query is performed :

SELECT D1.c4 AS c1,      D1.c2 AS c2,      sum(D1.c1) over (partition BY D1.c4)  AS c3,      D1.c1 AS c4 FROM       (SELECT sum(T245.AMOUNT_SOLD) AS c1,                T175.COUNTRY_NAME AS c2,                T175.COUNTRY_REGION AS c4           FROM                 SH.COUNTRIES T175,                SH.CUSTOMERS T186,                SH.SALES T245           WHERE  ( T175.COUNTRY_ID = T186.COUNTRY_ID AND T175.COUNTRY_REGION = 'Europe' AND T186.CUST_ID = T245.CUST_ID )            GROUP BY T175.COUNTRY_NAME, T175.COUNTRY_REGION      ) D1 ORDER BY c1, c2

OBIEE - How to see the SQL generated by a request (The query log) ?

ROW_NUMBER or SUM() OVER functions

In your queries, if you start noticing Partition By using ROW_NUMBER or SUM() OVER functions then that means level based measures are being used somewhere (not in all cases but in most of them).