🚚 Transportation Organization Data Warehouse

A comprehensive SQL Server Data Warehouse solution designed to centralize and analyze data for a transportation organization. The project implements a Kimball-style star schema across multiple business domains: Financial, Human Resources, Maintenance, and Transport Operations.

Built with T-SQL, this data warehouse provides robust ETL pipelines, dimensionally modeled data marts, and supporting documentation to enable advanced analytics and reporting.

---

📁 Project Structure

Transportation-Organization-Data-Warehouse/
├── DW/                         # Core Data Warehouse implementation
│   ├── Global/                 # Global dimension tables (shared across marts)
│   ├── Financial/              # Financial data mart (star schema)
│   ├── Human-Resource/         # HR data mart (star schema)
│   ├── Maintenance/            # Maintenance data mart (star schema)
│   ├── Transport/              # Transport operations data mart (star schema)
│   └── Temp/                   # Temporary/staging tables for ETL processing
├── Doc/                        # Documentation and schema diagrams
│   ├── Documentation & Report.pdf
│   ├── *-Mart-StarModel.(drawio|jpg)  # Visual star schema models
└── Back Up/                    # Archived source and backup files

---

🗄️ Data Warehouse Modules

Module	Description	Key Files
Global	Shared dimension tables used across all data marts	DDL_Create_Global_Dimension_Tables.sql
Financial	Tracks revenue, expenses, and financial KPIs	DDL_Create_Financial_Mart_Tables.sql
Human-Resource	Manages employee data, payroll, and workforce analytics	DDL_Create_Human-Resource_Mart_Tables.sql
Maintenance	Monitors fleet maintenance schedules and costs	DDL_Create_Maintenance_Mart_Tables.sql
Transport	Analyzes trip data, routes, and operational metrics	DDL_Create_Transport_Mart_Tables.sql
Temp	Staging area for data cleansing and transformation	DDL_Create_Temp_Tables.sql

---

⚙️ ETL Implementation

Each data mart includes dedicated ETL scripts for two loading strategies:

• DDL_ETL_First_Time_Fill_Dimensions.sql – Initial population of dimension tables (full load).
• DDL_ETL_Incrementally_Fill_Dimensions.sql – Incremental updates for ongoing data integration (Change Data Capture style).

The ETL process extracts data from the source operational system, transforms it into the star schema structure, and loads it into the respective fact and dimension tables.

---

🎯 Star Schema Design

The data warehouse follows star schema modeling principles for optimal query performance in analytical workloads. Visual schema diagrams (provided as .drawio and .jpg files) illustrate the fact-dimension relationships for each data mart:

• Financial-Mart-StarModel – Measures: revenue, cost, profit. Dimensions: time, account, department, etc.
• HR-Mart-StarModel – Measures: salary, headcount, turnover. Dimensions: employee, time, position, etc.
• Maintence-Mart-StarModel – Measures: maintenance cost, downtime. Dimensions: vehicle, time, maintenance type, etc.
• Transport-Mart-StarModel – Measures: trip count, distance, fuel consumption. Dimensions: route, vehicle, driver, time, etc.

View all schema diagrams in the /Doc folder

---

🛠️ Setup Instructions

Prerequisites

• Microsoft SQL Server (2016 or later recommended)
• SQL Server Management Studio (SSMS) or Azure Data Studio
• Sufficient database permissions (CREATE TABLE, INSERT, ALTER, etc.)

Installation Steps

1. Clone the repository

   git clone https://github.com/MME1893/Transportation-Organization-Data-Warehouse.git

2. Run Global dimension tables first (foundation for all marts)

   • Execute DW/Global/DDL_Create_Global_Dimension_Tables.sql in your SQL Server database.

3. Create the data mart tables for the domains you need:

   • DW/Financial/DDL_Create_Financial_Mart_Tables.sql
   • DW/Human-Resource/DDL_Create_Human-Resource_Mart_Tables.sql
   • DW/Maintence/DDL_Create_Maintence_Mart_Tables.sql
   • DW/Transport/DDL_Create_Transport_Mart_Tables.sql
   • DW/Temp/DDL_Create_Temp_Tables.sql (for staging)

4. Run the ETL scripts in the correct sequence:

   • First, execute the First_Time_Fill scripts to populate dimensions.
   • Then, run the Incrementally_Fill scripts to load the fact tables.

⚠️ Note: The exact execution order depends on foreign key relationships between dimension and fact tables. Review the scripts before running them in production.

---

📊 Usage Examples

Once the data warehouse is built and populated, you can run analytical queries such as:

-- Example: Total maintenance cost per vehicle type
SELECT v.VehicleType, SUM(m.MaintenanceCost) AS TotalCost
FROM Maintenance_Fact m
JOIN Vehicle_Dim v ON m.VehicleID = v.VehicleID
GROUP BY v.VehicleType;

Connect the database to Power BI, Tableau, or SQL Server Reporting Services (SSRS) for interactive dashboards and reports.

---

📖 Documentation

The /Doc folder contains:

• Documentation & Report.pdf – Comprehensive project report covering architecture, design decisions, and implementation details.
• Star schema diagrams (in both draw.io and JPEG formats) – Visual representation of the fact and dimension tables for each data mart.

---

🗂️ Backup Archive

The /Back Up folder contains ZIP archives of earlier versions:

• DW.zip – Archived data warehouse scripts
• SA.zip – Archived source system artifacts
• Source.zip – Original source data backups

---

📜 License

This project is open-source. See the repository for licensing details.

---

🙏 Acknowledgments

• Built with Microsoft SQL Server and T-SQL
• Schema design inspired by Kimball's dimensional modeling techniques
• Diagrams created with draw.io

---

📬 Contact & Support

For questions or contributions, please open an issue on GitHub.

---

⭐ If this project helps you, consider giving it a star!