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
- W_MCAL_DAY_D
- W_MCAL_WEEK_D
- W_MCAL_PERIOD_D
- W_MCAL_QTR_D
- W_MCAL_YEAR_D
- 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.
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
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':- 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: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:
- 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
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>
Thank You very very much
ReplyDeleteHi Artashes,
ReplyDeleteI'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