European Football Leagues - Data Warehouse & ETL Project

Data Warehouse & ETL Solution for European Football Analytics

Built using SQL Server, SSIS, and Power BI

---

Overview

This project presents a complete Data Warehouse & ETL solution for analyzing European football leagues across multiple seasons.

The system transforms operational football data into a centralized analytical warehouse optimized for:

• Team performance analysis
• League analytics
• Coach and stadium insights
• Match efficiency tracking
• KPI reporting and dashboards

The project follows a modern BI architecture consisting of:

OLTP Database → STG Layer → Data Warehouse → Power BI

---

Architecture

Source System (OLTP)

The source database stores operational football data including:

• Teams
• Matches
• Leagues
• Coaches
• Stadiums
• Scores
• Standings

OLTP ERD

---

Staging Layer (STG)

The staging area is responsible for:

• Data extraction
• Cleaning and transformation
• Incremental loading
• KPI preparation
• Data standardization

---

Data Warehouse (DWH)

The warehouse follows a Star Schema design with:

Dimension Tables

• Dim_Date
• Dim_Team
• Dim_League
• Dim_Coach
• Dim_Stadium
• Dim_Scoring_Junk

Fact Tables

• Fact_Team_Monthly_Stat
• Fact_Scoring_Efficiency
• Fact_Coach_Stadium_Performance

Schema Design

---

ETL Process

The ETL pipeline was implemented using SQL Server Integration Services (SSIS).

Workflow

Extract → Stage → Transform → Load Dimensions → Load Facts

Features

• Incremental Loading
• SCD Type 2 Support
• Surrogate Key Generation
• Lookup Transformations
• Data Validation & Cleansing
• SSIS Logging & Error Handling

---

Key Analytics & KPIs

The warehouse supports multiple analytical use cases including:

• Team win rate analysis
• Goals scored vs conceded
• League competitiveness
• Home advantage analysis
• Coach performance metrics
• Stadium impact analysis
• First-half vs second-half performance
• Monthly trend analysis

---

Power BI Dashboard

An interactive Power BI Dashboard was developed to visualize:

• Team performance
• League statistics
• Match trends
• Coach effectiveness
• Scoring efficiency
• Stadium analytics

Dashboard Screenshots

League & Team Performance

Screenshot 1	Screenshot 2

Match & Scoring Analysis

Screenshot 1	Screenshot 2

---

Technologies Used

Technology	Purpose
SQL Server	Database Engine
SSIS	ETL Development
Power BI	Data Visualization
T-SQL	Data Processing
Star Schema	Dimensional Modeling

---

Team Members

Name	GitHub	LinkedIn
Mohammed Atef Abd El Kader	Mohammed-3tef	Mohammed Atef
Mohammed Ahmed Mohammed	mohamedahmed2005	Mohammed Ahmed
Mostafa Ehab Mostafa Akl	Eng-M0stafaEhab	Mostafa Ehab
Mostafa Mahmoud Fathy	mostafa-mahmoud-fathy	Mostafa Mahmoud

---

Academic Information

• Faculty: Faculty of Computers and Artificial Intelligence
• University: Cairo University
• Course: IS341 - Data Warehousing
• Supervisor: Dr. Ali Zidan

---

Conclusion

This project demonstrates the implementation of a scalable and analytical football data warehouse using modern Business Intelligence concepts and tools.

The solution integrates ETL processing, dimensional modeling, KPI generation, and interactive reporting into a complete end-to-end BI system.