http://technet.microsoft.com/en-us/magazine/gg398018.aspx
Code download available at: SampleCode2009_08.exe (151 KB)
Data is the most important part of any business intelligence (BI) solution. As explained in Stacia Misner’s, “Planning Your First Microsoft BI Solution,” getting data into your BI solution and maintaining it once it’s there involves several steps. BI professionals refer to these steps as extract, transform and load ( ETL) processes. Even if you don’t plan to pursue a career focused on BI, you can still take advantage of ETL techniques and tools to manage the data you need to inform the day-to-day decisions you make in your job. In this article, I’ll show you how to design and build a simple data mart to illustrate how you can use SQL Server 2008 Integration Services (SSIS) to perform ETL for your own BI solution.
http://forum.kimballgroup.com/t946-assitance-with-time-dimension
How to model a time dimension in the Fact table.
However based on the latest publications by Kimball Group, there are two points worth mentioning regarding the Date and Time Dimensions, as they run against Kimball’s initial idea:
1. In addition to date key from a date dimension, embed a full SQL date-time stamp directly in the fact table for all queries requiring the extra precision. Normally, you don’t need a time dimension unless there is a predefined time unit. Reference: The Data Warehouse ETL Toolkit – 2004.
2. Use smart yyyymmdd integer key for date dimension instead of totally meaningless surrogate date key. The self derived smart date key is more resilient to changes on date dimension, and makes table partitioning more manageable. Reference: The Microsoft Data Warehouse Toolkit: With SQL Server2005 and the Microsoft Business Intelligence Toolset – 2006.
http://www.kimballgroup.com/html/designtipsPDF/DesignTips2004/KimballDT51LatestThinking.pdf
The most common and useful time dimension is the calendar date dimension with the granularity of a
single day. This dimension has surprisingly many attributes. Only a few of these attributes (such as
month name and year) can be generated directly from an SQL date-time expression. Holidays, work
days, fiscal periods, week numbers, last day of month flags, and other navigational attributes must
be embedded in the calendar date dimension and all date navigation should be implemented in
applications by using the dimensional attributes. The calendar date dimension has some very
unusual properties. It is one of the only dimensions that is completely specified at the beginning of
the data warehouse project. It also doesn’t have a conventional source. The best way to generate
the calendar date dimension is to spend an afternoon with a spreadsheet and build it by hand. Ten
years worth of days is less than 4000 rows.
