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:
- SELECT OL.LineId
- FROM OrderLine OL
- INNER JOIN DimDate DD
- ON DD.Month
- 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:
· 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: