Skip to content

Latest commit

 

History

History
51 lines (37 loc) · 2.48 KB

README.md

File metadata and controls

51 lines (37 loc) · 2.48 KB

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.