Designing a Star Schema for SQL Data Warehousing

Designing a Star Schema for Data Warehousing

Write a SQL query to design a star schema for a data warehouse.

Solution:

-- Fact table.
CREATE TABLE SalesFact (
    SaleID INT PRIMARY KEY,
    ProductID INT,
    CustomerID INT,
    DateID INT,
    Quantity INT,
    Amount DECIMAL(10, 2),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
    FOREIGN KEY (DateID) REFERENCES Dates(DateID)
);

-- Dimension tables.
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100)
);

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100)
);

CREATE TABLE Dates (
    DateID INT PRIMARY KEY,
    Date DATE
);

Explanation:

• Purpose of the Query :
• The goal is to design a star schema for efficient data warehousing and reporting.
• Key Components :
• SalesFact: Central fact table storing metrics like quantity and amount.
• Products, Customers, Dates: Dimension tables providing context for analysis.
• Why Use Star Schema? :
• Star schemas simplify queries and improve performance for analytical workloads.
• They provide a clear structure for multidimensional analysis.
• Real-World Application :
• In retail analytics, star schemas enable quick insights into sales trends.

Notes:

• Star schemas are optimized for read-heavy operations like reporting.
• Use surrogate keys in dimension tables for flexibility.
• Important Considerations:
• Balance simplicity with the need for detailed dimensions.

For more Practice: Solve these Related Problems:

• Write a SQL query to design a star schema for a data warehouse analyzing sales data, including fact and dimension tables.
• Write a SQL query to create a star schema for a data warehouse tracking website traffic, with dimensions for users, pages, and time.
• Write a SQL query to design a star schema for a retail analytics data warehouse, including dimensions for products, customers, and dates.
• Write a SQL query to create a star schema for a healthcare data warehouse analyzing patient visits, with dimensions for patients, doctors, and dates.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Resolving Insertion Anomalies in Database Design.
Next SQL Exercise: Resolving Deletion Anomalies in Database Design.