In this lesson, you will learn how to design a "Galaxy Schema" to allow multiple fact tables to share the same descriptive dimensions, enabling unified reporting across different parts of your business.
Beyond the Star Schema
Up until now, we have mostly focused on the Star Schema, where a single fact table sits in the center of its own dimension tables. However, in a real-world enterprise, you rarely have just one business process. You might have a Fact_Sales table for transactions and a Fact_Inventory table for stock levels.
If these tables exist in isolation, your data is "siloed." A Galaxy Schema (also known as a Fact Constellation) solves this by allowing multiple fact tables to point to the same Conformed Dimensions.
How it Works
Think of your dimensions—like Dim_Date, Dim_Product, or Dim_Store—as a shared library. When you structure a Galaxy Schema, you design these dimensions so they can be "plugged into" any number of fact tables. Because these dimensions are conformed, you can write a single report that joins Fact_Sales and Fact_Inventory to the same Dim_Product to answer questions like: "Which products are selling fast but have low stock?"
Here is a simplified view of how the tables connect:
-- Fact Tables
Fact_Sales (Sales_ID, Date_SK, Product_SK, Store_SK, Amount)
Fact_Inventory (Inventory_ID, Date_SK, Product_SK, Store_SK, Qty_On_Hand)
-- Shared Dimensions (Conformed)
Dim_Date (Date_SK, Full_Date, Month, Year)
Dim_Product (Product_SK, Product_Name, Category)
Dim_Store (Store_SK, Store_Name, Location)
In this architecture, Fact_Sales and Fact_Inventory both reference the same Product_SK found in Dim_Product. By using these shared keys, the data remains consistent, and you avoid the headache of maintaining separate product lists for different departments. The "Galaxy" name comes from the visual appearance of multiple "star" schemas sharing the same dimensional "planets."
By aligning your fact tables to these shared dimensions, you ensure that "Category A" in your sales report means the exact same thing as "Category A" in your inventory report.
Key Takeaway: A Galaxy Schema uses shared, conformed dimensions to link multiple fact tables together, allowing for accurate, cross-functional analysis across different business processes.