Level Based Measure and Report Totals in OBIEE 11G
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.
Create Level Based Measure
In this scenario column “Amount Sold by Region” is a level based measure. In the column “Amount Sold by Region”, amount sold is calculated at the region level. In the below figure even though the country name is changing, the region is Europe (constant) and hence Europe will have the same “Amount Sold by Region” for different country names.
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.
Method 1 (with a lowest query grain)
With a lowest query grain, each query that requests these columns 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).
Method 2 (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.
How Will Report-Based Totals Created in Previous Releases Be Upgraded?
In previous releases, you had the ability to create report-based totals in table views. Because report-based totals are handled slightly differently in this release, you might notice a difference in totals as follows:
- If the previous table included all report-based totals, then all measure columns and attribute columns in the upgraded table will use the Default option with the Report-Based Total option.
- If the previous table view included a mix of report-based totals and non-report-based totals, then all measure columns and attribute columns in the upgraded table will use the Default option with the Report-Based Total option.
- You can work around the upgraded totals manually. If you want to use the same measure value as in the previous release, then duplicate the measure column in the table and use the Aggregation Rule menu to specify a non-report-based total.
- If the previous table view included all non-report-based totals, then all measure columns and attribute columns in the upgraded table will continue to use non-report-based totals.
Query for Reference
SELECT Data1.c4 AS column1, Data1.c2 AS column2, SUM(Data1.c1) OVER (partition BY Data1.c4) AS column3, Data1.c1 AS column4 FROM (SELECT SUM(T245.AMOUNT_SOLD) AS column1, T175.COUNTRY_NAME AS column2, T175.COUNTRY_REGION AS column4 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 ) Data1 ORDER BY column1, column2
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 behavior, you can check the “Hide Level Based Measures” options (Tools/Options in the Administration Tool).
The query results return null because the query grain is higher than the level of aggregation specific to the level-based measure column.