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):





11 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

fatum said...

Informative article:)

Unknown 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

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

Anonymous said...

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

Data Mining for Intelligence

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

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

Prathyusha said...

It is a very insightful article. You have explained in detail a part of data warehousing in avery easy manner. The written script of the blog is very understandable.
data analytics institute in bangalore

Eva Joseph said...

This information you provided in the blog that is really unique I love it!! Thanks for sharing such a great blog Keep posting. Data Mining
PHP Services
Website Development Company in Chennai
Magento Service Providers
Web Data Extraction Services
Payment Gateway Providers in India
Online Appointment Scheduling Software
Data Extraction Services Company
Free Appointment Scheduling Software

Anonymous said...

They're produced by the very best degree developers who will be distinguished for your polo dress creating. You'll find polo Ron Lauren inside exclusive array which include particular classes for men, women.
data science course in malaysia
data analytics course
tableau course
360DigiTMG

Prakash said...

Really you have done a good job. Thanks for sharing this valuable information....
Informatica MDM Training in Chennai
informatica MDM Training Center Chennai
informatica MDM Training Institute in Chennai
Corporate Training in Chennai
Corporate Training Companies in Chennai
Corporate Training Institute in Chennai