Mahesh

• Aug 15th, 2006

 

We can use SCD Type 1/2/3 to load any Dimensions based on the requirement.

mahesh17375

 Profile 

• Aug 15th, 2006

 

We can use SCD  Type 1/2/3 to load data into any dimension tables as per the requirement.

Bhargava Kudligi

• Sep 4th, 2006

 

U can load time dimension manually by writing scripts in PL/SQL to load the time dimension table with values for a period.

Ex:- M having my business data for 5 years from 2000 to 2004, then load all the date starting from 1-1-2000 to 31-12-2004 its around 1825 records. Which u can do it fast writing scripts.

Bhargav

calltomadhu

 Profile Answers by calltomadhu 

• Sep 23rd, 2006

 

hi

thourgh type1, type2, type3 depending upon the codition

thanks

madhu

kumar

• Sep 28th, 2006

 

For loading data in to other dimensions we have respective tables in the oltp systems..

But for time dimension we have only one base in the OLTP database. Based on that we have to load time dimension. We can loan the time dimension using ETL procedures which calls the procedure or function created in the database. If the columns are more in the time dimension we have to creat it manually by using Excel sheet.

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;