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





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

Anonymous said...

thx u very much, i learn a lot

星美 said...

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

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

Rudra455 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

Anonymous said...

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

Data Mining for Intelligence

Rudra455 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

Unknown 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

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

Unknown said...

Thanks for sharing.
Data Mining software service providers

Afsal said...

Good contentData Mining software services India

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

nakshatra 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