time dimension

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

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.

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?

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.

>>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?

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