🏭 Warehouse Intelligence & Analytics Platform (WIAP)

This project mirrors a real enterprise warehouse data platform and showcases depth across,

* Data Engineering/ Data analytics (DE/DA)
* Data Modeling & Power BI Development
* Operational KPI/ SLA Frameworks
* End-to-end FMCG, Warehouse, Logistics and QC Domain Knowledge

📊 Interact with the Online Dashboard
🎦 Watch the Youtube Video

WIAP is a full-stack data engineering + analytics + warehouse operations intelligence platform designed to simulate and analyze real FMCG/3PL warehouse environments.

This project is basically a warehouse digital twin:

Generates complex synthetic operational data (Inbound, Outbound, Inventory, Transport, Quality, HR).
Loads everything into a PostgreSQL warehouse with a fully normalized schema.
Creates views powered by heavy SQL, data cleaning, imputations, and logic transformations.
Builds an analytic semantic layer in Power BI with M-ETL pipelines and optimized schemas.
Delivers 70+ operational KPIs used in real warehouse management.

Everything mirrors real industry workflow learned from 9+ years working in food FMCG QA, warehousing, and logistics.

📋 Project Planning & Scoping

Goals

Build a realistic, scalable warehouse data ecosystem.
Showcase analytics engineering + data engineering workflow end-to-end.
Create a modular platform that supports future ML and forecasting.
Demonstrate strong BI concepts: modeling, KPI governance, DAX standards.
Highlight my operational intelligence from QA + FMCG + logistics.

Objectives

Design enterprise-grade schema (normalized + views).
Create multi-domain synthetic data with natural randomness.
Build reproducible ETL logic.
Develop BI dashboards that mimic real 3PL/WH KPIs/ SLAs.
Create documented KPI dictionary for governance.

♻️ Basic Warehouse Operation Flow

End-to-end WH operation

🗂️ GitHub Projects Board

A Kanban board is included to track:

Data generation tasks
Schema iterations
Loader fixes
View redesigns
Power BI modeling
KPI validation
Future roadmap (Phase II Ops)

Progress tracking with GitHub Projects - Kanban board

🛠️ Tech Stack

Data Engineering & Analytics Stack

Category	Tools
Python & Data Generation
LLM Integration
Database Connectivity
SQL Database Management

BI & Analytics Engineering

Category	Tools
Power Platform & Visualization

Development & Workflow

Category	Tools
Version Control, Project Tracking & Documentation

AI & Productivity Tools

Category	Tools
AI Assistance & Creative Tools

📂 Project Folder

Data_Analytics_Projects_Warehouse_Process_Analysis_Pipeline/
├── data/
│   └── raw/                           # Raw data generated (Python Libraries + LLM)
│    
├── src/                               # Production-ready Python codes
│   ├── data_generator.py              # LLM functions and DataFrame creation logic
│   └── data_loader.py                 # Logic for loading data from CSVs into the PostgreSQL database.
|
├── sql/                               # PSQL scripts
│   └── schema.sql                     # CREATE TABLE statements for the database schema
|
├── KPI_docs/                          # Extensions to the main README.md to expand KPIs
│   ├── KPI_COO.md                     # KPIs in COO's view
│   ├── KPI_Inbound.md                 # KPIs in Inbounds & Returns Page
│   └── KPI_Outbound.md                # KPIs in Outbounds Page
│
├── reports/                           # Final reports and visualizations
│   ├── project_doc.docx.py            # Project Report 
│   ├── project_video.mp4              # Dashboard/ Report walkthrough
│   └── Operations_Dashboard_P01.pbix  # Data cleaning, data modeling, data analysis, visualization and publish
│
├── images/                            # All relevant image files
|
├── LICENSE.md                         # MIT License
├── .gitignore                         # Files and folders to ignore in Git.
└── README.md                          # Project documentation

🧰 Data Pipeline - from mind to matrix

🧠 Idea → 🎨 Design → 🔁 ETL → 📊 Analyze → 🎛️ Dashboard → 📈 Results

🏗️ Data Architecture

Python + VS Code - data_generator.py

Python-generated synthetic datasets
SQL-first normalized schema (PK/FK, indexes)
Data cleaning via SQL views
ETL pipeline using SQLAlchemy
Power BI data modeling & measure tables
Department-wise KPI models

🧱 Schema Design

PostgreSQL + VS Code - schema.sql

4 standalone dimension tables
10 dependent operational tables
2 monitoring/incident tables
16 analytics-ready views
Full PK/FK relationships
Indexes for query performance

The schema follows a Raw → Clean Views → PBI ETL → BI Model architecture.

🔄 ETL & Loading

Python + VS Code - data_loader.py

FK-safe load sequence
UPSERT logic (ON CONFLICT)
Automated logging
Idempotent reruns

🧹 View Layer (Advanced SQL)

PostgreSQL + DBeaver - views.sql

LLM hallucination corrections
Missing value imputation
NaN → TRUE logic conversions
Dimensional transformations
Standardized naming conventions
RegEx cleanup
Time-casting, type standardization
Derived KPIs (cycle times, severities, statuses)
Normalization of messy logs

🔧 ETL in Power BI

Power Query Editor:

Data profiling
Quality checks
Column-level lineage
Conditional transformations
Metadata management
Governance patterns
Versioned query groups
Staging → Clean → Fact → Dim layering

📊 Data Modeling (Power BI)

Model highlights:

