Understanding Oracle BI Application(OBIApps) methodology of deriving AGO Measures

Very often I have members from the IT team at Customer site asking me the question on how to customize OBIApps in order to derive Ago(Year,Quarter,Month,Day) Measures, which are not delivered Out of the Box(OOTB) .Of course there is a Time series function(AGO) in OBIEE which can be leveraged to fulfill these requirements , but in this article I would like to explain the OOTB approach that is taken to derive AGO measures. Getting familiar with OOTB approach and the underlying Data Model that is there to support similar requirements, can help us follow similar methodology for our customization.

In this article I have takenOracle BI Application 7.9.6>Human Resources>Workforce Profile Subject area as an example for understanding the Concepts.

1.Below is the Screnshot of the OOTB Human Resources>Workforce Profile Subject Area .

2. Building a quick report with the Metrics above displays the results as below .

Note that Prior Period Headcount for 2006 is equivalent to Headcount value of 2005.

3. Let us now look into the design . Open the OOTB RPD and expand the Human Resource - Workforce Profile Presentation Catalog. Scroll Down and further expand the Presentation Table Workforce Profile Facts you should notice the two Presentation Columns Headcount and Prior Period Headcount .

4. These two Presentation columns are mapped to different Logical Table Sources as listed below
Fact_W_WRKFC_EVT_MONTH_F_Snapshot_Year
Fact_W_WRKFC_EVT_MONTH_F_Snapshot_Year_Ago
Fact_W_WRKFC_EVT_MONTH_F_Snapshot_Quarter
Fact_W_WRKFC_EVT_MONTH_F_Snapshot_Quarter_Ago
Fact_W_WRKFC_EVT_MONTH_F_Snapshot_Month
Fact_W_WRKFC_EVT_MONTH_F_Snapshot_Month_Ago
Fact_W_WRKFC_EVT_MONTH_F_Snapshot_Day
Fact_W_WRKFC_EVT_MONTH_F_Snapshot_Day_Ago

5.Further drilling down into the Physical Layer reveals that the above logial table sources are derived from the two Physical Table Aliases of the same table W_WRKFC_EVT_MONTH_F.

6.Below is the Physical Diagragm of the alias Fact_W_WRKFC_EVT_MONTH_F_Snapshot joinining with the Time Dimensions.

7.Below is the expression definition used in the join criteria . Note that ROW_WID of Year,Quarter,Month and Day Dimensions are used to join with the Fact table alias.

8.Below is the Physical diagram of the alias Fact_W_WRKFC_EVT_MONTH_F_Snapshot_Period_Ago joining with the Time Dimensions.

9.Below is the expression definition used in the join criteria . Note that YEAR_AGO_WID,QUARTER_AGO_WID,MONTH_AGO_WID and DAY_AGO_WID are used to join with the Time Dimensions.

10.Let us now analyze the Data of the Time Dimension. As you can notice below the Data Model is in place which can be used to join and derive AGO measures.

11.Similar approach can be followed during Oracle BI Application(OBIApps) Customization for deriving ago measures which are not present Out of the Box(OOTB).