Date / Time dimension is known to be a special dimension for any Dimensional model as it is a much required dimension every where. No matter you are building a retail model or a Sales model or a banking application, Date dimension is inevitable and virtually every fact table is bound with Date dimension in a Dimensional model. Date dimension is a well known Conformed dimension and role playing in nature.
The grain of the table is generally designed based on the model requirement. It could be a DAY or an HOUR. Date Dimension can easily embrace multiple hierarchies in its data such as year -> Month -> Day or Fiscal Year -> Fiscal Quarter -> Month -> Week -> Day etc. Let us see how to load Date dimension using Informatica Powercenter
A sample Date Dimension table data can be accessed from ETLLabs Facebook account.
How to load date dimension in Informatica?
Method 1# Using Java transformation
1. You can create Informatica mapping with java transformation in it to create Date Dimension data.
2. Java transformation to be configured with the java script to create date dimension values. This will be a fairly a simple Java program that a Java Developer can code. I will not discuss much about java programming here..
3. Pass start date and End date as mapping parameters and pass them to Java transformation
Method 2# Using SQL Override
1. Write a SQL override in SOurce Qualifier transformation to generate a list of dates in Informatica mapping
SELECT TO_DATE(’01-01-2014′, ‘DD-MM-YYYY’)
+ LEVEL – 1 “DATE_FOR_DATE_DIMENSION”
FROM DUAL CONNECT BY LEVEL <= /*Expression in next line can be replaced by just passing 365** No. of days you want to load in your date dimension*/
(TO_DATE(’31-12-2014′, ‘DD-MM-YYYY’) – TO_DATE(’01-01-2014′, ‘DD-MM-YYYY’)+1 );
2. Evaluate the other columns in an Expression transformation for every date.
3. Pass start date and End date as mapping parameters and use them in Source Qualifier transformation
Method 3# Stored Procedure (Pre Session) + Source file
You can create a stored procedure with SQL in above method to create a source file with list of dates and load it into table using Informatica mapping.
Method 4# Using Excel source file
You can create an xls source file for the calender data and load it by creating an Informatica mapping and session.
Point #1 Create Holiday calender
You can create a Date dimension data for many years however you cannot ignore the fact of integrating the business working days to your Date dimension table every year. You can maintain a “Holiday lookup file / relational table to update Is_businessday flag in the date dimension if required
Point #2 Create Attribute tables – Week Name, Month Name
To make your mapping easier (should I say so?) or avoid using multiple functions, you can maintain lookup files for date attributes like week name and month name while loading date dimension.