📊 E-Commerce Data Warehouse

📝 Overview

This project is an E-Commerce Data Warehouse built using SQL Server and SSIS to support advanced business intelligence and analytics.
The system consolidates transactional data from a relational source into a dimensional model designed for reporting and insights into sales, payments, and inventory.

The main objectives:

• 🎯 Support decision-making with reliable analytical data.
• 📈 Provide insights into customer behavior, payments, sales trends, and supplier performance.
• 🔄 Enable ETL workflows for clean, transformed, and historical data storage.

---

🛠️ Tech Stack

• 🗄️ Database: Microsoft SQL Server
• ⚙️ ETL Tool: SQL Server Integration Services (SSIS)
• 🗂️ Modeling: ER Diagrams (Operational DB & DW)
• 📑 Documentation: PDF report with queries and analysis

---

🗃️ Physical Data Model

The warehouse is designed following a star schema, with transactional fact tables and conformed/shared dimensions.

🏛️ Relational Source (Operational DB)

🔗 Relational Model Diagram

🪐 Data Warehouse Schema

🔗 Data Warehouse Galaxy Schema

🖼️ Queries & Results

📂 See SQL Queries & Result Screenshots

---

🔄 ETL Workflow (SSIS)

The data warehouse is built through automated ETL workflows:

1. 📥 Extract: Pull data from the operational relational database.
2. 🔧 Transform: Clean, standardize, and integrate data (handling nulls, formatting, keys).
3. 📤 Load: Insert transformed data into dimensional and fact tables.

✅ This ensures that the warehouse always contains clean, historical, and analysis-ready data.

---

📂 Repository Structure

📦 e-commerce-data-warehouse
 ┣ 📂 SSIS-Packages                # SSIS packages for data extraction, transformation, loading
 ┃
 ┣ 📂 Relational-DB
 ┃ ┣ 📜 Physical ERD.png           # Relational Schema Diagram
 ┃ ┣ 📜 Schema.sql                 # SQL script for datasource
 ┃
 ┣ 📂 DataWarehouse-Design
 ┃ ┣ 📜 Galaxy-Schema-DWH.png      # Data warehouse schema
 ┃ ┣ 📜 Project Details.pdf        # Full report with detailed queries and insights
 ┃ ┣ 📜 DW-Staging-Schema.sql      # SQL script to create Staging & Data Warehouse tables  
 ┃
 ┣ 📂 Queries-script
 ┃ ┣ 📜 Queries-Script.sql         # SQL scripts containing the main analytical queries executed on fact tables 
 ┃
 ┣ 📂 Screenshots                  # Contains screenshots of executed queries with their result sets for better visualization 
 ┃
 ┣ 📜 README.md

---

📈 Example Queries & Insights

💳 Payment Fact Table

• Screenshots available in Screenshots/ folder.
• Includes queries for:
  1. 🔢 Total Number of Payments
  2. 🏆 Top Paying Customers
  3. 📅 Daily Revenue Report

-- top paying customers
SELECT C.custname, SUM(fp.PaymentAmount) AS TotalSpent
FROM DWH.f_payment fp
JOIN DWH.customer c ON fp.customerid = C.surkey
GROUP BY C.custname
ORDER BY TotalSpent DESC

---

📦 Inventory Purchases Fact Table

• Screenshots available in Screenshots/ folder.
• Includes queries for:
  1. 💰 Top 5 Most Expensive Purchases
  2. 🏭 Total Cost Per Supplier
  3. 📊 Total Units Purchased Per Product

-- Top 5 Most Expensive Purchases
SELECT TOP 5
P.productname,
S.suppliername AS SupplierName,
F. [unitspurchased],
F. [costofpurchase]
FROM
DWH. f_inventory_purchases F
JOIN
DWH. product P ON F.productid = P.surkey
JOIN
DWH. supplier S ON F.supplierid = S.surkey
ORDER BY
F. [costofpurchase] DESC;

---

🛒 Sales Fact Table

• Queries were implemented but not included in this README for brevity.
• 📑 For full details and results, please check DataWarehouse-Design/Project Details.pdf.

---

🚀 Key Takeaways

• 🔄 Built ETL workflows with SSIS for loading data from operational DB into the DW.
• 🗄️ Designed transactional fact tables for Sales, Payments, and Inventory Purchases.
• 📊 Delivered analytical queries and insights on customers, suppliers, and sales.
• ✅ Ensured clean, historical, and query-optimized data for BI reporting.