Home » SQL & PL/SQL » SQL & PL/SQL » ways to load time dimension
ways to load time dimension [message #229049] Thu, 05 April 2007 03:19 Go to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
i have a friend doing his internship project in datawareshousing, where we have some 6 tables in OLTP...
now he is in the process of designing the star schema.

can anyone please tell me how to load a time dimension ?
i have been thinking to use extract function in plsql, maybe
in the open source tool he is using, to populate time dimension,
but i am not sure...

anyone with experience in datawarehousing field, please let me
know in his scenario, how to load the time dimension table.

he is using an open source tool for ETL process..the idea is to
load the time dimension as one time only process..maybe load
4 years data in advance

any idea is greatly appreciated

Re: ways to load time dimension [message #229173 is a reply to message #229049] Thu, 05 April 2007 11:48 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Usually, you would generate the data for the time dimension table, not load it from anywhere (unless you have to work with some special calendar, ie a fiscal calendar in which case the definition of it will be stored in the OLTP system).

To generate data and load it into the time table, you do:
create table <tablename> as <select statement>


You usually use the dates a the base (so let's say all dates from 1 Jan 2006 till 31 Dec 2007), this can be done is various ways, one simple one being:

[CODE]SELECT to_date('01012006'
,'ddmmyyyy') - 1 + rownum dd
FROM all_objects
WHERE rownum <
(to_date('31122007'
,'ddmmyyyy') - to_date('01012006'
,'ddmmyyyy') + 2)CODE]

Note the -1 in the calculation for date and the +2 for the rownum < part. These are there because the date format used is truncated, so the 1st of January for example falls out of the range. There are loads of fancy way to solve this, but this is a simple one that works...

Now, you have to fill the rest of your columns, for example weekid, week desc, monthnum, month desc, year:

SELECT sub.dd
      ,to_char(dd
              ,'yyyyww') week_id
      ,'week ' || to_char(dd
                         ,'ww') week_desc
<etc...>                         
FROM   (SELECT to_date('01012006'
                      ,'ddmmyyyy') - 1 + rownum dd
        FROM   all_objects
        WHERE  rownum < (to_date('31122007'
                                ,'ddmmyyyy') -
               to_date('01012006'
                                ,'ddmmyyyy') + 2)) sub
Re: ways to load time dimension [message #229485 is a reply to message #229049] Sun, 08 April 2007 23:13 Go to previous message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
thanks, let me try this one
Previous Topic: Error...........
Next Topic: open source tool for debugging
Goto Forum:
  


Current Time: Sun Dec 04 06:55:49 CST 2016

Total time taken to generate the page: 0.10000 seconds