ashish arora

• May 18th, 2005

 

In Dataware house we manually load the time dimension

sudhir

• Aug 3rd, 2005

 

Every Datawarehouse maintains a time dimension. It would be at the most granular level at which the business runs at (ex: week day, day of the month and so on). Depending on the data loads, these time dimensions are updated. Weekly process gets updated every week and monthly process, every month.

Sivarao

• Aug 16th, 2005

 

Time dimension in DWH must be load Manually. we load data into Time dimension using pl/sql scripts.

Raghunath Bhairavajosula

• May 12th, 2006

 

Generally we load the Time dimension by using SourceStage as a Seq File and we use one passive stage in that transformer stage we will manually write functions as Month and Year Functions to load the time dimensions but for the lower level i.e., Day also we have one function to implement loading of Time Dimension.

manisha.sinha

 Profile Answers by manisha.sinha 

• Dec 29th, 2006

 

create a procedure to load data into Time Dimension. The procedure needs to run only once to popullate all the data. For eg, the code below fills up till 2015. You can modify the code to suit the feilds in ur table.

create or replace procedure     QISODS.Insert_W_DAY_D_PR as
LastSeqID number default 0;
loaddate  Date default to_date('12/31/1979','mm/dd/yyyy');
begin
Loop
LastSeqID  := LastSeqID + 1;
loaddate :=  loaddate + 1;
INSERT into QISODS.W_DAY_D values(
LastSeqID,
Trunc(loaddate),
Decode(TO_CHAR(loaddate,'Q'),'1',1,decode(to_char(loaddate,'Q'),'2',1,2)
),
TO_FLOAT(TO_CHAR(loaddate, 'MM')),
TO_FLOAT(TO_CHAR(loaddate, 'Q')),
trunc((ROUND(TO_DECIMAL(to_char(loaddate,'DDD'))) +
ROUND(TO_DECIMAL(to_char(trunc(loaddate, 'YYYY'), 'D')))+ 5) / 7),
TO_FLOAT(TO_CHAR(loaddate, 'YYYY')),
TO_FLOAT(TO_CHAR(loaddate, 'DD')),
TO_FLOAT(TO_CHAR(loaddate, 'D')),
TO_FLOAT(TO_CHAR(loaddate, 'DDD')),
1,
1,
1,
1,
1,
TO_FLOAT(TO_CHAR(loaddate, 'J')),
((TO_FLOAT(TO_CHAR(loaddate, 'YYYY')) + 4713) * 12)  +
TO_number(TO_CHAR(loaddate, 'MM')),
((TO_FLOAT(TO_CHAR(loaddate, 'YYYY')) + 4713) * 4)  +
TO_number(TO_CHAR(loaddate, 'Q')),
TO_FLOAT(TO_CHAR(loaddate, 'J'))/7,
TO_FLOAT (TO_CHAR (loaddate,'YYYY'))  + 4713,
TO_CHAR(load_date, 'Day'),
TO_CHAR(loaddate, 'Month'),
Decode(To_Char(loaddate,'D'),'7','weekend','6','weekend','weekday'),
Trunc(loaddate,'DAY') + 1,
Decode(Last_Day(loaddate),loaddate,'y','n'),
to_char(loaddate,'YYYYMM'),
to_char(loaddate,'YYYY') || ' Half' ||
Decode(TO_CHAR(loaddate,'Q'),'1',1,decode(to_char(loaddate,'Q'),'2',1,2)
),
TO_CHAR(loaddate, 'YYYY / MM'),
TO_CHAR(loaddate, 'YYYY') ||' Q ' ||TRUNC(TO_number( TO_CHAR(loaddate,
'Q')) ) ,
TO_CHAR(loaddate, 'YYYY') ||' Week'||TRUNC(TO_number( TO_CHAR(loaddate,
'WW'))),
TO_CHAR(loaddate,'YYYY'));
If loaddate=to_Date('12/31/2015','mm/dd/yyyy') Then
Exit;
End If;
End Loop;
commit;
end Insert_W_DAY_D_PR;