Data Warehouse Fact Constellation Schema and Design

  • Post author:
  • Post last modified:March 15, 2018
  • Post category:Data Warehouse
  • Reading time:3 mins read

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:

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.

data warehouse fact constellation schema

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:

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