What is the process of loading the time dimension?

Showing Answers 1 - 3 of 3 Answers

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;

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions