Scenario 19: How to populate date dimension through PL/SQL code by using stored procedure transformation.

Scenario 19: How to populate date dimension through PL/SQL code by using stored procedure transformation.

Let's Chat: Populating date dimension table can be done with multiple approaches according to requirements. Here I have taken a Basic one. In which I am taking a date and populating date dimension for complete year. Suppose date is coming '02-Jun-81' so, date dimension would be populated for complete year 1981. Starting from 01-Jan-81 to 31-Dec-81. If any date belongs to 1981 is coming once again in year then discard that date means populate date dimension table only for new calendar year.

Theory: It's too easy with help of procedure and same is used mostly in real time as well.

Date_Dimension Table Structure:
DATEKEY                                 NUMBER 
FULLDATE                                DATE 
DAYOFWEEK                           NUMBER
DAYNAMEOFWEEK                VARCHAR2(15)
DAYOFYEAR                             NUMBER
WEEKDAYWEEKEND            VARCHAR2(15)
WEEKOFYEAR                           NUMBER
MONTHNAME                            VARCHAR2(15)
MONTHOFYEAR                        NUMBER
QTR                                              NUMBER 
CAL_YEAR                              NUMBER

A Procedure Code which will populate Date Dimension.

create or replace PROCEDURE DATE_DIM
(in_date in date)
as
date_count number;
start_date date;
end_date date;
max_date_key number;
new_date_key number;
curr_date date;
Begin
--Check Whether Date Dimension table has been already populated for coming date?
-- If count is 0 then there is no data for coming date.
select count(*) into date_count
from date_dimension
where fulldate=in_date;

--Set Start_Date to first date of year. Ex: (01/01/2013)
select trunc(in_date, 'YYYY')into start_date from dual;

--Set end_date to Last date of year. Ex: (31/12/2013)
select add_months(trunc(in_date,'YYYY'), 12)into end_date from dual;

--Find the last generated DATE_KEY value
select max(datekey)into max_date_key from date_dimension;

--If last generated key value is null then assign 1 else assign last generated key value + 1 to new_date_key
select nvl2(max_date_key,max_date_key+1,1)into new_date_key from dual;

--For Loop set current date as Start Date
curr_date := start_date;

--If date count=0 means we don't have any data for particular year
if date_count = 0
then
--If condition is TURE then Start the Loop and insert record starting from 1st day to last day of year.

While curr_date < end_date loop
insert into date_dimension(datekey,FULLDATE,DATE_DAY,DAYOFWEEK, 
            DAYNAMEOFWEEK,DAYOFYEAR,WEEKDAYWEEKEND,WEEKOFYEAR,
            MONTHNAME,MONTHOFYEAR, qtr, cal_year)
select new_date_key,curr_date, TO_CHAR(curr_date,'DD'),
        TO_CHAR(curr_date,'D'),TO_CHAR(curr_date, 'Day'),
        TO_CHAR(curr_date, 'DDD'), 
        DECODE(TO_CHAR(curr_date, 'D'),  1,'Weekend',
                                      7,'Weekend',
                                      'Weekday'),
        TO_CHAR(curr_date, 'iw') ,TO_CHAR(curr_date, 'Month') ,
        TO_CHAR(curr_date, 'mm'),TO_CHAR(curr_date, 'q') ,
        TO_CHAR(curr_date, 'yyyy') from dual;

 --Generating new id for DATEKEY column       
new_date_key:= new_date_key + 1;

--Increment date by one for next date. 
curr_date := curr_date + 1;
END LOOP; 
end if;
End;

(Above code is tested and executed successfully. Feel free to mail me if you found any error)

Yahoo!!! Everything done by procedure, Now we need to create a simple mapping.

Step 1: We need to create a dummy source. Create a .txt target file with one column with date datatype.

Drag and Drop your source in Mapping Designer.

Step 2:

Stored Procedure Transformation.

Create Stored procedure Transformation.

Set In_Date port as output port as well. 
Connect Hiredate of EMP Table (Source) to In_Date port of Stored Procedure.

Step 3

Connect your Date_In port to Target.

We are done friends, create session and workflow and run it.

BINGO!!!

Feel free to post your doubt in comment section.

Due to limitation, I can't attach exported .xml file which you can Import in your repository and see or analyse. (Feel free to mail me if you need .xml file with Scenario number and Question.)

If you like the approach and solution feel free to share with others.

Thanks.

hpandey.harsh@gmail.com