Fact Constellation

• As its name implies, it is shaped like a constellation of stars (i.c., star schemas).
• This schema is more complex than star or snowflake varieties, which is due to the fact that it contains multiple fact tables.
• This allows dimension tables to be shared amongst the fact tables.
• A schema of this type should only be used for applications that need a high level of sophistication.
• For each star schema or snowflake schema it is possible to construct a fact constellation schema.
• That solution is very flexible, however it may be hard to manage and support.
• The main disadvantage of the fact constellation schema is a more complicated design because many variants of aggregation must be considered.
• In a fact constellation schema, different fact tables are explicitly assigned to the dimensions, which are for given facts relevant.
• This may be useful in cases when some facts are associated with a given dimension level and other facts with a deeper dimension level.
• Use of that model should be reasonable when for example, there is a sales fact table (with details down to the exact date and invoice header id) and a fact table with sales forecast which is calculated based on month, client id and product id.
• In that case using two different fact tables on a different level of grouping is realized through a fact constellation model.

Family of stars: