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 :
The new OBIEE function in 11G AGGREGATE AT can leverage the same functionality
To create a level based measure as the amount sold by region, you can:
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)
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:
The query results return null because the query grain is higher than the level of aggregation specific to the level-based measure column.