Calculating time series functions in Oracle Business Intelligence (OBI) requires development of a time hierarchy. This time hierarchy is necessary because while OBI can calculate the previous period it does not know the context of the period.
The time hierarchy should be built using unique or specific keys at each level of the hierarchy so that OBI can determine the correct period for the time series function and a path to follow.
The OBI calendar shown below can be used as a basis to understand the problem and the resolution.
In the calendar, 2008 has quarter 1 and 2009 has quarter 1 which are not year-specific. Thus, if the current period is Q2 2009, OBI does not know whether the previous period is CAL_QTR 1 of 2008 or CAL_QTR 1 of 2009. A year-specific calendar key is required for OBI to navigate to the correct path. Accordingly, PER_QTR_NAME should be used instead of CAL_QTR which would specifically indicate 2009 – Q1.
The time hierarchy should be built with the Time Dimension enabled for the hierarchy and the detail level having the chronological key enabled. The diagrams below show example hierarchies that can be employed.
As shown, each time category (i.e., Year, Half, Quarter, Month and Week) includes period name (PER_NAME_XXX) that specifies the period parent. For example, in the case of a Quarter, the period name might be 2008-Q1. With this approach OBI can use the PER_NAME to calculate the previous period.
Blog Author: Somya Kancheti
Somya is a contributing blog author on the BI team at BizTech
