The Data Warehousing and Business Intelligence: Extending Oracle BI Applications Calendars

The end of fiscal year is near, and it is a good time to extend Oracle BI Applications (OBIA) calendars.

The system that I'm going to describe in this post is an OBIA 7.9.6.1 which is integrated with Oracle EBS 12.1.1 source system. The current 2013 fiscal year ends on 03/02/2013. The fiscal calendars (both source and generated) should be extended till the end of 2016 fiscal year which will be on 31/01/2016. The Gregorian calendar should be extended till the 31/12/2016 accordingly.

The OBIA supports Enterprise or Global calendar (cross-functional reporting calendar, can be fiscal or Gregorian), Fiscal calendar (can be accounting or financial), Gregorian calendar, 4-4-5 and 13 period calendars. The Time Dimension that represents those calendars uses the following tables from Oracle Business Analytics Warehouse (OBAW):

Gregorian Calendar

W_DAY_D
W_WEEK_D
W_MONTH_D
W_QTR_D
W_YEAR_D

Multiple Fiscal Calendars (both source and generated)

W_MCAL_DAY_D
W_MCAL_WEEK_D
W_MCAL_PERIOD_D
W_MCAL_QTR_D
W_MCAL_YEAR_D

Enterprise Calendar

W_ENT_WEEK_D
W_ENT_PERIOD_D
W_ENT_QTR_D
W_ENT_YEAR_D

To support this exercise I have created a new subject area called Calendars, which is made up of the tables mentioned above.

The 'Multiple Calendar Support' configuration tag is added because multi-calendar tasks are not included in the ETL process by default.

I have also created new execution plan called Calendars and added the Calendars subject area to it.

After generating parameters, assigning parameter values and building, the new execution plan is ready for the first run.

A quick check on W_MCAL_CAL_D table shows that the system has four fiscal calendars configured - JEBE_MONTH_VAT and WMM_GB_ACCOUNTS which are sourced from EBS, and 4-4-5 and 13 which are generated by OBIA itself:

SQL> SELECT ROW_WID, MCAL_CAL_ID, MCAL_CAL_NAME, MCAL_CAL_CLASS
  2  FROM W_MCAL_CAL_D;

   ROW_WID MCAL_CAL_ID                    MCAL_CAL_NAME                  MCAL_CAL_CLASS
---------- ------------------------------ ------------------------------ --------------------
      1000 JEBE_MONTH_VAT~Month           JEBE_MONTH_VAT                 OLTP Sourced
      1001 WMM_GB_ACCOUNTS~21             WMM_GB_ACCOUNTS                OLTP Sourced
      1002 10000                          4-4-5                          Generated
      1003 10001                          13                             Generated

SQL>

The enterprise calendar is set up using the 4-4-5 generated calendar:

Whatever generated calendars we are going to extend, the Gregorian calendar comes first. And the W_DAY_D is the base table for the Time Dimension. The W_DAY_D should be extended prior to extending the Gregorian calendar aggregate tables (W_WEEK_D, W_MONTH_D, W_QTR_D, W_YEAR_D) or any other calendars (Fiscal and Enterprise).
In order to set up the Gregorian calendar date range (according to Oracle® Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users, Version 7.9.6.1) one should:

Launch the DAC client, navigate to Design view and select the appropriate container, e.g. 'Custom Oracle R12.1.1'
Navigate to Tasks tab and query for the SIL_DayDimension task
Navigate to Parameters tab in the lower pane and use $$START_DATE and $$END_DATE parameters to set the date range

In my case the $$END_DATE parameter was set to '31/12/2013 12:53:52'. A quick check on W_DAY_D table showed that the maximum date there is 30/12/2013:

SQL> SELECT TO_CHAR(MIN(DAY_DT), 'DD-MM-YYYY') MIN_DAY_DT,
  2    TO_CHAR(MAX(DAY_DT), 'DD-MM-YYYY') MAX_DAY_DT
  3  FROM W_DAY_D
  4  WHERE ROW_WID <> 0;

