315 lines
5.7 KiB
Markdown
315 lines
5.7 KiB
Markdown
# <div align="center">DHL – Logistics Analytics Data Warehouse</div>
|
||
|
||
---
|
||
|
||
## <div align="center">Overview</div>
|
||
|
||
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.
|
||
|
||
---
|
||
|
||
# <div align="center">Architecture</div>
|
||
|
||
## 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 |
|
||
|
||
---
|
||
|
||
# <div align="center">Entity Relationship Structure</div>
|
||
|
||
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
|
||
|
||
---
|
||
|
||
# <div align="center">Fact Tables</div>
|
||
|
||
## 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 |
|
||
|
||
---
|
||
|
||
# <div align="center">Dimension Tables</div>
|
||
|
||
## 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 |
|
||
|
||
---
|
||
|
||
# <div align="center">Business Objectives</div>
|
||
|
||
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?
|
||
|
||
---
|
||
|
||
# <div align="center">Technologies Used</div>
|
||
|
||
| 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 |
|
||
|
||
---
|
||
|
||
# <div align="center">Data Warehouse Workflow</div>
|
||
|
||
```txt
|
||
Operational Data
|
||
↓
|
||
Data Cleaning
|
||
↓
|
||
Transformation
|
||
↓
|
||
Dimension Creation
|
||
↓
|
||
Fact Table Population
|
||
↓
|
||
Relationship Modeling
|
||
↓
|
||
Power BI Integration
|
||
↓
|
||
Business Analytics
|
||
```
|
||
|
||
---
|
||
|
||
# <div align="center">Analytical Capabilities</div>
|
||
|
||
## 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
|
||
|
||
---
|
||
|
||
# <div align="center">Advantages of the Model</div>
|
||
|
||
| 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 |
|
||
|
||
---
|
||
|
||
# <div align="center">Requirements</div>
|
||
|
||
| Requirement | Description |
|
||
|:---:|:---:|
|
||
| Power BI Desktop | Dashboard development |
|
||
| Excel | Data source handling |
|
||
| SQL Knowledge | Optional for extensions |
|
||
| Windows OS | Recommended environment |
|
||
|
||
---
|
||
|
||
# <div align="center">Future Improvements</div>
|
||
|
||
- SQL Server migration
|
||
- ETL pipeline automation
|
||
- Real-time analytics
|
||
- Incremental data refresh
|
||
- Cloud data warehouse integration
|
||
- Predictive analytics
|
||
- Machine learning integration
|
||
|
||
---
|
||
|
||
# <div align="center">Disclaimer</div>
|
||
|
||
This project was developed for academic and Business Intelligence purposes.
|
||
|
||
Users remain responsible for validating data integrity and analytical outputs before operational usage.
|
||
|
||
---
|
||
|
||
# <div align="center">Author</div>
|
||
|
||
José Garcia
|
||
Data Scientist
|
||
Business Intelligence & Data Warehouse Engineering |