Shalini's Knowledge Share

There are different types of dimensional modelling, facts and dimensions. Most of the terms are quite similar and the definitions are overlapping across them.
Star Schema and Fact Constellation is one such example which is explained below:

1. Star Schema

• Traditional dimensional modelling that has a large fact table that relates to multiple dimensions.
• Simple design

2. Fact Constellation

• Multiple fact tables relating to multiple \’shared\’ dimensions.
• Combination of star or snowflake schema, can also be taken as a \’galaxy\’ schema
• Complex design as there are many facts and different aggregations must be considered
• Flexible solution but difficult to maintain

In my opinion, a large and complex data warehouse will have more of a fact constellation due to the impossibility of having disjoint star schema for two reasons;
1. There maybe certain dimensions that can be shared among different facts; ex: Calendar, Time, Geography can be shared by sales and employee fact tables.
2. When there are fact tables associated with different dimensional models; ex: a fact table with Sales forecast will be based on the month and another fact table for sales for the exact date.