CRM / Call Center Tips:

SQL and the data warehouse: Tip on date dimensions

SearchCRM.com

In every data warehouse there is a dimension 'date' which starts with the year dimension and drills down to date. Here is an example of an SQL procedure that populates Year, Quarter, Month, Week, Day and Date dimensions. The table names are as follows:

Year - Year_D
Year_Key - SK
The_Year - Int

Quarter - Quarter_D
Quarter_Key - SK
The_Quarter - Int

Month - Month_D
Month_Key - SK
Quarter_Key - Int [Quarter_D.Quarter_key]
The_Month - Int

Week - Week_D
Week_Key - SK
The_Week - Int

Day - Day_D
Day_Key - SK
The_Day - Int

Date - Date_D
Date_Key - SK
Year_Key - Int [Ref: Year_D.Year_Key]
Month_Key - Int [Ref: Month_D.Month_Key]
Week_Key - Int [Ref: Week_D.Week_Key]
Day_Key - Int [Ref: Day_D.Day_Key]
The_Date - Date

Code:

create procedure Load_Date_Dimension_Sp @year_start int, @year_end int (Full code will be provided in next tip) end

The Procedure can be executed as:

Exec Load_Date_Dimension_Sp 1985, 2005

...which will populate the date dimension for the years 1985 through 2005.

All the syntaxes are based on SQL Server TSQL.

For more information, browse this Learning Guide on Data Quality.

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.