EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials Data Warehouse Tutorial Galaxy Schema
Secondary Sidebar
Data Warehouse Tutorial
  • Basic
    • What is Data Warehouse
    • Data Warehouse tools
    • Career in Data Warehousing
    • Benefits of Data Warehouse
    • Data Warehouse Architecture
    • Data Warehouse Design
    • Data Warehouse Implementation
    • Data Warehouse Features
    • Data Warehouse Modeling
    • Data Warehouse Software
    • Data Warehousing
    • Types of Data Warehouse
    • 10 Popular Data Warehouse Tools
    • Data Lake Architecture
    • Three Tier Data Warehouse Architecture
    • Data Warehouse Process
    • Database Parallelism
    • What is OLTP
    • What is OLAP
    • OLAP Tools
    • Types of OLAP
    • Operations in OLAP
    • MOLAP
    • HOLAP
    • Data Warehouse Schema
    • Data Warehouse Components
    • Snowflake Schema
    • Snowflake Architecture
    • What is Star Schema
    • Galaxy Schema
    • What is Fact Table
    • Kimball Methodology
    • Data Warehouse Testing
    • Operational Data Stores
  • ETL
    • What is Data Mart
    • What is Data Cube
    • What is a Data Lake
    • What is Data Integration
    • What is ETL
    • What is ETL Testing
    • ETL Testing Tools
    • ETL architecture
    • Dimension Table
    • Multidimensional Data Model
    • Fact Constellation Schema
    • ETL Process
  • Interview Questions
    • Data Warehouse Interview Questions
    • ETL Interview Questions
    • ETL Testing Interview Questions
    • Data Warehousing Interview Questions

Related Courses

Business Intelligence Course

All in One Data Science Course

Data Visualization Certification Courses

Galaxy Schema

By Priya PedamkarPriya Pedamkar

Galaxy Schema

Introduction to Galaxy Schema

Galaxy Schema is also known as fact constellation schema, as the name suggests, appears like a galaxy in the space. The design involves more than one fact table, which are connected further with multiple dimension tables that are completely normalized. The fact table contains all the facts, while the dimension table contains the objects and properties, where the dimension tables are connected to the fact tables using the Foreign key from each dimension table.

Essentially, Galaxy schema can be derived as a collection of star schemas interlinked and completely normalized, to avoid redundancy and inaccuracy of data. It can also be a meaningful association of a Snowflake schema with a Star schema, where the fact tables of both schemas can be linked. The Dimension tables from both structures can be linked as per the requirement. The resulting system appears in the structure like a Constellation of stars, wherein fact tables are the stars here. This explains the name Galaxy schema. This type of schema is also called as ‘Fact constellation’ schema, as it has multiple fact tables.

Since this can have both star and snowflake schemas in it, Galaxy Schema is completely normalized. Normalization is nothing but breaking down of information into further more levels, so as to facilitate a meaningful relationship between the fact and dimension tables. This ensures accurate, organized and well-defined data in the system.

Galaxy Schema

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Reasons to Choose Snowflake Schema

Usually, the Schema type is chosen based on multiple parameters that are thought to be important for any given Project, by the Project Management team. Here are the basic characteristics of the Galaxy Schema that can help in making the choice,

  • This model can involve more than one fact table and many dimension tables
  • All the dimension tables are normalized until there is no more space for further normalization.
  • Galaxy Schema makes it possible for the data in the Database to be more distinct, in contrast to star schema but similar to snowflake schema.
  • The Fact Table will have all the facts/ measures, while the Dimension Tables will have foreign keys to connect with the Fact Table.
  • Galaxy Schema allows the Dimension Tables to be linked to other Dimension tables, including the Dimension Tables in the first level.
  • This Multidimensional nature makes it easy to implement on complex Relational Database systems, thus resulting in effective Analysis & Reporting processes.
  • In terms of Accessibility, Complex multiple levels of Join queries are required to fetch aggregated data from the center fact table, using the foreign keys to access all the required Dimension tables, as the system itself is more complex.
  • Multiple Dimension tables, which are created as a result of normalization, serve as lookup tables when querying with complex multi-level Join queries.
  • The process of breaking down all the Dimension tables into multiple small Dimensions until it is completely normalized takes up a lot of storage space compared to other schemas.
  • As the system and querying process is multifaceted, the speed of Data Retrieval from the database systems is by very slow.
  • Different fact tables are explicitly assigned to each of the dimensions available. This is advantageous for facts associated with the given dimension table, and also for other facts that can have a deeper dimension level.

