#
DHL – Logistics Analytics Data Warehouse
---
## Overview
This project consists of the development of a dimensional Data Warehouse model for logistics and sales analytics using a Star Schema architecture.
The solution was designed to support Business Intelligence operations related to:
- Sales analysis
- Shipment analytics
- Customer analysis
- Product return monitoring
- Profitability analysis
- Regional performance evaluation
The project integrates fact tables, dimension tables and analytical relationships optimized for Power BI reporting and KPI analysis.
---
# Architecture
## Star Schema Model
The data warehouse follows a classic Star Schema architecture composed of:
### Fact Tables
| Table | Description |
|:---:|:---:|
| FactSales | Stores sales transaction metrics |
| FactReturns | Stores product return information |
---
### Dimension Tables
| Table | Description |
|:---:|:---:|
| DimCustomer | Customer information |
| DimProduct | Product hierarchy and categories |
| DimDate | Temporal analysis dimension |
| DimRegion | Geographic information |
| DimShipMode | Shipment methods |
---
# Entity Relationship Structure
The schema connects transactional data with descriptive dimensions through foreign-key relationships optimized for analytical queries.
The model supports:
- Temporal analysis
- Geographic analysis
- Product analytics
- Customer segmentation
- Shipment analysis
- Return tracking
---
# Fact Tables
## FactSales
Main transactional sales table.
### Attributes
| Attribute | Type |
|:---:|:---:|
| RowID | INT |
| Sales | DECIMAL(10,2) |
| Quantity | INT |
| Discount | DECIMAL(3,2) |
| Order Priority | VARCHAR(20) |
| OrderID | VARCHAR(50) |
| Profit | DECIMAL(10,2) |
| Shipping Cost | DECIMAL(10,2) |
### Foreign Keys
| Foreign Key | References |
|:---:|:---:|
| ShipOrder | DimShipMode |
| CodigoPostal | DimRegion |
| DateKey_Ship | DimDate |
| DateKey_Order | DimDate |
| CustomerID | DimCustomer |
| ProductID | DimProduct |
---
## FactReturns
Stores returned product transactions.
### Attributes
| Attribute | Type |
|:---:|:---:|
| ReturnID | INT |
| ReturnQuantity | INT |
| ReturnAmount | DECIMAL(10,2) |
| OrderID | VARCHAR(50) |
| Reason | VARCHAR(50) |
### Foreign Keys
| Foreign Key | References |
|:---:|:---:|
| CodigoPostal | DimRegion |
| DateKey_Order | DimDate |
| CustomerID | DimCustomer |
---
# Dimension Tables
## DimCustomer
| Attribute |
|:---:|
| CustomerID |
| CustomerName |
| Segment |
| CodigoPostal |
| IsCurrent |
---
## DimProduct
| Attribute |
|:---:|
| ProductID |
| ProductName |
| Category |
| SubCategory |
---
## DimDate
| Attribute |
|:---:|
| DateKey_Order |
| DateKey_Ship |
| Year |
| Date |
| Semester |
| Week |
| Quarter |
| Month |
| Weekday |
---
## DimRegion
| Attribute |
|:---:|
| CodigoPostal |
| RegionName |
| CountryName |
| StateName |
| Market |
| CityName |
---
## DimShipMode
| Attribute |
|:---:|
| ShipOrderID |
| ShipModeName |
| Method |
---
# Business Objectives
The model was designed to answer analytical questions such as:
- Which products generate the highest sales?
- Which regions generate the highest profit?
- Which customers spend the most?
- What is the impact of discounts on profit?
- Which shipment methods are most used?
- What are the main return reasons?
- How do sales evolve over time?
---
# Technologies Used
| Technology | Purpose |
|:---:|:---:|
| Power BI | Dashboard visualization |
| Excel | Source data |
| SQL Concepts | Data modeling |
| Star Schema | Data warehouse architecture |
| DAX | KPI calculations |
| Power Query | Data transformation |
---
# Data Warehouse Workflow
```txt
Operational Data
↓
Data Cleaning
↓
Transformation
↓
Dimension Creation
↓
Fact Table Population
↓
Relationship Modeling
↓
Power BI Integration
↓
Business Analytics
```
---
# Analytical Capabilities
## Sales Analytics
- Revenue analysis
- Profitability tracking
- Product performance
- Customer segmentation
---
## Logistics Analytics
- Shipment mode analysis
- Delivery analysis
- Shipping cost monitoring
---
## Return Analytics
- Product return tracking
- Return reason analysis
- Return quantity monitoring
---
## Geographic Analytics
- Regional profit analysis
- Country-level analysis
- Market segmentation
---
# Advantages of the Model
| Advantage | Description |
|:---:|:---:|
| Performance | Optimized analytical queries |
| Scalability | Easy integration of new dimensions |
| Simplicity | Easy BI dashboard integration |
| Flexibility | Supports multiple business analyses |
| Maintainability | Clear separation of dimensions and facts |
---
# Requirements
| Requirement | Description |
|:---:|:---:|
| Power BI Desktop | Dashboard development |
| Excel | Data source handling |
| SQL Knowledge | Optional for extensions |
| Windows OS | Recommended environment |
---
# Future Improvements
- SQL Server migration
- ETL pipeline automation
- Real-time analytics
- Incremental data refresh
- Cloud data warehouse integration
- Predictive analytics
- Machine learning integration
---
# Disclaimer
This project was developed for academic and Business Intelligence purposes.
Users remain responsible for validating data integrity and analytical outputs before operational usage.
---
# Author
José Garcia
Data Scientist
Business Intelligence & Data Warehouse Engineering