MIN_DAY_DT MAX_DAY_DT
---------- ----------
01-01-1980 30-12-2013

SQL> SELECT COUNT(*) FROM W_DAY_D
  2  WHERE ROW_WID <> 0;

  COUNT(*)
----------
     12647

SQL>

This suggests that in order to have the data in W_DAY_D table extended till 31/12/2016 the value of the $$END_DATE parameter should be set to '01/01/2017 00:00:00':

The data in Time Dimension is loaded once during the initial full load. In order to reload the Time Dimension tables (according to Oracle® Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users, Version 7.9.6.1) one would also need to:

Navigate to Setup view, then Physical Data Sources tab
Select the DataWarehouse connection and navigate to Refresh Dates tab in the lower pane
Set refresh date for W_DAY_D, W_WEEK_D, W_MONTH_D, W_QTR_D and W_YEAR_D tables to NULL

Now, since we (hopefully) have all the settings for the Gregorian calendar in order, it is the time to configure the fiscal calendars. The W_MCAL_CONFIG_G table controls how fiscal calendars are generated by OBIA. In turn, this table is loaded from file_mcal_config_g.csv file which is located in INFORMATICA_HOME/server/infa_shared/SrcFiles directory. In order to extend the fiscal calendar date range (either 4-4-5 or 13 period) one should modify the file_mcal_config_g.csv file and set the value of CAL_ST_DT and CAL_END_DT columns for the desired calendars. In my case I have set the value of CAL_END_DT column to '20160131000000':

Now it's time to run the Calendars execution plan and see what happens...
The Calendars execution plan has been successfully executed and now it's time to check the results:

SQL> SELECT TO_CHAR(MIN(DAY_DT), 'DD-MM-YYYY') MIN_DAY_DT,
  2    TO_CHAR(MAX(DAY_DT), 'DD-MM-YYYY') MAX_DAY_DT
  3  FROM W_DAY_D
  4  WHERE ROW_WID <> 0;

MIN_DAY_DT MAX_DAY_DT
---------- ----------
01-01-1980 01-01-1980

SQL> SELECT COUNT(*) FROM W_DAY_D
  2  WHERE ROW_WID <> 0;

  COUNT(*)
----------
         1

SQL>

Not exactly what was expected. A quick check in DAC showed that the W_DAY_D table is being populated by SIL_DayDimension task (other tasks that have W_DAY_D table as a target just update flags and attributes). The SIL_DayDimension task, in turn, has W_DUAL_G table as the primary source. The W_DUAL_G table, as we can see below, contains only one row:

SQL> SELECT COUNT(*) FROM W_DUAL_G;

  COUNT(*)
----------
         1

SQL>

Looking at the SIL_DayDimension mapping in the Informatica PowerCenter Designer, we can see that the mapping uses the W_DUAL_G table as a seed source:

It seems that we need to seed this table. Another check in DAC showed that the W_DUAL_G table is a target for the SIL_DayDimension_GenerateSeed, SIL_DayDimension_GenerateRows[1-7] and SIL_DayDimension_CleanSeed tasks. The task names hint we're on the right path - the W_DUAL_G table must be seeded prior to loading the calendar tables.

This time, in order to include the seeding of the W_DUAL_G table, before triggering the Calendars execution plan, we'll need to:

Navigate to Setup view, then Physical Data Sources tab
Select the DataWarehouse connection and navigate to Refresh Dates tab in the lower pane
Set refresh date for W_DUAL_G, W_DAY_D, W_WEEK_D, W_MONTH_D, W_QTR_D and W_YEAR_D tables to NULL

Now it's time for another run of the Calendars execution plan...
The Calendars execution plan has again been successfully executed and the results are:

SQL> SELECT TO_CHAR(MIN(DAY_DT), 'DD-MM-YYYY') MIN_DAY_DT,
  2    TO_CHAR(MAX(DAY_DT), 'DD-MM-YYYY') MAX_DAY_DT
  3  FROM W_DAY_D
  4  WHERE ROW_WID <> 0;

MIN_DAY_DT MAX_DAY_DT
---------- ----------
01-01-1980 31-12-2016

