Sql Project Overview: Sales and Operations Data Warehouse

4 min readAug 12, 2024

This project involves the design and implementation of a data warehouse for a retail company. The data warehouse is structured to support the analysis and reporting of key business operations, including sales, inventory management, employee performance, supplier relationships, and promotions. The database schema consists of fact tables and dimension tables that enable detailed and efficient querying of business data

Sql Project Overview: Sales and Operations Data Warehouse

Key Components:

Dimension Tables:

dim_customer: Stores detailed information about customers, including contact details and location.

CREATE TABLE dim_customer (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(255),
    customer_email VARCHAR(255),
    customer_phone VARCHAR(50),
    customer_address VARCHAR(255),
    customer_city VARCHAR(100),
    customer_state VARCHAR(100),
    customer_zipcode VARCHAR(20),
    customer_country VARCHAR(100)
);

dim_product: Contains product details like category, brand, and price.

CREATE TABLE dim_product (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255),
    product_category VARCHAR(100),
    product_subcategory VARCHAR(100),
    product_brand VARCHAR(100),
    product_price DECIMAL(10, 2)
);

dim_store: Provides data on store locations, including address and region.

CREATE TABLE dim_store (
    store_id INT PRIMARY KEY,
    store_name VARCHAR(255),
    store_address VARCHAR(255),
    store_city VARCHAR(100),
    store_state VARCHAR(100),
    store_zipcode VARCHAR(20),
    store_country VARCHAR(100)
);

dim_employee: Holds information about employees, such as their role, department, and hire date.

CREATE TABLE dim_employee (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(255),
    employee_role VARCHAR(100),
    employee_department VARCHAR(100),
    employee_hire_date DATE,
    employee_salary DECIMAL(10, 2)
);

dim_date: Manages date-related data, including the day, month, year, and holiday indicators.

CREATE TABLE dim_date (
    date_id INT PRIMARY KEY,
    date DATE,
    day_of_week VARCHAR(20),
    month INT,
    quarter INT,
    year INT,
    is_holiday BOOLEAN
);

dim_supplier: Stores supplier details, including contact information and location.

CREATE TABLE dim_supplier (
    supplier_id INT PRIMARY KEY,
    supplier_name VARCHAR(255),
    supplier_contact VARCHAR(255),
    supplier_phone VARCHAR(50),
    supplier_email VARCHAR(255),
    supplier_address VARCHAR(255),
    supplier_city VARCHAR(100),
    supplier_state VARCHAR(100),
    supplier_zipcode VARCHAR(20),
    supplier_country VARCHAR(100)
);

dim_promotion: Contains details about promotions, such as promotion type, discount rate, and validity period.

CREATE TABLE dim_promotion (
    promotion_id INT PRIMARY KEY,
    promotion_name VARCHAR(255),
    promotion_type VARCHAR(100),
    promotion_discount DECIMAL(5, 2),
    start_date DATE,
    end_date DATE
);

dim_time: Tracks time details, including hours, minutes, seconds, and the time of day.

CREATE TABLE dim_time (
    time_id INT PRIMARY KEY,
    hour INT,
    minute INT,
    second INT,
    time_of_day VARCHAR(20)  -- Morning, Afternoon, Evening, Night
);

2. Fact Tables:

fact_sales: Tracks all sales transactions, capturing details like date, time, customer, product, store, employee, and promotions involved in each sale.

CREATE TABLE fact_sales (
    sales_id INT PRIMARY KEY,
    date_id INT,
    time_id INT,
    customer_id INT,
    product_id INT,
    store_id INT,
    employee_id INT,
    promotion_id INT,
    quantity_sold INT,
    total_sales_amount DECIMAL(15, 2),
    FOREIGN KEY (date_id) REFERENCES dim_date(date_id),
    FOREIGN KEY (time_id) REFERENCES dim_time(time_id),
    FOREIGN KEY (customer_id) REFERENCES dim_customer(customer_id),
    FOREIGN KEY (product_id) REFERENCES dim_product(product_id),
    FOREIGN KEY (store_id) REFERENCES dim_store(store_id),
    FOREIGN KEY (employee_id) REFERENCES dim_employee(employee_id),
    FOREIGN KEY (promotion_id) REFERENCES dim_promotion(promotion_id)
);

