Posted by: BI-Polar November 13, 2008
Data WareHousing in SQL Server 2005 questions
Login in to Rate this Post:     1       ?         Liked by
Fact Table consists records of lowest level of granularity. In a real world scenario, I see Employee as a dimension not as a Fact table. Fact table will have employee surrogate keys pointing to the employee table(Dimension) and some measures(quantitative values). 

*** Your solution is a simeple StarSchema

In another word, a Fact table will have only surrogate Keys and measures, no such value as employee fullname or any other hierarchical members in it. Those values will be stored in the Dimension tables

You can create a Hiredate (time dimension) and insert its surrogate keys to the Fact table.

update Staging_FactTable
set HireDateKey = Dm_Time.TimeKey
from FactTable (nolock)join DM_Time (nolock)
on cast(convert(varchar,staging_FactTable.hiredate,101)as datetime)= DM_time.Date

For starters, make a staging_FactTable and do most of the scrubbings using SSIS, and put your end product in
the Final Fact table.Which will also help when you do incremental loads

HTH ..DataWarehousing/Business intelligence is Fun!!!


Read Full Discussion Thread for this article