SQL> SELECT COUNT(*) FROM W_DAY_D
  2  WHERE ROW_WID <> 0;

  COUNT(*)
----------
     13764

SQL>

Looks much better now! The following check suggests that all the tables of Gregorian calendar have been populated correctly:

SQL> SELECT 'W_DAY_D' TABLE_NAME,
  2    TO_CHAR(MIN(DAY_DT), 'YYYY-MM-DD') MIN_DT,
  3    TO_CHAR(MAX(DAY_DT), 'YYYY-MM-DD') MAX_DT
  4  FROM W_DAY_D
  5  WHERE ROW_WID <> 0
  6  UNION
  7  SELECT 'W_WEEK_D' TABLE_NAME,
  8    TO_CHAR(MIN(CAL_WEEK_START_DT), 'YYYY-MM-DD') MIN_DT,
  9    TO_CHAR(MAX(CAL_WEEK_END_DT), 'YYYY-MM-DD') MAX_DT
 10  FROM W_WEEK_D
 11  WHERE ROW_WID <> 0
 12  UNION
 13  SELECT 'W_MONTH_D' TABLE_NAME,
 14    TO_CHAR(MIN(CAL_MONTH_START_DT), 'YYYY-MM-DD') MIN_DT,
 15    TO_CHAR(MAX(CAL_MONTH_END_DT), 'YYYY-MM-DD') MAX_DT
 16  FROM W_MONTH_D
 17  WHERE ROW_WID <> 0
 18  UNION
 19  SELECT 'W_QTR_D' TABLE_NAME,
 20    TO_CHAR(MIN(CAL_QTR_START_DT), 'YYYY-MM-DD') MIN_DT,
 21    TO_CHAR(MAX(CAL_QTR_END_DT), 'YYYY-MM-DD') MAX_DT
 22  FROM W_QTR_D
 23  WHERE ROW_WID <> 0
 24  UNION
 25  SELECT 'W_YEAR_D' TABLE_NAME,
 26    TO_CHAR(MIN(CAL_YEAR_START_DT), 'YYYY-MM-DD') MIN_DT,
 27    TO_CHAR(MAX(CAL_YEAR_END_DT), 'YYYY-MM-DD') MAX_DT
 28  FROM W_YEAR_D
 29  WHERE ROW_WID <> 0;

TABLE_NAME MIN_DT     MAX_DT
---------- ---------- ----------
W_DAY_D    1980-01-01 2016-12-31
W_MONTH_D  1980-01-01 2016-12-31
W_QTR_D    1980-01-01 2016-12-31
W_WEEK_D   1980-01-01 2016-12-31
W_YEAR_D   1980-01-01 2016-12-31

SQL>

Now, since we have the Gregorian calendar in order, it's time to check the other calendars. A quick check on the Fiscal calendars suggests that 4-4-5 calendar has been populated correctly but we have some problems with the calendars sourced from EBS:

SQL> SELECT MCAL_CAL_WID, MCAL_CAL_NAME,
  2    TO_CHAR(MIN(MCAL_DAY_DT), 'YYYY-MM-DD') MIN_DT,
  3    TO_CHAR(MAX(MCAL_DAY_DT), 'YYYY-MM-DD') MAX_DT
  4  FROM W_MCAL_DAY_D
  5  WHERE ROW_WID <> 0
  6  GROUP BY MCAL_CAL_WID, MCAL_CAL_NAME
  7  ORDER BY MCAL_CAL_WID;

MCAL_CAL_WID MCAL_CAL_NAME                  MIN_DT     MAX_DT
------------ ------------------------------ ---------- ----------
        1000 JEBE_MONTH_VAT                 2001-01-01 2010-12-31
        1001 WMM_GB_ACCOUNTS                2008-02-04 2013-12-29
        1002 4-4-5                          2004-02-02 2016-01-31

SQL>

A quick check on EBS shows that the WMM_GB_ACCOUNTS calendar (or period set, in EBS terms) been propagated till 31/01/2016:

