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.

• SALES fact table has attributes: Product ID, Customer ID, Order ID, Time ID, Total Sale, Quantity Sold, and Discount.
• INVENTORY fact table includes: Product ID, Warehouse ID, Time ID, Stock Level, Reorder Point, and Supplier ID.

Both fact tables share the following dimension tables:

• Product Dimension: Product ID, Product Name, Category, Unit Price.
• Time Dimension: Time ID, Date, Month, Quarter, Year.
• Location Dimension: Warehouse ID, City, Region, Country.
• Supplier Dimension (used only by Inventory): Supplier ID, Supplier Name, Contact Info.

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

Advantages and Disadvantages