fact_inventory: Manages inventory levels, recording the quantity of products in stock, inventory value, and supplier information.

CREATE TABLE fact_inventory (
    inventory_id INT PRIMARY KEY,
    date_id INT,
    store_id INT,
    product_id INT,
    supplier_id INT,
    quantity_in_stock INT,
    inventory_value DECIMAL(15, 2),
    FOREIGN KEY (date_id) REFERENCES dim_date(date_id),
    FOREIGN KEY (store_id) REFERENCES dim_store(store_id),
    FOREIGN KEY (product_id) REFERENCES dim_product(product_id),
    FOREIGN KEY (supplier_id) REFERENCES dim_supplier(supplier_id)
);

fact_employee_performance: Measures employee performance by tracking their sales, customers served, and associated store locations.

CREATE TABLE fact_employee_performance (
    performance_id INT PRIMARY KEY,
    date_id INT,
    employee_id INT,
    store_id INT,
    total_sales DECIMAL(15, 2),
    customers_served INT,
    FOREIGN KEY (date_id) REFERENCES dim_date(date_id),
    FOREIGN KEY (employee_id) REFERENCES dim_employee(employee_id),
    FOREIGN KEY (store_id) REFERENCES dim_store(store_id)
);

fact_supplier_orders: Records orders placed with suppliers, including the quantity ordered, product details, and the store receiving the order.

CREATE TABLE fact_supplier_orders (
    order_id INT PRIMARY KEY,
    date_id INT,
    supplier_id INT,
    product_id INT,
    store_id INT,
    quantity_ordered INT,
    order_amount DECIMAL(15, 2),
    FOREIGN KEY (date_id) REFERENCES dim_date(date_id),
    FOREIGN KEY (supplier_id) REFERENCES dim_supplier(supplier_id),
    FOREIGN KEY (product_id) REFERENCES dim_product(product_id),
    FOREIGN KEY (store_id) REFERENCES dim_store(store_id)
);

fact_promotions: Analyzes the effectiveness of promotions by tracking total discounts given and sales generated during promotional periods.

CREATE TABLE fact_promotions (
    promotion_id INT,
    date_id INT,
    store_id INT,
    product_id INT,
    total_discount_given DECIMAL(15, 2),
    total_sales_in_promotion DECIMAL(15, 2),
    PRIMARY KEY (promotion_id, date_id, store_id, product_id),
    FOREIGN KEY (promotion_id) REFERENCES dim_promotion(promotion_id),
    FOREIGN KEY (date_id) REFERENCES dim_date(date_id),
    FOREIGN KEY (store_id) REFERENCES dim_store(store_id),
    FOREIGN KEY (product_id) REFERENCES dim_product(product_id)
);

Relationships:

Fact tables are linked to dimension tables via foreign keys, enabling complex queries across multiple business dimensions such as time, product, location, and customer.
Common dimensions like dim_date, dim_store, and dim_product are shared across fact tables, facilitating comprehensive data analysis.

Use Cases:

Sales Analysis: Understanding sales trends across different products, stores, and time periods.
Inventory Management: Monitoring stock levels and managing supplier orders.
Employee Performance: Evaluating the performance of employees based on sales and customer service.
Supplier Relationships: Analyzing supplier reliability and order fulfillment.
Promotion Effectiveness: Assessing the impact of marketing promotions on sales and revenue.

This data warehouse structure supports decision-making by providing insights into various aspects of the retail business, helping optimize operations, improve customer satisfaction, and increase profitability.