Populate W_DAY_D correctly
By @lex on Sep 08, 2008
2011 is near...
Oracle BI Applications come with a predefined Informatica Repository and a pre-built DAC (Datawarehouse Administration Console) repository. When loading the BAW (Business Analysis Warehouse, the predefined schema) for the first time, this is called a full load.
One table is of particular interest: w_DAY_D, implementing the time dimension, storing one record for each day between 1st of January 1980 and 31st of December 2010.
The end day of this period is now raising concerns (and eyebrows) among admins and analysts over the globe as they see that date coming soon.
So how is W_DAY_D populated?
Using the DAC Client this question is easily answered. There are four tasks that take care of W_DAY_D.
The main task SIL_DayDimension has two parameters which are passed to the Informatica workflow.
$$START_DATE has a default value of 1980-01-01 and $$END_DATE one of 2010-12-31.
Before you can update these parameters, you have to create a custom container using the functionality in the DAC File menu. Once the container is created, go to the SIL_DayDimension task, click the Parameters tab and update the parameters according to your needs.
When a referenced object in the DAC repository is changed, it will be cloned and you have to commit that in a dialog box.
Now we have to enforce a full load on the W_DAY_D table, which means setting the table's refresh date to NULL. This is standard behavior of the DAC. It will start the task in full mode when the refresh date of the target table(s) are set to NULL.
To do this, navigate to Setup > Physical Data Sources > DataWarehouse and click the Refresh Dates tab. Here you query for the table and set the refresh date to NULL.
When the next execution plan (you will have to create a new one once you have created a custom container) is run, W_DAY_D will be loaded with the new range of dates.
Please keep in mind that you should only extend the date range and never narrow it, so if you have those dates of 1980 already in W_DAY_D, they should stay there for the sake of referential integrity.