Complex-schema with clean relationship directions
Row-level granularity by operation
Model optimization:
- Field parameter grouping
- Surrogate keys
- Removing high-cardinality clutter
- Merged fact tables
Department-wise measure tables
KPI folders for governance

Data model

📈 Analytics Delivered (Phase I)

Each KPI includes:

Business Question
Formula
Importance
Operational Meaning (High vs Low)
How to Improve

📙 COO's Dashboard (section wise) - COO's KPI Dictionary 🔍

✔ Revenue, Profit, CBM flows
✔ Workforce demographics
✔ Warehouse utilization
✔ All operational KPIs summarized

COO's UI

📗 Inbound/ Retunrs KPIs - Inbound/ Returns KPI Dictionary 🔍

✔ Labour efficiency
✔ Shift productivity (Inbound, Returns)
✔ Operational Cycle times (Picking, Loading, Return handling)
✔ On-time put-away %
✔ Rejection % analyses
✔ Supplier performance
✔ Return behaviors
✔ Incident reporting

Inbounds UI

Returns UI

📘 Outbound KPIs - Outbound KPI Dictionary 🔍

✔ Labour efficiency
✔ Shift productivity
✔ Order fulfillment %
✔ Operational cycle time
✔ WH Throughput (Cartons, CBM, Pallet)
✔ Failed-pick product analysis
✔ Lost GP due to failed-picks
✔ Vehicle utilization
✔ On-Time-Dispatch (OTD) %

Outbounds UI

🧭 Roadmap (Phase II)

Inventory Control analytics
Quality Control analytics
Transport/ Logistics analytics

🧭 Future Enhancements

Integrate Sales data model to perform a financial analysis
Predictive analysis with Machine Learnig models

👷 How to Run WIAP

📘 Shall we explore how to run the WIAP 🔍 ..?

# 1. Clone the repo
git clone https://github.com/<your-username>/wiap.git
cd wiap

# 2. Install dependencies
pip install -r requirements.txt

# 3. Start PostgreSQL (Docker)
docker-compose up -d

# 4. Generate synthetic datasets
python data_generation/data_generator.py

# 5. Load data into the DW
python etl/data_loader.py

# 6. Open Power BI Desktop and proceed with your own analysis and visualization
.pbix file is not inluded

📝 Commit Message Convention

📘 Want to commit 🔍 ..?

feat: added supplier rejection logic  
fix: corrected on-time putaway calculation  
docs: updated KPI dictionary  
refactor: optimized SQL view joins  
test: added loader unit tests  
chore: updated requirements.txt

🔧 How to Contribute

📘 Want to explore how you can contribute 🔍 ..?

1. Fork the repo
2. Create a feature branch
3. Follow commit conventions
4. Ensure tests pass
5. Submit PR with:
  * What changed
  * Why it was needed
  * Any dependencies
  * Screenshots (if Power BI)

🧪 Testing Strategy

📘 Would you like test it 🔍 ..?

✔ Data Gen Tests

Column issues
Null handling
Pattern consistency
Business rule checks

✔ ETL Tests

PK/FK constraints
UPSERT validation
Row counts
Error handling

✔ SQL View Tests

Data cleaning logic
COALESCE strategy
Cycle time calculations
SLA logic correctness

🏁 Final Thoughts

WIAP isn’t a toy project. It’s a full-fledged warehouse intelligence platform demonstrating,

Data engineering abilities
Analytics engineering discipline
Business logic modeling
Dashboard design
KPI governance
Operational domain knowledge

🙏 Acknowledgments & Gratitude

My sincere thanks to the communities and resources that supported this learning journey:

eLearning.lk: I was fortunate to find this online edu. platform at the start of my learning path. Special thanks to Mr. Sanjaya Elvitigala, the platform owner, and Mr. Asanka Senarath, my first Power BI mentor.
YouTube Communities: For exploring best practices in KPI representation and drawing inspiration for user interface design.
AI Assistants (Grok, ChatGPT, DeepSeek): For researching concepts, validating ideas, developing KPI/SLA frameworks, and assisting with debugging and code optimization.

👨‍💻 Author

Thilina Perera | Data with TP

📌 Data Science/ Data Analytics D-Technosavant
📌 Machine Learning, Deep Learning, LLM/LMM, NLP, and Automated Data Pipelines Inquisitive

🏆 License

This project is licensed under the MIT License.
Free to use and extend.

Name		Name	Last commit message	Last commit date
Latest commit History 7 Commits
data		data
kpi_doc		kpi_doc
sql		sql
src		src
.gitignore		.gitignore
Data_Analytics_Projects_WIAP_Warehouse_Intelligence_&_Analytics_Pipeline.code-workspace		Data_Analytics_Projects_WIAP_Warehouse_Intelligence_&_Analytics_Pipeline.code-workspace
LICENSE.md		LICENSE.md
Readme.md		Readme.md
coo_dashboard.png		coo_dashboard.png
cover_image.png		cover_image.png
data_pipeline.png		data_pipeline.png
github_projects.png		github_projects.png
inbound_dashboard.png		inbound_dashboard.png
outbound_dashboard.png		outbound_dashboard.png
requirements.txt		requirements.txt
retunrs_dashboard.png		retunrs_dashboard.png
wh_data_model.png		wh_data_model.png
wh_process.png		wh_process.png

License

ThilinaPerera-DataAnalytics/Data_Analytics_Projects_WIAP_Warehouse_Intelligence_-_Analytics_Pipeline

Folders and files

Latest commit

History

Repository files navigation