This project demonstrates the complete lifecycle of a data warehouse system built from an OLTP database source. Our goal was to design a dimensional model based on KPIs and implement an automated ETL process using SQL Server Integration Services (SSIS).
- 4 students
- Identified business KPIs from a real-world OLTP system (custom schema, not AdventureWorks or Northwind).
- Designed a star schema with:
- ✅ 6+ Dimensions
- ✅ 3 Fact Tables
- Created the physical model for both OLTP and DW schemas.
- Used SSIS to perform:
- ETL from OLTP → Staging → Data Warehouse
- Data cleaning and transformation
- Deployment and scheduling of packages
- Developed T-SQL queries on each fact table to extract actionable insights.
- Included screenshots of query results for visualization.
- SQL Server (OLTP & DWH)
- SSIS (ETL)
- Visual Studio
- T-SQL
- Built a scalable data warehouse with scheduled ETL jobs
- Delivered business insights via analytical queries