This project aims to develop a data warehouse for Dominick’s Finer Foods (DFF), a prominent grocery store chain, leveraging historical data collected between 1989 and 1994. The objective is to enable insightful analysis of store-level data to optimize inventory management, marketing strategies, and profitability. The initiative addresses key business challenges using a scalable, dimensional data warehouse design based on Kimball’s methodology.
- Centralized Data Repository: Consolidate data from multiple formats (CSV, SAS) into a unified warehouse.
- Business Insights: Answer critical business questions, including:
- Store performance and customer traffic analysis.
- Product profitability trends across stores and seasons.
- Impact of promotions and holiday sales patterns.
The project uses a rich dataset containing:
- Sales and Pricing: Weekly data at the product and store levels.
- Customer Traffic: Daily in-store footfall and coupon usage.
- Demographics: Census data linked to stores for insights into consumer behavior.
The warehouse employs a star schema structure, featuring:
- DimStore: Contains store attributes like location, price tier, and zone.
- DimProduct: Includes product details such as UPC and category codes.
- DimDate: Supports time-series analysis with granular time attributes (e.g., day, week, holiday).
- FactSales: Tracks sales transactions, linking dimensions for holistic analysis.
- ETL Pipeline: Utilizes SQL Server Integration Services (SSIS) for data extraction, cleaning, and transformation.
- Data Standardization: Ensures consistency in data types and naming conventions.
- Indexing and Aggregations: Optimizes query performance through indexed views and pre-computed summaries.
- Scalability: Modular data marts facilitate incremental expansion while ensuring data integrity.
The data warehouse empowers DFF to:
- Understand customer behavior and optimize store layouts.
- Tailor pricing and promotions to maximize profitability.
- Enhance decision-making through detailed, time-based insights.
This comprehensive solution not only addresses existing challenges but also positions DFF for sustainable growth in a competitive retail market.