Mar 5, 2010

Time Dimension in Data Warehouse


In any data warehouse date and time plays a vital role in keeping track of when a particular event has taken place. However, we do not store the time information in the same way as we do in the OLTP database. In a dimensional model a separate time dimension is normally used. This time dimension contains separate attributes for year, month, and day and so on. This time dimension is initially populated with the data of a particular period like ten years. Whenever an event/transaction needs to be recorded in the fact table the time key from this dimension for the corresponding time is obtained from the time dimension.

Surrogate key for time dimension is auto incremented just like any other dimension but it is always helpful to have it the right format like for date: 05-Mar-2010 the time key should be 05032010. This proves to be extremely useful as queries can be done directly based on the time key and thus improves performance.
The following figures show how to create and populate time dimension in Sql Server Analysis Services (SSAS):





12 comments:

daniel john said...

Your blog is really excellent. It inspires the readers who has that great desire to lead a better and happier life. Thanks for sharing this information and hope to read more from you.
Term Paper

gamze said...

Informative article:)

微笑每一天 said...

thx u very much, i learn a lot

星美 said...

thank you for you to make me learn more,thank you∩0∩

advantage said...

This was a really quality post. In theory I'd like to write like this too - taking time and real effort to make a good article... but what can I say... I procrastinate alot and never seem to get something done.
Advantage Custom writing – We do it your way

shakar12 said...

Hello,
Palmer Leasing Inc offers one of the largest fleets of Quality Mobile Storage, Transportation and Logistics equipment for rent or lease - ready for your use, without the expense, exposure or hassle of ownership and always at competitive rates.

Luke said...

instead of creating the date key as a typical surrogate auto incremented number, try creating the key as an integer something like this: 20110805 etc. that will help when partitioning a table

1e3f1574-c229-11e0-bcdb-000f20980440 said...

I agree with time format.
This is a great blog. I am learning a lot.

Data Mining for Intelligence

Arbeej12 said...

Hello,
We facilitate the provision of independent analysis to support expert testimony, regulatory or legislative engagements. Frequently, this work includes economic, financial and statistical studies of varying data analysis, technical and http://www.palmerleasing.com.

Melany Flemmings said...

You're so cool! It is really very well written and quit comprehensive in explaining with a very simple language. Was just browsing through the net in my office and happened upon your blog. keeps it up so i can do comments on your post. Thanks :)

detroit lakes hotels

benslin kard said...

Data warehousing creates one database, the number of sources can be nearly limitless, provided the system can handle the volume.

sathya said...

A very nice guide. I will definitely follow these tips. Thank you for sharing such detailed article. I am learning a lot from you.

Dataware Housing Training in Chennai