Oracle BI EE 10.1.3.4.1 – Time Based Aggregation – Essbase Equivalence – Part 1
Posted by Venkatakrishnan J on May 17, 2009
Right now i am in the process of comparing certain Essbase related functionalities in BI EE and vice versa. One of the main reasons why i am doing this is to understand how the feature of one product can be leveraged while working with the other, now that more and more customers seem to have both of them together. While doing a quick comparison, one of the key Essbase functionalities that came to the forefront was the ability to do Time based aggregations. Any Essbase user would be aware of the fact that both Block Storage and Aggregate Storage (to an extent) essbase cubes support time based aggregations. Time based aggregations are nothing but aggregations of measures wherein the aggregation rule varies for the time dimension alone. There are 3 types of time based aggregations
1. Time Based Last – In some cases, when we are at a year level, we would like to see the amount sold for the last month(December) alone instead of a summation of all the months. But the aggregation for all the other dimensions (and their levels) would be SUM.
2. Time Based First – In some cases, when we are at a year or a quarter level, we would like to see the amount sold for the first month of that quarter or year instead of a summation of the all the child months for that quarter/year. But the aggregation for all the other dimensions would be SUM.
3. Time Based Average – In some cases, when we are at a year or a quarter level in the Time dimension, we would like to see the amount sold as an average of all the corresponding child months for that quarter/year. But the aggregation for all the other dimensions would be SUM.
Just to explain this a bit further, lets consider the screenshot below
As you see, we basically have 2 dimensions. One is Product and the other is Year. In the report containing Top Level of Product and Year Level of Time, the report has produced a number which is nothing but sum of amount sold for all the products in the last month of that Year (December). So, basically there are 2 kinds of aggregation for the same measure. The method for achieving this though is pretty simple, i wanted to introduce this here so that this can act as a pre-cursor to even more complex dimension based aggregations which i would show in the coming blog entries. To achieve this, open up the repository and go to the aggregation tab of the measure that you are reporting against.
Now choose the based on dimensions option. In the aggregation list first choose Other Dimensions and enter the aggregation as SUM. After that choose the Time dimension and enter the aggregation as LAST.
Ensure that the order of the dimensions listed above are the same. So basically what this does is, it sets the aggregation of the measure for all the dimensions apart from Time as SUM. For Time alone it would do a LAST aggregation. The order of the dimensions ensure that the LAST operation is carried out after the SUM.
Now create a report on Time and Product. You would notice that whenever upper levels of Time dimension is queried upon, it would result in LAST aggregation.
If you look at the actual query fired, you would notice that TIME based LAST aggregation would be performed on a Summed up product dimension.
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from (select distinct D1.c2 as c1, D1.c3 as c2, LAST_VALUE(D1.c6 IGNORE NULLS) OVER (PARTITION BY D1.c4, D1.c5 ORDER BY D1.c4 NULLS FIRST, D1.c5 NULLS FIRST, D1.c7 NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as c3, D1.c4 as c4, D1.c5 as c5 from (select T3663.TOTAL_PRODUCT_DSC as c2, T3683.YEAR_DSC as c3, T3663.TOTAL_PRODUCT_ID as c4, T3683.YEAR_ID as c5, sum(T3720.UNITS) as c6, T3683.MONTH_ID as c7 from PRODUCT_DIM T3663, TIME_DIM T3683, UNITS_FACT T3720 where ( T3663.ITEM_ID = T3720.ITEM_ID and T3663.TOTAL_PRODUCT_DSC = 'Total Product' and T3683.MONTH_ID = T3720.MONTH_ID ) group by T3663.TOTAL_PRODUCT_DSC, T3663.TOTAL_PRODUCT_ID, T3683.MONTH_ID, T3683.YEAR_DSC, T3683.YEAR_ID ) D1 ) D1 order by c1, c2
The above was pretty straightforward. But there is one more similar use case of Time based properties in Essbase which is more tougher to simulate in BI EE against a relational source. Time based aggregation can vary on different products. For example, for one product we would need Time Based aggregation of LAST, the other FIRST and the other AVERAGE. In such cases the above cannot be used as the aggregation is constant across a dimension(in the example above). In the above case, we had the same TB property for all members within a dimension. We shall see how to go about achieving such complex members based other dimension Time Based Aggregations in a future blog entry( in Part 2)