Project Overview: Design and Implementation of a Data Warehouse for a Retail Store

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.

---

Objectives

1. Centralized Data Repository: Consolidate data from multiple formats (CSV, SAS) into a unified warehouse.
2. 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.

---

Dataset Details

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.

---

Data Warehouse Design

The warehouse employs a star schema structure, featuring:

1. Dimension Tables

• 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).

2. Fact Tables

• FactSales: Tracks sales transactions, linking dimensions for holistic analysis.

---

Key Features and Implementation

• 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.

---

Business Value

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.