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.

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

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

🔗 Relational Model Diagram

🔗 Data Warehouse Galaxy Schema

📂 See SQL Queries & Result Screenshots

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.

📦 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 

• 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

• 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;

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

• 🔄 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.