THE SQL Server Blog Spot on the Web

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

Jorg Klein

Jorg Klein, Microsoft-only BI consultant 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:
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]

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

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About jorg

Jorg Klein, Microsoft-only BI consultant from the Netherlands, blogging about BI on SQL Server with a focus on SSIS.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement