Galaxy Schema in Data Warehouse Modeling

A Galaxy Schema, also known as a Fact Constellation Schema, uses two or more fact tables that share common dimension tables. This setup allows multiple business processes like sales and inventory to be modeled together using shared dimensions such as Time, Product, and Location. This schema is ideal for large, complex data warehouses where multiple subject areas need to be analyzed together. It offers greater flexibility and scalability than star or snowflake schemas.

Note: It is called a Galaxy because it looks like a group of star schemas linked together similar to stars forming a constellation.

Features of Galaxy Schema

• Multiple fact tables: Each fact table represents a specific business process and stores quantitative data for analysis.
• Shared dimension tables: Common dimensions (like Time, Product, Location) are used by multiple fact tables to ensure data consistency.
• Support for complex analytics: Enables integrated analysis across different business domains by connecting shared dimensions.
• Semi-denormalized structure: While dimensions may be somewhat denormalized for performance, fact tables may vary in design depending on the domain.
• Enterprise-scale schema: Suitable for large-scale data warehouse environments with diverse analytical requirements.
• Extensible design: New fact tables and dimensions can be added easily, enhancing long-term flexibility.

Example of Galaxy Schema

In the example below, the data warehouse includes two fact tables: SALES and INVENTORY - that share common dimensions like Product, Time, and Location.

Note: In the aabove diagram:

• PK = Primary Key
• FK = Foreign Key

• SALES table captures transaction details such as Product ID, Customer ID, Order ID, Time ID, Total Sale, Quantity Sold and Discount.
• INVENTORY table tracks warehouse metrics including Product ID, Warehouse ID, Time ID, Stock Level, Reorder Point, and Supplier ID.

Both fact tables share the following dimension tables:

• Product Dimension
• Time Dimension
• Location Dimension

Note: The Supplier Dimension is linked only to the Inventory fact table because supplier information is relevant to stock management and not to sales transactions.

This structure enables comprehensive cross-domain reporting such as analyzing sales trends in relation to inventory levels or customer preferences.

Advantages and Disadvantages