SQL> SELECT PERIOD_SET_NAME,
  2    TO_CHAR(MIN(START_DATE), 'DD-MM-YYYY') MIN_START_DATE,
  3    TO_CHAR(MAX(END_DATE), 'DD-MM-YYYY') MAX_END_DATE
  4  FROM GL_PERIODS
  5  GROUP BY PERIOD_SET_NAME;

PERIOD_SET_NAME MIN_START_DATE MAX_END_DATE
--------------- -------------- ------------
JEBE_MONTH_VAT  01-01-2001     31-12-2010
WMM_GB_ACCOUNTS 04-02-2008     31-01-2016

SQL>

The JEBE_MONTH_VAT calendar does not seem to be in use anymore, so we'll concentrate only on WMM_GB_ACCOUNTS calendar.

The ETL process flow diagram for time dimension (from Oracle® Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users, Version 7.9.6.1) indicated that in order to reload the calendars sourced from EBS we'll need understand how the W_MCAL_PERIOD_DS table is being populated. A quick check in DAC showed that the W_MCAL_PERIOD_DS table is a source for SIL_TimeDimension_MCalPeriod task:

The SIL_TimeDimension_MCalPeriod task(similarly to SIL_DayDimension task) has two parameters - $$START_DATE and $$END_DATE, and in my case $$END_DATE was set to '31/12/2013 12:53:03':

This suggests that in order to extend the calendars sourced from EBS, in addition to the steps described above, we'll also need to:

Set the $$START_DATE and $$END_DATE parameters for the SIL_TimeDimension_MCalPeriod task to set the date range
Set refresh date for W_MCAL_DAY_D, W_MCAL_WEEK_D, W_MCAL_PERIOD_D, W_MCAL_PERIOD_DS, W_MCAL_QTR_D and W_MCAL_YEAR_D tables to NULL

In my case the $$END_DATE parameter should be set to '31/12/2016 00:00:00':

After another successful execution of the Calendar execution plan the results look much better:

SQL> SELECT MCAL_CAL_WID, MCAL_CAL_NAME,
  2    TO_CHAR(MIN(MCAL_DAY_DT), 'YYYY-MM-DD') MIN_DT,
  3    TO_CHAR(MAX(MCAL_DAY_DT), 'YYYY-MM-DD') MAX_DT
  4  FROM W_MCAL_DAY_D
  5  WHERE ROW_WID <> 0
  6  GROUP BY MCAL_CAL_WID, MCAL_CAL_NAME
  7  ORDER BY MCAL_CAL_WID;

MCAL_CAL_WID MCAL_CAL_NAME                  MIN_DT     MAX_DT
------------ ------------------------------ ---------- ----------
        1000 JEBE_MONTH_VAT                 2001-01-01 2010-12-31
        1001 WMM_GB_ACCOUNTS                2008-02-04 2016-01-31
        1002 4-4-5                          2004-02-02 2016-01-31

SQL>

The following check suggests that all the tables of the Fiscal calendars have now been populated correctly:

