Star Schema

Use Case: Analyzing Sales in a Retail Business

• Imagine a retail company that wants to analyze its sales data. Here’s how the fact and dimension tables would be structured:

Business Problem:

• The retail company needs to understand which products are selling the most, in which stores, and during which time periods. They also want to know how different customer groups are contributing to the sales.

Tables

Customer_DImention Table

Date Dimention table

Product dimensional table

Store DImension table

sales Transaction Table

SQL Query to Populate the Sales Fact Table

INSERT INTO Sales_Fact (Sale_ID, Product_ID, Customer_ID, Store_ID, Date_ID, Sale_Amount, Quantity_Sold)
SELECT 
    s.Sale_ID, 
    p.Product_ID, 
    c.Customer_ID, 
    st.Store_ID, 
    d.Date_ID, 
    s.Sale_Amount, 
    s.Quantity_Sold
FROM 
    Sales_Transactions s               -- Source: Transaction table from OLTP system
JOIN 
    Product_Dimension p ON s.Product_Name = p.Product_Name  -- Join with Product Dimension
JOIN 
    Customer_Dimension c ON s.Customer_Name = c.Customer_Name  -- Join with Customer Dimension
JOIN 
    Store_Dimension st ON s.Store_Name = st.Store_Name  -- Join with Store Dimension
JOIN 
    Date_Dimension d ON s.Transaction_Date = d.Date;  -- Join with Date Dimension

                        +---------------------+
                        |   Date Dimension    |
                        |---------------------|
                        | Date_ID             |
                        | Date                |
                        | Month               |
                        | Year                |
                        +---------------------+
                                ↑
                                |
                                |
    +----------------------+    |    +---------------------+    +---------------------+
    |   Product Dimension   |    |    |  Sales Fact Table   |    |  Customer Dimension |
    |----------------------|    |    |---------------------|    |---------------------|
    | Product_ID            |    |    | Sale_ID             |    | Customer_ID         |
    | Product_Name          |----+----| Product_ID          |----| Customer_Name       |
    | Category              |         | Customer_ID         |    | Age                 |
    | Price                 |         | Store_ID            |    | Gender              |
    +----------------------+         | Date_ID             |    +---------------------+
                                      | Sale_Amount         |
                                      | Quantity_Sold       |
                                      +---------------------+
                                                ↑
                                                |
                                                |
                                      +---------------------+
                                      |   Store Dimension    |
                                      |---------------------|
                                      | Store_ID            |
                                      | Store_Name          |
                                      | Location            |
                                      +---------------------+
                                                ↑
                                                |
                                                |
                                      +---------------------+
                                      |  Sales Transactions  |
                                      |---------------------|
                                      | Sale_ID             |
                                      | Product_Name        |
                                      | Customer_Name       |
                                      | Store_Name          |
                                      | Transaction_Date    |
                                      | Sale_Amount         |
                                      | Quantity_Sold       |
                                      +---------------------+