Fact Table in Datawarehouse
Posted on | December 31, 2009 | 1 Comment
Fact table is the centre table in snow flake scheme.A fact is measure of business for example sales.Generally facts are numbers.Dimension on other help us to analyze facts.Example of Dimension will be Region.Apart from these measures the fact table contain foreign key for dimension table.Fact table is generally large in size.Fact table are highly normalized because of that reason.
Measures in Fact Table can be of three type
Additive: Additive facts are facts that can be summed up through all of the dimensions in the fact table.
Semi-Additive: Semi-additive facts are facts that can be summed up for some of the dimensions in the fact table but not the others.
Non-Additive: Non-additive facts are facts that cannot be summed up for any of the dimensions present in the fact table.
Let us see all three type of measures in fact table.Suppose you have fact table has
Region
Year
Per-Profit
Sale
Sale and Per-Profit are the facts while region & Year are dimension.Sale is additive fact since it makes sense to add it across Region and Year.Percentage profit is non-additive fact as it doesn’t make sense to add it across Region and Year.
Every fact table has primary key which help in uniquely identifying the record.
Some fact table don’t contain facts at all.Such table are called factless Fact table.These table are used to find event which occurred or didn’t occurred.
The other concept of fact table is to understand what is meant by granularity.Granularity means what is the lowest level of information that will be stored in fact table.
Comments
One Response to “Fact Table in Datawarehouse”
Leave a Reply
February 4th, 2010 @ 7:21 am
Just killing some free time on Stumbleupon and I found your post . Not typically what I prefer to learn about, but it was definitely worth my time. Thanks.