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:

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:

· 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):

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

The Result, monthly order lines: