Oracle BI EE 10.1.3.4.1 – Number of Members in a Level & Aggregate Navigation

Venkatakrishnan J -

One of the less known/less used capabilities of BI EE is the ability to switch between Aggregate tables based on the number of members in a level. For example, lets consider the screenshot shown below

In most cases, we will not have the necessity to alter the number shown above. But there are cases(especially in Aggregate navigation) where this number can have a significant effect on the logical table source selection. For example, lets consider a very simple use case containing 3 main fact tables (1 main fact and 2 aggregate facts)

  1. SALES Fact – This contains the lower level data across 3 main dimensions (Channel, Promotions and Time).

  1. SALES (Year/Channel Class Fact ) – This contains aggregated data of the SALES fact table at the Year level of Time dimension and Channel Class level of Channel dimension.

  1. SALES (Year/Promotions Category Fact) – This contains aggregated data of the SALES fact table at the Year level of Time dimension and the Promotions Category level of the Promotions dimension

In this example, i basically have 3 main dimensions with the following hierarchy

Lets consider a case wherein an end user is trying to create a report containing the Year Column from Time dimension and a measure (that is present in all 3 aggregate tables) as shown below

Since both the aggregate tables can provide data for this report, how does BI Server determine which logical table source to source the data from? This is where the number of members in a level comes into play. BI Server looks at the ratio of number of members across levels in each dimension to determine the optimal aggregate table. Since this is not documented completely, following is how i think this works based on internal testing

  1. For this to be useful, each aggregate table should have the same number of joining dimensions (but the dimensions themselves can be different as in the above case).

  2. BI Server during the query run-time, extracts the number of members from each non-participating dimension (in the query) and chooses that aggregate table that has less number of members at the joining level.

For example, in the above use case, the first aggregate table SALES (Year/Channel Class) Fact has Channel as the non-participating dimension. So BI Server during run time extracts number of members at the joining level (Channel Class) and then compares with the number of members at the joining level of the SALES (Year/Promotion Category) Fact (which is the number of members at Promotion Category level). Whichever is lower, BI Server chooses that aggregate table. But when there are multiple non-participating dimensions, BI Server takes the largest number from the number of members within  the various non-participating dimensions of the aggregate tables and chooses the one with the lowest of the highest values.

In the above example, lets first enter the values of 2 and 100 for Channel Class level and Promotion Category level as shown below & then run the report.

If we run the report and then look at the SQL, you will notice that the SALES (Year/Channel Class) aggregate table is chosen since Channel Class has a lower value for the number of members in the joining level

WITH
SAWITH0 AS (select T1982.CALENDAR_YEAR as c1,
     sum(T10560.AMOUNT_SOLD) as c2,
     T1982.CALENDAR_YEAR_ID as c3
from
     TIMES T1982,
     SALES T10560 /* SALES - Year/Channel Class */
where  ( T1982.TIME_ID = T10560.TIME_ID )
group by T1982.CALENDAR_YEAR, T1982.CALENDAR_YEAR_ID)
select SAWITH0.c1 as c1,
     SAWITH0.c2 as c2
from
     SAWITH0
order by c1

Lets switch the number of members in such a way that Promotion Category level has a lesser number than Channel Class.

If we now run the report again, you will notice that the SALES (Year/Promotion Category) aggregate table will be used.

WITH
SAWITH0 AS (select T1982.CALENDAR_YEAR as c1,
     sum(T10573.AMOUNT_SOLD) as c2,
     T1982.CALENDAR_YEAR_ID as c3
from
     TIMES T1982,
     SALES T10573 /* SALES - Year/Promotion Category */
where  ( T1982.TIME_ID = T10573.TIME_ID )
group by T1982.CALENDAR_YEAR, T1982.CALENDAR_YEAR_ID)
select SAWITH0.c1 as c1,
     SAWITH0.c2 as c2
from
     SAWITH0
order by c1

The joining level in the BMM layer can be different from the actual joining level in the physical layer. Depending on how we want the navigation to happen we can switch the numbers accordingly. This sometimes can be very useful especially when you are dealing with Oracle OLAP 10g where we can mimic aggregate tables using a single view. We can control the selection of the logical table sources by varying the numbers of members in the levels.