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
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.