OLAP Cube Deployment & Business Insights for WWI and Valeur 

Overview

This project focused on designing and deploying an OLAP cube using SQL Server and SSAS for Wide World Importers (WWI) and proposed enhancements for Valeur’s data warehouse. The objective was to deliver a robust dimensional model for business analysis, enhance reporting, and integrate customer demographics and transactional behavior to provide actionable insights for executive decision-making. 

Tools and Technologies

Data Modeling: Microsoft SQL Server, SQL Server Data Tools (SSDT)

OLAP Cube Deployment: SQL Server Analysis Services (SSAS)

Query Language: MDX (Multidimensional Expressions), SQL

Reporting & Visualization: Power BI, SQL Server Reporting Services (SSRS), Excel Power Pivot

ETL & Data Integration: SQL Server Integration Services (SSIS)

Design & Hierarchies: Dimension Hierarchies (City, Date, Customer), SCD Type 2

Map Visualizations: Power BI Drill-down Geo Maps

Data Analysis: KPI Modeling, Time-Series Trends, Pricing Distribution

Part 1: Dimensional Model Redesign for Valeur 

To meet the CIO’s expectations and future-proof Valeur’s data architecture, I proposed the addition of new dimensions, attributes, and measures:

• New Dimensions:

  • DimGeography: Includes state, city, postcode for regional analysis

  • DimLoyalty: Stores card ID, join date, points earned/spent, tiering

  • DimPromo: Captures promotion type and date range

• New Attributes in DimCustomer:
  Added Income, MaritalStatus, Employment, HouseholdSize, and Channel for better segmentation.

• New Measures:

  • DiscountAmt

  • LoyaltyPoints

  • PromotionFlag

• Design Enhancements:

  • Applied SCD Type 2 to track changes in dynamic attributes like income and marital status

  • Introduced Geographic and Date Hierarchies for drill-down analysis

  • Suggested Sentiment Analysis features for feedback interpretation using polarity scores and source tracking

Part 2: OLAP Cube Development and Deployment 

• Designed a multidimensional cube in SQL Server Analysis Services (SSAS) for WWI

• Created custom hierarchies in DimCity, DimCustomer, and DimDate to enable drill-down capabilities

• Processed and deployed the cube, ensuring accurate dimension and fact table integration

Insights:

• High-Profit Cities: Absecon, NJ and Airport Drive, PR showed exceptional performance

• Geographic Trends: Alabama showed consistent sales; recommended focused expansion and inventory enhancement

• Quantity Trend: Sales volume declined after 2015, suggesting external economic or competitor impact

Part 3: MDX Expressions & KPI Development 

• Created a calculated measure using MDX:
  Total Sale = Unit Price * Quantity

• Developed a KPI to evaluate product pricing:

  • Unit Price ≥ $10.00: Good performance

  • $7.00–$9.99: Moderate performance

  • < $7.00: Poor performance

This metric helped assess pricing strategy effectiveness and guided product-level analysis.

Part 4: SSRS Reporting 

Developed comprehensive reports using SQL Server Reporting Services (SSRS):

• Compiled sales data by customer and region

• Designed for stakeholder accessibility and real-time business decisions

Part 5: Visual Analytics Using Power BI  

Created interactive map visualizations:

• Texas and California showed the highest profits (larger bubbles)

• City-level drill-downs revealed Dallas and Denver as top performers

Identified a downward trend in profits for Tailslip Toys and Wingtip Toys from 2015 onward, despite earlier growth in ordered quantity

Highlighted seasonality effects, with peaks in May and July — suggested time-series modeling and seasonal campaigns

Part 6: Integration Strategy  Valeur & WWI Warehouses 

Developed a roadmap for data warehouse integration, focusing on:

• Conformed Dimensions: Standardizing attributes like DimCustomer across systems

• SCD Type 2: Maintaining history for changing attributes

• Fact Table Granularity: Aligning transactional data by daily aggregation

• ETL Pipeline: Proposed use of SSIS for extract, transform, and load operations

• Data Security & Governance: Role-based access, encryption for PII

• Cloud Scalability: Suggested batch processing using Azure or AWS

Part 7: Final Deliverables 

• OLAP Cube with multidimensional hierarchies
  
  

• KPI and MDX metric implementation
  
  

• Power BI and SSRS visual dashboards
  
  

• Integration strategy between two enterprise data warehouses
  
  

• Recommendations for seasonal trends, product pricing, and loyalty engagement