Star Schema

​

Components of a Star Schema

1. Fact Table:
   • The central table in the star schema.
   • Contains quantitative data (measures or metrics) such as sales, revenue, or quantity.
   • Each row in the fact table represents a specific event or transaction.
   • Connected to dimension tables via foreign keys.
   Example:
   • A fact table for a retail store might store sales transactions:
     • Fact_Sales: Transaction_ID, Date_ID, Product_ID, Customer_ID, Store_ID, Quantity_Sold, Total_Amount.
2. Dimension Tables:
   • Surround the fact table like the points of a star.
   • Contain descriptive attributes (context or metadata) related to the facts.
   • Used to filter, group, or label the data in the fact table.
   Examples:
   • Dim_Date: Date_ID, Date, Month, Quarter, Year, Day_of_Week.
   • Dim_Product: Product_ID, Product_Name, Category, Brand, Price.
   • Dim_Customer: Customer_ID, Customer_Name, City, State, Phone_Number.
   • Dim_Store: Store_ID, Store_Name, City, Manager_Name.

​

Example: Star Schema for a Retail Store

Star Schema

​

Fact Table

​

Dimension Tables

1. Dim_Date:

   Date_ID	Date	Month	Quarter	Year	Day_of_Week
   20250101	2025-01-01	January	Q1	2025	Wednesday
   20250102	2025-01-02	January	Q1	2025	Thursday
   20250103	2025-01-03	January	Q1	2025	Friday

2. Dim_Product:

   Product_ID	Product_Name	Category	Brand	Price
   1	Saree	Sarees	Nalli	2500
   2	Kurta-Pajama	Ethnic Wear	Fashion India	1500
   3	Turmeric Powder	Spices	ABC	200

3. Dim_Customer:

   Customer_ID	Customer_Name	City	State	Phone_Number
   1001	Priya Sharma	Mumbai	Maharashtra	9876543210
   1002	Rajesh Patel	Ahmedabad	Gujarat	8765432109
   1003	Anjali Singh	Delhi	Delhi	7654321098

4. Dim_Store:

   Store_ID	Store_Name	City	Manager_Name
   501	Nalli Silks	Chennai	Ramesh Kumar
   502	Fashion India	Bengaluru	Sunita Reddy
   503	ABC Spices	Delhi	Amit Sharma

​

How the Star Schema Works

1. Querying Data:
   • Suppose you want to find the total sales of sarees in Mumbai for January 2025.
   • The query would join the Fact_Sales table with the Dim_Date, Dim_Product, and Dim_Customer tables using their respective keys.
   • Example SQL Query:
     Copy

     SELECT SUM(Fact_Sales.Total_Amount) AS Total_Sales
     FROM Fact_Sales
     JOIN Dim_Date ON Fact_Sales.Date_ID = Dim_Date.Date_ID
     JOIN Dim_Product ON Fact_Sales.Product_ID = Dim_Product.Product_ID
     JOIN Dim_Customer ON Fact_Sales.Customer_ID = Dim_Customer.Customer_ID
     WHERE Dim_Product.Category = 'Sarees'
       AND Dim_Customer.City = 'Mumbai'
       AND Dim_Date.Month = 'January'
       AND Dim_Date.Year = 2025;
     

2. Benefits:
   • Simplified Queries: The star schema makes it easy to write and understand queries.
   • Improved Performance: Optimized for read-heavy analytical workloads.
   • Scalability: Can handle large datasets efficiently.

​

Advantages of Star Schema

1. Query Performance: Fewer joins are required compared to normalized schemas, leading to faster queries.
2. Simplicity: Easy to design and understand, even for non-technical users.
3. Flexibility: New dimensions can be added without disrupting existing queries.
4. Business-Friendly: Aligns well with how business users think about data (e.g., sales, customers, products).

​

Disadvantages of Star Schema

1. Data Redundancy: Dimension tables may contain redundant data (e.g., repeating city names in Dim_Customer).
2. Limited Flexibility for Complex Relationships: Not suitable for scenarios requiring many-to-many relationships between dimensions.
3. Storage Overhead: Large dimension tables can consume significant storage space.

​

Conclusion