THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Jorg Klein

Microsoft Data & Analytics consultant and Microsoft Data Platform MVP from the Netherlands

SSIS - Blowing-out the grain of your fact table

Recently I had to create a fact table with a lower grain than the source database. My source database contained order lines with a start- and end date and monthly revenue amounts.

To create reports that showed overall monthly revenue per year, lowering the grain was necessary. Because the lines contained revenue per month I decided to blow out the grain of my fact table to monthly records for all the order lines of the source database. For example, an order line with a start date of 1 January 2009 and an end date of 31 December 2009 should result in 12 order lines in the fact table, one line for each month.

To achieve this result I exploded the source records against my DimDate. I used a standard DimDate:

The query below did the job; use it in a SSIS source component and it will explode the order lines to a monthly grain:

Code Snippet
  1. SELECT OL.LineId
  2.       ,DD.ActualDate
  3.       ,OL.StartDate
  4.       ,OL.EndDate
  6.   FROM OrderLine OL
  7.   INNER JOIN DimDate DD
  8.       ON DD.Month
  9.       BETWEEN
  10.       (YEAR(OL.StartDate)*100+MONTH(OL.StartDate))
  11.       AND
  12.       (YEAR(OL.EndDate)*100+MONTH(OL.EndDate))
  14.   WHERE DD.DayOfMonth = 1


Some explanation about this query below:

· I always want to connect a record to the first day of the month in DimDate, that’s why this WHERE clause is used:

Code Snippet
  1. WHERE DD.DayOfMonth = 1

· Because I want to do a join on the month (format: YYYMM) of DimDate I need to format the start and end date on the same way (YYYYMM):

Code Snippet
  1. (YEAR(OL.StartDate)*100+MONTH(OL.StartDate))

The source, order lines with a start and end date:

The Result, monthly order lines:

Published Tuesday, December 15, 2009 11:36 PM by jorg
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS


No Comments

Leave a Comment


About jorg

Jorg Klein, Microsoft Data & Analytics consultant from the Netherlands.
Privacy Statement