OBIEE - Nested aggregate measure

Purpose / What is a nested aggregated measure ?

This article talk about 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 ?

If you perform explicit aggregation (SUM, AVG, MIN, MAX, or COUNT) on a measure column (default aggregation rule defined in the Aggregation tab of the Logical Column) through a Logical SQL request, you are actually asking for an aggregate of an aggregate.

This article talks about two types of nested aggregate measure :

a simple nested aggregated measure, an aggregate of an aggregate as for example : the sum of the amount sold summed by year.
and a complex nested aggregated measure, an aggregate of a conditional aggregate as for example : the sum of customer by region with a turnover greater than 20 000. In this case, you have to add an extra conditional step before performing your last aggregate action.

Articles Related

Aggregate of an aggregate

Let's take this example : The sum of the amount sold summed by year.

It's a sum of a sum.

OBIEE manage this kind of nested aggregation by using in the same time the BI Server and the BI Presentation service calculation.

The first sum have to be define/and perform by the OBI Server through a measure column, then the second sum is computed by the presentation service through a formula. To have a complete example, check out this article on the paragraph “Aggregation with a Measure Columns” : Aggregation rule with a measure column

Complex Nested Aggregate : Aggregate of a conditional aggregate

Example :

The number of customers by region who have a turn-over greater than a certain amount

Number of customer by region with a turnover greater than 20 000

To calculate this measure, you have to do for example :

a sum on the turn-over by customer and by region
a case statement to retrieve the customer with a big amount (greater than = 1, minus = 0)
and once again, you must perform a sum of this selection

You have then 3 actions to perform on the data set :

first a sum aggregation,
then a case statement
and to finish another sum aggregation.

The only way to create this kind of aggregate measure is to use the pivot view of answer, because in a pivot, you can exclude some column and the BI presentation server perform then an other aggregation. The BI server alone is not able for the moment to provide this functionality. See the test repository section below.

The OBIEE Implementation

To create the measure above, in answer, we must choose the column :

Customer.“Country Region”
Customer.“Cust Id”
“Sales Facts”.“Amount Sold”

Then transform the column “Amount Sold” by a formula column with this statement :

case when sum("Sales Facts"."Amount Sold" by Customers."Cust Id") > 20000 then 1 else 0 end

We retrieve 1 if the amount by customer is greater than 1 and 0 for the opposite.

Don't forget to set the aggregation rule to sum

You must have in you answer the column Customer.“Cust Id” because it's used in the formula. If you don't have it, the BI server will perform unexpected result or you can also create in place of “sum(“Sales Facts”.”Amount Sold“ by Customers.”Cust Id“)” a level based measure in the repository

It remains to perform a sum of this column by region and for this purpose, we have to create a pivot view and to exclude the Customer Id column as below :

In the query log, you will see that the BI server give only the sum by customer id and by region. Therefore, the BI Presentation service do the other operation (the case and the sum).

select T175.COUNTRY_REGION as c1,
     T186.CUST_ID as c2,
     sum(T245.AMOUNT_SOLD) as c5
from 
     SH.COUNTRIES T175,
     SH.CUSTOMERS T186,
     SH.SALES T245
where  ( T175.COUNTRY_ID = T186.COUNTRY_ID and T186.CUST_ID = T245.CUST_ID ) 
group by T175.COUNTRY_REGION, T186.CUST_ID
order by c2, c1

The test in the repository

In this part, we just try to create Nested Aggregate Measure but it never works.

In the logical column

 SUM( CASE  WHEN  SUM(SH.Salesfacts."Amount Sold") > 20000 THEN 1 ELSE 0 END )

OBIEE doesn't support in the BI Server this kind of modelling because you will receive this error if you check the global consistency :

ERRORS:
BUSINESS MODEL SH:
[14041] Error in measure definition for column #BigCustomer. 
Nested aggregate measure definitions are currently not supported.

Aggregation based on Dimension

 SUM( CASE  WHEN  SUM(SH.Salesfacts."#BigCustomer") > 20000 THEN 1 ELSE 0 END )

If you check the global consistency, you will not receive any error but the SQL query generated in not the expected one :

select D1.c2 as c1,
     sum(D1.c3) as c2
from 
     (select D1.c1 as c2,
               D2.c2 as c3
          from 
               (select distinct T175.COUNTRY_REGION as c1
                    from 
                         SH.COUNTRIES T175
               ) D1,
               (select case  when sum(T245.AMOUNT_SOLD) > 20000 then 1 else 0 end  as c2
                    from 
                         SH.COUNTRIES T175,
                         SH.CUSTOMERS T186,
                         SH.SALES T245
                    where  ( T175.COUNTRY_ID = T186.COUNTRY_ID and T186.CUST_ID = T245.CUST_ID ) 
               ) D2
     ) D1
group by D1.c2
order by c1