BeyeBLOGS | BeyeBLOGS Home | Get Your Own Blog

« CDC Technique for dimension table which is based on a multi-table query | Main | What is metrics and what are the different types of metrics »

June 20, 2007

Design technique for Date type columns in fact table for maximum performance

By: Milind Zodge

In the Data warehousing project you have dimensions and facts tables. And when you design a Data warehouse or Data Mart you come across many Date data type attributes. In this article I have pointed out a design technique for Date columns which gives highest performance.

Consider a data mart having a fact table "Order" with many columns like Order Number, Order Date, Shipped Date and Amount and a "Time" dimension which have an entry for each day. You use "time_id" for "Order Date" however most of the time "Shipped Date" is kept as a Date column.

Consider in your reporting system you want to design a report to report number of orders shipped in a particular year. Now you will have format the shipped date column so that you can compare its year portion to get result. If you have a massive fact table this query is going to take more time as it will not be using any index, well you can create index to solve this problem.

Now consider you have a report which report number of orders shipped in a particular month, day, quarter etc. To speed up this operation you will have create index probably more than one. However if we use the id column and index on that column then you can avoid the above problem

Add shipped_date_id column along with the Shipped Date column in the fact table. Derive the value by using Time dimension. So whenever you query you always use index.

This way you can achieve maximum performance without adding more indexes. You can just go to your time dimension get the required ids and join it with your fact table which will use index defined on "shipped_date_id" column.

Posted by Milind Zodge at June 20, 2007 7:30 PM


Hi Ryan,I have been debating about joining Wealthy Affiliate, and I had come to my conclusion of yes then I got an e-mail about your flipping websites. (From Steve at ivetriedthat) he has been a wonderful help because I know nothing about websites and sffiliate marketing. So I was wondering I have the money to join one which one should I spend my money on??Wealthy affiliate????Flipping Websites????Let me know what you thinkThanksCacey

Posted by: contacts for halloween at May 16, 2011 7:05 PM

Post a comment

Remember Me?