Workflow of Galaxy Schema

Here we will discuss the Workflow of Galaxy Schema by explaining how to create Galaxy Schema along with the pros and cons.

How to Create a Galaxy Schema?

When the system consists of the fact tables A & B that is connected to the Dimension tables C, D, E, F, G, H, I, Galaxy Schema can be structured. Given the rules, fact tables can be connected with one another, and dimension tables can be connected with any fact table and dimension table inside the system, the below example will help in better understanding the underlying concept of a Galaxy Schema.

Galaxy Schema

In this example, the student database has two fact tables – students & Teaching staff. Both the fact tables can have common dimensions – Professional courses, Arts & Science, based on their respective departments. From the student dimension, furthermore, normalization applied to derive co-op and offcampus jobs. This can be further broken down to mention the experience types and internships.

On the other side, the teaching staff will have assignments for students. And, these assignments dimension table can be further normalized to define assessment details. All these dimension tables can be further normalized based on the limitations of the information provided for the designing team by the project.

Pros and Cons of Galaxy Schema

The following pros and cons are mention below –

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,294 ratings)

Pros:

  1. Its multidimensional nature helps in structuring complex Database systems efficiently.
  2. Minimum or no redundancy, as a result of Normalization.
  3. This is a flexible Schema, considering the complexity of the system.
  4. Data Quality will be fine, as Normalization provides the advantage for well-defined tables/ data formats.
  5. When queried with Joins, clear & accurate data can be extracted.
  6. High Data quality & accuracy helps in creating exceptional Reporting & Analytical results.

Cons:

  1. Galaxy schema can be Complex in structure.
  2. Working on this schema is tedious, as the complexity in both Schema and database system makes it more intricate all together.
  3. Data retrieval is done with multi-level joins combined with conditional expressions.
  4. The number of levels of normalization is expected, depending on the depth of the given database.
  5. Maintenance and support tasks get difficult as Galaxy schema is applied for larger database systems with complex structures.
  6. Large storage space is required for its larger design arrangement and detailed querying process.
  7. The analysis gets difficult, as it has no limitation on how many fact and dimension tables it can have.

Conclusion

When the project team is provided with more than one fact tables, than has both individually connected dimensions and dimensions shared amongst the fact tables, Galaxy Schema can be a well-organized solution. Most of the advanced applications might need multiple fact tables to share dimension tables, and Galaxy Schema is a given in such cases. If the requirement comes with consent for more storage space, acceptance for low performance, structures with more than one fact tables & more than one dimension tables, time & space for normalization, Galaxy Schema will be the best solution for that particular Database system.

Recommended Articles

This is a guide to Galaxy Schema. Here we discuss the workflow of Galaxy Schema by explaining how to create Galaxy Schema? the pros and cons respectively. You can also go through our other suggested articles to learn more –

  1. What is Star Schema?
  2. What is MySQL Schema?
  3. Docker Alternatives
  4. What is Microservices?
Popular Course in this category
All in One Data Science Bundle (360+ Courses, 50+ projects)
  360+ Online Courses |  1500+ Hours |  Verifiable Certificates |  Lifetime Access
4.7
Price

View Course

Related Courses

Business Intelligence Training (12 Courses, 6+ Projects)4.9
Data Visualization Training (15 Courses, 5+ Projects)4.8
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more