Retail Data Warehouse Design for Dominick’s Fine Food

Created on Feb 28, 2025

Note: If you want to see a copy of the report

Skills:

• Data Warehousing & ETL: Kimball’s Dimensional Modeling, SSIS, ETL pipelines
• Database Design: Entity Relationship Diagrams (ERD), OLTP to OLAP transformations
• Data Cleaning & Integration: Handling missing values, duplicate records, inconsistent data
• Business Intelligence & Reporting: SSRS, SSAS cubes, Power BI, Tableau
• SQL & Data Manipulation: Data extraction, transformation, and loading (ETL)

Project Overview:

The objective of this project was to design and develop a data warehouse for Dominick’s Fine Food (DFF), a retail store chain in Chicago. The warehouse was built to consolidate store-level data across 100 stores, covering customer traffic, product movement, pricing, and demographics from historical datasets collected over nine years. The goal was to provide actionable insights into store profitability, customer footfall trends, product performance, and seasonal sales through a robust BI reporting system.

Key Contributions:

• Data Warehouse Design:Modeled the warehouse using Kimball’s methodology, creating an independent data mart to answer 5 key business questions. Designed logical and physical schemas, fact, and dimension tables, and implemented an OLTP to OLAP transformation.
• ETL Development:Built ETL pipelines using SSIS to extract, clean, and load data. Addressed data quality issues like missing values, incorrect types, and duplicates. Developed transformation rules for aggregations and historical tracking.
• Data Integration & Staging:Mapped source files to staging tables and then to the target warehouse. Organized the staging area, optimized data loading procedures, and validated data integrity at each step.
• Business Intelligence Reporting:Designed and implemented reports to answer critical business questions. Built SSRS reports for store profitability, customer footfall trends, product performance, and seasonal sales. Created SSAS cubes for multidimensional analysis, enabling drill-down and roll-up capabilities.
• Performance OptimizationTuned ETL processes for efficiency, designed aggregate tables for faster queries, and optimized indexing strategies to handle large volumes of historical data.

Results and Impact:

• Enabled data-driven decision-making through comprehensive BI reports, helping DFF optimize pricing strategies, identify best-selling products, and adjust inventory based on customer traffic.
• Improved data accuracy and consistency by implementing rigorous data cleaning and validation processes.

Learnings and Takeaways:

• Gained deep experience with end-to-end data warehouse development, from schema design to ETL and reporting.
• Learned how to handle real-world data challenges, including large file sizes, dirty data, and complex relationships.
• Understood the importance of aligning data models with business needs to ensure reports deliver valuable insights.