OBIEE 11.1.1.3 and 11.1.1.5: different approach to AGO function

Hi again.

After spending some time on the Time Hierarchy in 11.1.1.5 I found it useful to mention that it now is possible to use the AGO function in the BMM layer whilst the underlying logical facttable has multiple sources on different grains.
What I mean is the following:

Let's say in your logical fact table there is a logical measure column called AMOUNT_SOLD. This column maps to two different columns in the physical layer: 1 comes from a table with a day-grain, the other comes from a table with a month-grain. (In cases where you only have a limited set of day-based data and an infinite set of month-based data, this will occur...)

In your BMM layer, the logical fact table has 2 sources: the DAY-based table and the MONTH-based table. When you specify the content correctly for both LTS's, (map them to the corresponding levels in your logical Calendar hierarchy), drilling down on a dashboard from year via quarter to month level causes OBI to query the physical MONTH-based table. Drilling down to the day-detail level, OBI queries the physical DAY-based table. That is, if the logical table for your calendar has a seperate logical source for the DAY level, next to a logical source for the month level and up.

So far, so good.
But, in order to use time-series calculations you would want to use the AGO function in the BMM layer. Something like AGO("Measure","Calendar Level",1).
This does not work in 11.1.1.3, since the logical table for the Calendar has multiple logical sources. (because of the double-source nature of the AMOUNT_SOLD measure)...
Creating a single logical source for the Calendar table does not solve the issue, because now, you're limited to the lowest level of the calendar. Meaning that you wil always query the DAY-based physical table...

In 11.1.1.5 you can combine both.
You CAN use a single logical table source for your calendar and still have 2 physical sources for your AMOUNT_SOLD measure. Specify the correct Calendar level for both individual logical table sources and it works !


Kind regards,
René