End-to-End Business Case Study

This case study brings together all Power BI concepts covered in the course — from data connection to deployment — in a realistic business scenario.

Scenario: Contoso Retail Analytics Platform

Company: Contoso Retail — a multi-channel retailer with 50 stores, an e-commerce platform, and 500+ employees.

Challenge: Leadership lacks visibility into cross-channel performance, inventory efficiency, and customer behavior. They need a unified analytics platform.

Phase 1: Requirements Gathering

Phase 2: Data Architecture

Data Sources: • SQL Server — Transaction data (POS, e-commerce orders) • Excel files — Budget and targets • SharePoint — Employee data and HR surveys • Web API — Marketing campaign metrics • Azure Blob — Historical CSV archives

Data Model (Star Schema):

                    ┌── Products (Dimension)
                    │
Dates ── Sales ─────┼── Customers (Dimension)
  │      (Fact)     │
  │                 ├── Stores (Dimension)
  │                 │
  │                 └── SalesReps (Dimension)
  │
  ├── Inventory (Fact) ── Warehouses (Dimension)
  │
  ├── Budget (Fact) ── Departments (Dimension)
  │
  └── HRData (Fact) ── Employees (Dimension)

Phase 3: Data Preparation

Power Query Transformations: • Clean transaction data: remove nulls, fix data types, trim text • Merge customer table with loyalty program data • Append multiple CSV files from Azure Blob • Create calculated columns: fiscal year, customer segment • Generate a complete date table with fiscal periods

Phase 4: Data Modeling & DAX

Key Measures Created: • Revenue, COGS, Gross Profit, Net Profit • YoY Growth, MoM Growth • Customer Lifetime Value (CLV) • Inventory Turnover, Days of Stock • Employee Attrition Rate • Budget Variance

Time Intelligence: • YTD, QTD, MTD calculations • Same Period Last Year comparisons • Rolling 12-month averages

Phase 5: Report Development

Reports Created:

1. Executive Dashboard (1 page)
2. Sales Analytics (3 pages + drill-through)
3. Inventory Management (2 pages)
4. Financial Overview (2 pages)
5. HR Insights (2 pages)
6. Marketing & Customers (2 pages)

Design Standards: • Consistent color theme (brand colors) • Navigation bar on every page • Dynamic titles reflecting slicer selections • Tooltip pages for contextual detail • Bookmarks for view toggling

Phase 6: Security Implementation

RLS Roles:

OLS: Salary and cost columns hidden from non-finance roles

Phase 7: Deployment

Pipeline:

Development Workspace → Test Workspace → Production Workspace

Deployment rules: Different database connections per stage Scheduled refresh: Every 4 hours during business hours Gateway: On-premises gateway for SQL Server data

Phase 8: Governance

• Sensitivity labels applied: Confidential for HR and Finance, General for Sales • Certification applied to production reports • Audit logging enabled • Monthly review of access permissions • Documentation: Data dictionary, DAX measure catalog, RLS design document • Training: Conducted user training for all stakeholder groups

Lessons Learned

Skills Applied

Every concept from Modules 1-14: data connection, Power Query, data modeling, DAX (basic + advanced), time intelligence, visualizations, drill-through, slicers, bookmarks, RLS/OLS, Power BI Service, deployment pipelines, governance, and collaboration