Basically, Data warehouse fact constellation schema is viewed as a collection of many star schemas. For each star schema or snowflake schema it is possible to create Fact Constellation schema. This schema is one of the widely used data warehouse design methodology and is also called Galaxy schema. Sophisticated application required the Fact constellation schemas.
Read:
- Star Schema model in Data Warehouse
- Snowflake Schem Model in Data Warehouse
- Step by Step Guide to Dimensional Data Modeling
- Slowly Changing Dimensions (SCD) in Data Warehouse
- Rapidly Changing Dimension in Data Warehouse
Representation of Data Warehouse Fact Constellation Schema
To understand the Fact Constellation Schema, consider the example of sales data warehouse design. As shown in the below example, sales data warehouse consists two fact tables, one, sales fact and other ship fact. Both fact shares the product and date dimension tables. As you can see centralized fact tables holds the primary key from the dimension tables. This data ware house model is a combination of two star schema models.
This schema is more complex than star or snowflake schema architecture desing, which is because it contains multiple fact tables. For examples, typical sales data warehouse may contains sales and shipping fact tables. This allows dimension tables to be shared among many fact tables. The data warehouse fact contellation solution is very flexible, however, it may be hard to manage and support.
Also See:
- Various Data Warehouse Design Approaches:Top-Down and Bottom-Up
- Different Extraction Methods in Data Warehouse
- Types of Dimension Tables in a Data Warehouse
- Types of Fact Tables in a Data Warehouse
- Rapidly Changing Dimension (RCD) in Data Warehouse
Advantages of Data Warehouse Fact Constellation Schema
- Different fact tables are explicitly assigned to the dimensions.
- Provides a flexible schema for implementation
Disadvantages of Data Warehouse Fact Constellation Schema
- Complexity of the schema involved because of several aggregations
- Fact constellation solution is hard to maintain and support