Files
2026-05-22 13:13:56 +00:00

315 lines
5.7 KiB
Markdown
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
# <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