A Simplified Data Modeling Approach
The star schema is a widely used data modeling technique in data warehousing and business intelligence. It is designed to simplify complex data structures and optimize query performance for analytical workloads. The star schema gets its name from its visual representation, which resembles a star , with a central fact table connected to multiple dimension tables.
Fact Table:
Example:
Fact_Sales
: Transaction_ID
, Date_ID
, Product_ID
, Customer_ID
, Store_ID
, Quantity_Sold
, Total_Amount
.Dimension Tables:
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
.Star Schema
Fact_Sales
Transaction_ID | Date_ID | Product_ID | Customer_ID | Store_ID | Quantity_Sold | Total_Amount |
---|---|---|---|---|---|---|
101 | 20250101 | 1 | 1001 | 501 | 2 | 5000 |
102 | 20250102 | 2 | 1002 | 502 | 1 | 1500 |
103 | 20250103 | 3 | 1003 | 503 | 5 | 1000 |
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 |
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 |
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 |
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 |
Querying Data:
Fact_Sales
table with the Dim_Date
, Dim_Product
, and Dim_Customer
tables using their respective keys.Benefits:
Dim_Customer
).The star schema is a powerful and intuitive data modeling technique that simplifies data analysis and improves query performance. By organizing data into a central fact table and surrounding dimension tables, it provides a clear and efficient structure for business intelligence and analytics.