time dimension

View previous topic View next topic Go down

time dimension

Post  dwhuser11 on Tue Apr 27, 2010 7:34 am

Hi

I have to create a time dimension to cater to the reporting needs. With the current database structure which is not a dimensional model we need to create reports that are period based.
How do we create a time dimension table (we need year, quarter, month, week) and join this with the existing table?
The existing tables has a varchar field which holds the timestamp. Thanks for your help

dwhuser11

Posts : 3
Join date : 2010-04-27

View user profile

Back to top Go down

Re: time dimension

Post  ngalemmo on Tue Apr 27, 2010 11:49 am

By 'time' dimension, I assume you mean date... that is, you are not interested in the time of day. As for your varchar timestamp columns, do they include time of day?

The easiest thing to do is create a date table, with appropriate attributes, that has a primary key in a suitable format that makes it easier to join with your existing timestamp columns. If those timestamp columns include time of day, you will need to develop a function to transform that value to one you can use to reference your date table. While not ideal, it would be the simplest way to incorporate a date table into your existing schema. Because you would need to use a function on the base table attribute, you may not be able to index for date on those tables (although some databases allow functions in index definitions) so you may experience performance issues.

The correct way would be to add new column(s) to the existing tables to store a proper foreign key to the date table (which could then use a surrogate primary key). But such a change could be a significant amount of work, depending on the size of your schema, and would probably be a hard sell to whoever has to pay for it.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Thanks ngalemmo

Post  dwhuser11 on Wed Apr 28, 2010 10:45 am

Thanks ngalemmo
Yes, the varchar field does have time in it.
Is it possible to preserve the timestamp in the main table and then join it to the date dimension that we create?

dwhuser11

Posts : 3
Join date : 2010-04-27

View user profile

Back to top Go down

Re: time dimension

Post  ngalemmo on Wed Apr 28, 2010 11:48 am

You could have a compound key for the date dimension containing a begin and end timestamp for the day and join using a BETWEEN condition, but that can be messy. If you do that, define an index that has the end timestamp first so the row can be located faster. I prefer to just have the date (no time) as the key and use a function to convert the transaction data timestamp.

It would have been a lot easier if they simply stored the timestamp in a timestamp data type rather than a VARCHAR.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: time dimension

Post  VHF on Wed Apr 28, 2010 12:06 pm

>>Is it possible to preserve the timestamp in the main table and then join it to the date dimension that we create?

Yes, you should be able to strip out the time component in the join with the user-defined function that ngalemmo mentioned.

What format is the existing VARCHAR timestamp field? And what database are you using?

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

timestamp details

Post  dwhuser11 on Thu Apr 29, 2010 4:18 pm

Thank you guys..
the timestamp format is ddmmyyyyhhmmss and the database is oracle 9i

dwhuser11

Posts : 3
Join date : 2010-04-27

View user profile

Back to top Go down

Re: time dimension

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum