OBIEE 10G/11G - Level-Based Measure Calculations

About

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

The calculation of this measure is independent of the query grain and used always the aggregation grain of the logical column.

Level-based measures allow :

to return data at multiple levels of aggregation (the query grain and the level-based column grain) with one single query
to create share measures (percentage), that are calculated by taking some measure and dividing it by a level-based measure. For example, you can divide salesperson revenue by regional revenue to calculate the share of the regional revenue each salesperson generates.

The new OBIEE function in 11G AGGREGATE AT can leverage the same functionality

Articles Related

How to create a level based measure

To create a level based measure as the amount sold by region, you can:

make a copy of the Amount Sold logical column
and then
- drag the logical column into its hierarchy level
- or set the aggregation grain in the level tab of the logical column property.

A level-based measure

with a lowest query grain

With a lowest query grain, each query that requests these column will return the amount aggregated to its associated levels (in our case by region)

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

with a higher query grain

When a query includes a level-based measure column, and the query grain is higher than the level of aggregation specific to the column, the query results return null. Note that in releases previous from 11g, results were returned for this situation, but they were not deterministic.

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 10G/11G - Nqquery.log (Query logging)

Configuration

By default, each level of a dimension hierarchy shows both:

dimension columns that are assigned to that level,
and level-based measures that have been fixed at that level. If you don't want this behaviour, you can check the “Hide Level Based Measures” options (Tools/Options in the Administration Tool).

Support

The query results return null

The query results return null because the query grain is higher than the level of aggregation specific to the level-based measure column.

Reference

Documentation