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:
clip_image001[4]

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
  5.      
  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))
  13.      
  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:
clip_image002[4]

The Result, monthly order lines:
clip_image003[4]