Academic work from Texas A&M University — implementation not publicly shareable without explicit professor approval. Get in touch to request access.
Impact
Built an end-to-end data warehouse solution that reduced data processing time by 63% (from 5.5 to 2.0 hours) and enabled analysis of $600M+ retail sales patterns, leading to optimized inventory management and promotion strategies.
Problem Statement
Dominick's Fine Foods — a retail business — faced challenges in analyzing their sales patterns, customer behavior, and product performance due to:
- Scattered data across multiple sources
- Manual data compilation taking 5+ hours monthly
- Inability to track historical changes in product categories
- Lack of unified view for sales and customer behavior analysis
- Complex requirements for analyzing specific product combinations
Approach
- Design two specialized data marts:
- Sales Data Mart for product performance analysis
- Store Information Data Mart for customer behavior tracking
- Implement robust ETL processes:
- Type-2 SCD (Slowly Changing Dimensions) for tracking dimensional changes
- Automated data validation and transformation
- Error handling and logging mechanisms
- Develop multi-tool visualization strategy:
- SSRS for static holiday season reports
- SSAS cubes for historical trend analysis
- Tableau for interactive dashboards
Methodology
- Data Mart Implementation:
- Created star schema designs for both marts
- Implemented fact tables (factSales, factStoreInfo)
- Developed shared dimensions (dimTime, dimStore)
- Established proper relationships and constraints
- ETL Development:
- Built SSIS packages for data transformation
- Implemented lookup transformations
- Created derived calculations
- Set up incremental loading
- BI Solution Development:
- Developed SSAS cubes with calculated measures
- Created named queries for optimization
- Designed specialized visualizations for each business question
- Implemented dual-axis charts for correlation analysis
Result
The solution successfully:
- Reduced monthly data processing time from 5.5 to 2.0 hours
- Enabled analysis of 6 years of historical data
- Provided automated tracking of product category changes
- Delivered insights through 5 specialized dashboards
- Created a scalable foundation for future analytics needs