SQL> WITH CAL AS (
  2    SELECT 'W_MCAL_DAY_D' TABLE_NAME, MCAL_CAL_WID CAL_WID, MCAL_CAL_NAME CAL_NAME,
  3      TO_CHAR(MIN(MCAL_DAY_DT), 'YYYY-MM-DD') MIN_DT,
  4      TO_CHAR(MAX(MCAL_DAY_DT), 'YYYY-MM-DD') MAX_DT
  5    FROM W_MCAL_DAY_D
  6    WHERE ROW_WID <> 0
  7    GROUP BY MCAL_CAL_WID, MCAL_CAL_NAME
  8    UNION
  9    SELECT 'W_MCAL_WEEK_D' TABLE_NAME, MCAL_CAL_WID CAL_WID, MCAL_CAL_NAME CAL_NAME,
 10      TO_CHAR(MIN(MCAL_WEEK_START_DT), 'YYYY-MM-DD') MIN_DT,
 11      TO_CHAR(MAX(MCAL_WEEK_END_DT), 'YYYY-MM-DD') MAX_DT
 12    FROM W_MCAL_WEEK_D
 13    WHERE ROW_WID <> 0
 14    GROUP BY MCAL_CAL_WID, MCAL_CAL_NAME
 15    UNION
 16    SELECT 'W_MCAL_PERIOD_D' TABLE_NAME, MCAL_CAL_WID CAL_WID, MCAL_CAL_NAME CAL_NAME,
 17      TO_CHAR(MIN(MCAL_PERIOD_START_DT), 'YYYY-MM-DD') MIN_DT,
 18      TO_CHAR(MAX(MCAL_PERIOD_END_DT), 'YYYY-MM-DD') MAX_DT
 19    FROM W_MCAL_PERIOD_D
 20    WHERE ROW_WID <> 0
 21    GROUP BY MCAL_CAL_WID, MCAL_CAL_NAME
 22    UNION
 23    SELECT 'W_MCAL_QTR_D' TABLE_NAME, MCAL_CAL_WID CAL_WID, MCAL_CAL_NAME CAL_NAME,
 24      TO_CHAR(MIN(MCAL_QTR_START_DT), 'YYYY-MM-DD') MIN_DT,
 25      TO_CHAR(MAX(MCAL_QTR_END_DT), 'YYYY-MM-DD') MAX_DT
 26    FROM W_MCAL_QTR_D
 27    WHERE ROW_WID <> 0
 28    GROUP BY MCAL_CAL_WID, MCAL_CAL_NAME
 29    UNION
 30    SELECT 'W_MCAL_YEAR_D' TABLE_NAME, MCAL_CAL_WID CAL_WID, MCAL_CAL_NAME CAL_NAME,
 31      TO_CHAR(MIN(MCAL_YEAR_START_DT), 'YYYY-MM-DD') MIN_DT,
 32      TO_CHAR(MAX(MCAL_YEAR_END_DT), 'YYYY-MM-DD') MAX_DT
 33    FROM W_MCAL_YEAR_D
 34    WHERE ROW_WID <> 0
 35    GROUP BY MCAL_CAL_WID, MCAL_CAL_NAME
 36  )
 37  SELECT *
 38  FROM CAL
 39  ORDER BY CAL_WID, TABLE_NAME;

TABLE_NAME         CAL_WID CAL_NAME                       MIN_DT     MAX_DT
--------------- ---------- ------------------------------ ---------- ----------
W_MCAL_DAY_D          1000 JEBE_MONTH_VAT                 2001-01-01 2010-12-31
W_MCAL_PERIOD_D       1000 JEBE_MONTH_VAT                 2001-01-01 2010-12-31
W_MCAL_QTR_D          1000 JEBE_MONTH_VAT                            2010-12-31
W_MCAL_YEAR_D         1000 JEBE_MONTH_VAT                            2010-12-31
W_MCAL_DAY_D          1001 WMM_GB_ACCOUNTS                2008-02-04 2016-01-31
W_MCAL_PERIOD_D       1001 WMM_GB_ACCOUNTS                2008-02-04 2016-01-31
W_MCAL_QTR_D          1001 WMM_GB_ACCOUNTS                2008-02-04 2016-01-31
W_MCAL_YEAR_D         1001 WMM_GB_ACCOUNTS                2008-02-04 2016-01-31
W_MCAL_DAY_D          1002 4-4-5                          2004-02-02 2016-01-31
W_MCAL_PERIOD_D       1002 4-4-5                          2004-02-02 2016-01-31
W_MCAL_QTR_D          1002 4-4-5                          2004-02-02 2016-01-31
W_MCAL_WEEK_D         1002 4-4-5                          2004-02-02 2016-01-31
W_MCAL_YEAR_D         1002 4-4-5                          2004-02-02 2016-01-31

13 rows selected.

SQL>

The following check suggests that all the tables of the Enterprise calendar have also been populated correctly:

