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