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

Bi Server Architecture With Client

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

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.
Obiee Logical Column Level Region Obiee Level Based Data Type Column

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)

Obiee Level Based Measure Rapport

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

Obiee 11g Bi Server Options Hide Level Based Measure

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





Discover More
Essbase Overview
Essbase - Suppression of interdimensional irrelevance by splitting the database

Interdimensional irrelevance occurs when many members of a dimension are irrelevant across other dimensions. Essbase defines irrelevant data as data that Essbase stores only at the summary (dimension)...
Bi Server Architecture With Client
OBIEE - Fact table (of logical fact table)

Dimension tables are expected to store columns that cannot be aggregated whereas fact tables are expected to store measure columns that can be aggregated. As a general rule: don’t include...
Obiee Fragmentation Content One Column
OBIEE - Grain - Level of (summarization|aggregation)

Grain definition in the context of OBIEE. The following list describes the different grains in navigating a query: Query grain. The grain of the request. Aggregation grain. The grain of the aggregate...
Obiee Measures Aggregation
OBIEE - How to create a measure from data that is stored in a dimension table

You may: need to create some count aggregation on your dimension attribute. have some column in your dimension that hold measures such as the working day for a time dimension. In this case, we are...
Obiee Pivot Sort On Column Total
OBIEE - How to sort on the column grand total value of a pivot view ?

In a pivot view, you don't have any options to choose the sort order on the column grand total (Amount sold in our case). To sort on a measure column (Direct Sales for instance), see this article : ...
Obiee Nested Aggregate Case When Formula
OBIEE - Nested aggregate measure

This article talk nested aggregate measure in OBIEE and try to found the best way to implement it. Before continuing, we have to answer to this simple question : what is a nested aggregated measure...
Bi Server Architecture
OBIEE - Query Compiler

The query compiler is responsible of the compilation of a logical sql in the query processing process of BI Server. The query compilation is composed of the following five phases: parsing, , navigation,...
Bi Server Architecture With Client
OBIEE - Repository Design

... .. A consideration to take when designing a subject area is to pay attention at the final user. Do you design for a user:OBIEE Analytics/Reporting...
Obiee Multiple Dimension Hierarchie
OBIEE 10G/11G - Dimension Hierarchy (logical dimensions)

(Dimension hierarchies|Logical Dimensions|Hierarchy) introduce formal hierarchies into a business model. They are created in the Business Model and Mapping layer. Before 11g, end users can't see them...
Obiee11g Time Dimension Property
OBIEE 10G/11G - How to set up the time dimension (for time series functions Ago, Todate, ) ?

Oracle BI Server ships Time Series Conversion Functions : AGO and TODATE. They are supported for all relational database and for use with Essbase. They are not support for flat files. To use them on...



Share this page:
Follow us:
Task Runner