Fact Constellation is a schema for representing multidimensional model. It is a collection of multiple fact tables having some common dimension tables. It can be viewed as a collection of several star schemas and hence, also known as Galaxy schema. It is one of the widely used schema for Data warehouse designing and it is much more complex than star and snowflake schema. For complex systems, we require fact constellations.
Here, the pink coloured Dimension tables are the common ones among both the star schemas. Green coloured fact tables are the fact tables of their respective star schemas.
Example:
In above demonstration:
- Placement is a fact table having attributes: (Stud_roll, Company_id, TPO_id) with facts: (Number of students eligible, Number of students placed).
- Workshop is a fact table having attributes: (Stud_roll, Institute_id, TPO_id) with facts: (Number of students selected, Number of students attended the workshop).
- Company is a dimension table having attributes: (Company_id, Name, Offer_package).
- Student is a dimension table having attributes: (Student_roll, Name, CGPA).
- TPO is a dimension table having attributes: (TPO_id, Name, Age).
- Training Institute is a dimension table having attributes: (Institute_id, Name, Full_course_fee).
So, there are two fact tables namely, Placement and Workshop which are part of two different star schemas having dimension tables – Company, Student and TPO in Star schema with fact table Placement and dimension tables – Training Institute, Student and TPO in Star schema with fact table Workshop. Both the star schema have two dimension tables common and hence, forming a fact constellation or galaxy schema.
Advantage: Provides a flexible schema.
Disadvantage: It is much more complex and hence, hard to implement and maintain.
Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.
Recommended Posts:
- Difference between Snowflake Schema and Fact Constellation Schema
- Difference between Star Schema and Fact Constellation Schema
- Difference between Data Warehouse and Data Mart
- Data Mining: Data Warehouse Process
- Difference between Data Lake and Data Warehouse
- Snowflake Schema in Data Warehouse Model
- Star Schema in Data Warehouse modeling
- Characteristics and Functions of Data warehouse
- Data Warehouse Architecture
- ETL Process in Data Warehouse
- Building a Data Warehouse in DBMS
- Difference between Database System and Data Warehouse
- Differences between Operational Database Systems and Data Warehouse
- Types of Keys in Data Warehouse Schema
- Testing in Data warehouse
- Difference between Data Warehouse and Hadoop
- Data Abstraction and Data Independence
- Types of Sources of Data in Data Mining
- Data Normalization in Data Mining
- Characteristics of Biological Data (Genome Data Management)
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.
