Data Warehouse: Create Date Dimension (version 1)


Data Warehouse is “a system used for reporting and data analysis”. In general it is built by dim (dimension) and fact tables (structure and data tables). Here I create a stored procedure to build Date Dimension. Now it builds only calendar data and will be expanded to include “fiscal” data and “offset” from a specific date (today or the last business day).

Let’s say I have a server, dedicated for ETL. I have a schema “etl” for storing objects during the run of the ETL processes and “dw” for storing the actual Data Warehouse. In a production environment I’ll have much more objects (Detailed Log table, Job Agent Schedules, SSIS packages, etc.), but for this example I will simplify the scenario and will focus on dimDate.

Create database.

Create stored procedure to build Date Dimension.

The parameters I pass are:

  • FirstDayOfWeek –> the definition of a “week” differs in different cultures. Sunday in North America, Monday in Europe etc.
  • YearsPast –> how many full past years will be included in dw.dimDate table
  • YearsFuture –> how many full future years will be included in dw.dimDate table

The (innermost) level is day or one line represents a data for one day.

Test the stored procedure.

Check the Log.

Log

I ran Mon twice to cache a plan and again in the end to run the next reports for @FirstDayOfWeek = ‘Monday’.
The max execution time is 1 second.

Quick reporting, based on Date Dimension.

Business Days In Year

Business Days In Quarter

Business Days In Month

Preview –> dimDate (1).xlsx

Delete database.

Keep it simple :-)

Leave a comment

Your email address will not be published. Required fields are marked *