Populate W_DAY_D correctly

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.


click to enlarge

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.


click to enlarge

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.

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

The intention of this blog is to share findings about Siebel CRM and Oracle Business Intelligence Enterprise Edition (Oracle BI EE formerly known as Siebel Analytics) from technical and other (sometimes unprecedented) perspectives.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today