SQL> WITH CAL AS (
  2    SELECT 'W_ENT_WEEK_D' TABLE_NAME, ENT_CAL_WID CAL_WID, ENT_CAL_NAME CAL_NAME,
  3      TO_CHAR(MIN(ENT_WEEK_START_DT), 'YYYY-MM-DD') MIN_DT,
  4      TO_CHAR(MAX(ENT_WEEK_END_DT), 'YYYY-MM-DD') MAX_DT
  5    FROM W_ENT_WEEK_D
  6    WHERE ROW_WID <> 0
  7    GROUP BY ENT_CAL_WID, ENT_CAL_NAME
  8    UNION
  9    SELECT 'W_ENT_PERIOD_D' TABLE_NAME, ENT_CAL_WID CAL_WID, ENT_CAL_NAME CAL_NAME,
 10      TO_CHAR(MIN(ENT_PERIOD_START_DT), 'YYYY-MM-DD') MIN_DT,
 11      TO_CHAR(MAX(ENT_PERIOD_END_DT), 'YYYY-MM-DD') MAX_DT
 12    FROM W_ENT_PERIOD_D
 13    WHERE ROW_WID <> 0
 14    GROUP BY ENT_CAL_WID, ENT_CAL_NAME
 15    UNION
 16    SELECT 'W_ENT_QTR_D' TABLE_NAME, ENT_CAL_WID CAL_WID, ENT_CAL_NAME CAL_NAME,
 17      TO_CHAR(MIN(ENT_QTR_START_DT), 'YYYY-MM-DD') MIN_DT,
 18      TO_CHAR(MAX(ENT_QTR_END_DT), 'YYYY-MM-DD') MAX_DT
 19    FROM W_ENT_QTR_D
 20    WHERE ROW_WID <> 0
 21    GROUP BY ENT_CAL_WID, ENT_CAL_NAME
 22    UNION
 23    SELECT 'W_ENT_YEAR_D' TABLE_NAME, ENT_CAL_WID CAL_WID, ENT_CAL_NAME CAL_NAME,
 24      TO_CHAR(MIN(ENT_YEAR_START_DT), 'YYYY-MM-DD') MIN_DT,
 25      TO_CHAR(MAX(ENT_YEAR_END_DT), 'YYYY-MM-DD') MAX_DT
 26    FROM W_ENT_YEAR_D
 27    WHERE ROW_WID <> 0
 28    GROUP BY ENT_CAL_WID, ENT_CAL_NAME
 29  )
 30  SELECT *
 31  FROM CAL
 32  ORDER BY CAL_WID, TABLE_NAME;

TABLE_NAME        CAL_WID CAL_NAME                       MIN_DT     MAX_DT
-------------- ---------- ------------------------------ ---------- ----------
W_ENT_PERIOD_D       1002 4-4-5                          2004-02-02 2016-01-31
W_ENT_QTR_D          1002 4-4-5                          2004-02-02 2016-01-31
W_ENT_WEEK_D         1002 4-4-5                          2004-02-02 2016-01-31
W_ENT_YEAR_D         1002 4-4-5                          2004-02-02 2016-01-31

SQL>

2 comments:

Unknown27 December 2013 at 14:52
Thank You very very much
Unified Blogger15 March 2017 at 12:59
Hi Artashes,

I'm facing an issue while the load plan is running.

Whenever a load plan runs the table "W_MCAL_YEAR_D" gets refreshed, I can see duplicate rows created in it. This is causing failure of the load plan as there is an dependent step to create Index which is failing with the following error.

ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

The work around I tried is the following.

=====

SQL> select ROW_WID from W_MCAL_YEAR_D where rowid not in (select min(rowid) from W_MCAL_YEAR_D group by ROW_WID);

212016

SQL> delete from W_MCAL_YEAR_D where rowid not in (select min(rowid) from W_MCAL_YEAR_D group by ROW_WID);

1 row deleted.

SQL> commit;

Commit complete.

=====

Is there a way I can avoid the duplicate rows..?

Thank you,
Prasad

New comments are not allowed.

The Data Warehousing and Business Intelligence

Pages

Thursday 15 November 2012

Extending Oracle BI Applications Calendars

2 comments: