GlobalTech Electronics

🏢 Business Context

Company Overview

GlobalTech Electronics is a multinational retailer specializing in consumer electronics with operations across North America, Europe, Asia, and the Middle East. The company operates both physical stores and online channels, selling products from major brands including Apple, Samsung, Sony, Dell, Canon, LG, Bose, and HP.

Current Situation

The company's data is scattered across multiple systems:

• Product catalog maintained in the inventory management system (10 active products)
• Customer information stored in the CRM database (10 customers across 5 continents)
• Sales transactions captured in the point-of-sale (POS) system (15 historical orders)
• Store information managed separately by regional offices (5 flagship stores globally)
• Incremental sales data arriving daily from POS systems (5 new orders per day on average)

The business intelligence team needs to consolidate this data to provide:

• Real-time sales analytics
• Customer segmentation and behavior analysis
• Product performance tracking
• Trend analysis and forecasting
• Historical change tracking

🎯 The Challenge

The Problem Statement

The Head of Analytics, Sarah Martinez, approaches your team with the following challenges:

"Our executive team needs a unified view of our business performance, but our data is fragmented across different systems. We're receiving new sales data daily, customer segments are changing, and we need to track these changes historically. We're losing insights because our current process takes days to consolidate data manually. Can you build a data warehouse solution that gives us accurate, timely insights?"

Specific Pain Points

1. Data Fragmentation: Sales data exists in one system, customer data in another, product information in a third
2. No Historical Tracking: When a customer moves from "Consumer" to "Corporate" segment, the history is lost
3. Manual Reporting: Analysts spend hours manually combining Excel files from different departments
4. Delayed Insights: By the time reports are ready, the data is already outdated
5. Inconsistent Metrics: Different departments calculate revenue, profit, and discounts differently
6. Growing Data Volume: Daily transaction data keeps accumulating, making full reloads impractical
7. Missing Time Intelligence: No easy way to analyze trends by month, quarter, or fiscal year

📊 Business Requirements

Business Ask #1: Unified Sales Reporting

Stakeholder: Chief Revenue Officer (CRO)

The Ask: "I need a single source of truth for all sales metrics. Right now, finance reports different revenue numbers than sales operations, and nobody can agree on profit margins. Give me one dashboard where everyone sees the same numbers."

What They Need:

• Total Gross Revenue (Quantity × List Price)
• Net Revenue (after discounts)
• Gross Profit (Revenue - Cost)
• Total Cost of Goods Sold
• Discount amounts and percentages
• Shipping costs included in total transaction value

Business Impact:

• Monthly board meetings delayed because finance and sales argue over numbers
• Strategic decisions postponed due to data inconsistency
• Lost credibility with investors who see conflicting reports

Success Criteria:

• Single calculation logic for all revenue metrics
• Finance, Sales, and Operations see identical numbers
• Calculations documented and transparent

Business Ask #2: Customer Segmentation & Loyalty Tracking

Stakeholder: VP of Marketing

The Ask: "We classify customers as Corporate, Consumer, or Home Office. We also have loyalty tiers (Gold, Silver, Bronze). But when a customer changes segments, we lose the history. I need to see both current status AND how customers have changed over time."

What They Need:

• Current customer segment (Corporate, Consumer, Home Office)
• Current loyalty tier (Gold for 2+ years, Silver for 1+ year, Bronze for new)
• Historical tracking: When did customer C001 change from Consumer to Corporate?
• Customer tenure calculation (years since registration)
• Market classification (Developed vs Emerging markets)

Business Impact:

• $500K annual marketing budget targeting specific segments
• Cannot measure if customers are moving up the loyalty ladder
• Losing ability to analyze "what changed" when campaigns succeed or fail

Success Criteria:

• See current AND historical customer status
• Track segment changes with effective dates
• Analyze campaign effectiveness before/after segment changes

Business Ask #3: Product Performance & Profitability

Stakeholder: Head of Merchandising

The Ask: "I need to know which products are actually profitable. We have list prices and cost data, but I want to see profit margins by product, identify our premium vs budget items, and understand which categories drive the most profit."

What They Need:

• Profit per unit (List Price - Unit Cost)
• Profit margin percentage
• Product classification by price tier:
  • Premium (≥ $2,000)
  • Mid-Range ($1,000 - $1,999)
  • Standard ($500 - $999)
  • Budget (< $500)
• Active vs Discontinued product status
• Category and brand performance

Business Impact:

• Stocking decisions based on gut feel, not data
• Premium products might be subsidizing low-margin items
• No visibility into brand performance

Success Criteria:

• Clear profitability metrics for each product
• Easy identification of high-margin vs low-margin products
• Category-level profitability analysis

Business Ask #4: Discount Analysis & Pricing Strategy

Stakeholder: Director of Sales Operations

The Ask: "We're giving discounts on many orders, but I don't know if they're helping or hurting us. I need to understand discount patterns, their impact on profitability, and identify which orders are high-value vs standard."

What They Need:

• Discount categorization:
  • Heavy Discount (> 10%)
  • Light Discount (0-10%)
  • No Discount
• Discount amount in dollars
• Order value classification (High Value ≥ $1,000 vs Standard)
• Impact analysis: How do discounts affect profit margins?

Business Impact:

• Sales reps offering discounts without understanding margin impact
• Potential revenue leakage from unnecessary discounts
• No clear pricing strategy across product lines

Success Criteria:

• Clear visibility into discount patterns
• Ability to identify optimal discount levels
• Data-driven pricing recommendations

Business Ask #5: Incremental Data Processing

Stakeholder: Head of Business Intelligence

The Ask: "We're getting new sales data every day. Right now, we reload everything, which takes hours. We need an incremental load process that only processes new data, so our dashboards are always current."

What They Need:

• Daily incremental load of new sales transactions
• ProcessedFlag to track which records have been loaded
• Ability to append new data without full reload
• Consistent transformations applied to both historical and new data

Business Impact:

• Current full reload takes 4 hours, blocking morning dashboards
• Data as of previous day, missing today's sales
• Growing data volume making full reloads unsustainable

Success Criteria:

• Incremental load completes in under 15 minutes
• New data available in dashboards by 8 AM daily
• Same calculations apply to historical and incremental data

Business Ask #6: Time-Based Analysis

Stakeholder: Senior Data Analyst

The Ask: "I need to analyze trends by day, week, month, quarter, and fiscal year. Right now, I'm manually creating date hierarchies in every report. Give me a master calendar that makes time-based analysis easy."

What They Need:

• Calendar table with all dates from min to max order date
• Date attributes: Year, Quarter, Month, Week, Day of Week
• Fiscal year calculation (starts April 1)
• Business day flags (Monday-Friday)
• Current period indicators (IsToday, IsCurrentMonth, IsCurrentYear)
• Link to actual sales data (HasSales flag)

Business Impact:

• Analysts spending 2-3 hours per report on date logic
• Inconsistent date hierarchies across reports
• Difficulty comparing year-over-year or quarter-over-quarter

Success Criteria:

• Single calendar table used across all reports
• Drag-and-drop date hierarchies in visualizations
• Easy fiscal year analysis
• Consistent date logic everywhere

Business Ask #7: Geographic Performance Analysis

Stakeholder: VP of Operations

The Ask: "I need to understand performance by geography - which regions, countries, and cities are driving revenue? I also want to see our store locations and their performance."

What They Need:

• Geographic hierarchy: Region → Country → City
• Store dimension with location and type
• Market classification (Developed vs Emerging)
• Ability to drill down from region to city level

Business Impact:

• Expansion decisions lack geographic performance data
• Cannot identify underperforming markets
• No visibility into store-level trends

Success Criteria:

• Clear geographic hierarchy (Country → Region)
• Market classification for analysis
• Store-level detail available

🔧 Technical Requirements

Data Sources

You are provided with 5 source QVD files representing different operational systems:

1. DIM_Products.qvd (Product Master Data)

   • 10 products across multiple categories
   • Product attributes: Name, Category, Brand, Pricing, Cost
   • Status flags (Active/Discontinued)

2. DIM_Customers.qvd (Customer Master Data)

   • 10 customers across 5 continents
   • Geographic information: Country, Region, City
   • Segment classification
   • Registration dates

3. FACT_Sales.qvd (Historical Sales Transactions)

   • 15 completed orders
   • Transaction details: Quantity, Discount, Shipping
   • Order status tracking

4. DIM_Stores.qvd (Store Locations)

   • 5 flagship stores globally
   • Store types: Flagship, Premium, Standard
   • Opening dates and locations

5. FACT_Sales_Incremental.qvd (New Daily Sales)

   • 5 new orders (simulating daily arrivals)
   • Same structure as FACT_Sales
   • ProcessedFlag = 0 (unprocessed)

Technical Objectives

Data Integration:

• Extract data from all 5 QVD files
• Join dimensions (Products, Customers) with facts (Sales)
• Handle multiple LEFT JOIN operations properly

Data Transformation:

• Calculate revenue, profit, and margin metrics
• Classify products by price tier
• Categorize customers by loyalty level
• Flag discount levels and order values
• Add date intelligence fields

Data Loading Strategies:

• Implement incremental load for daily updates
• Track historical changes with SCD Type 2
• Generate master calendar dynamically
• Avoid synthetic keys in final data model

Data Quality:

• Ensure consistent field naming
• Prevent data duplication
• Maintain referential integrity
• Document all calculations

🎯 Learning Outcomes

By completing this scenario, participants will master:

Core ETL Concepts

• Extract, Transform, Load pipeline design
• Data integration from multiple sources
• Business logic implementation
• Data quality management

Qlik Scripting Skills

• Variable declaration and usage (SET vs LET)
• LOAD statements (inline, from files, resident)
• Data transformation techniques
• Join operations (LEFT JOIN, proper key selection)
• Concatenation (auto vs explicit, NoConcatenate)
• Conditional logic (IF-THEN-ELSE)
• Functions (date, string, mathematical)

Advanced Techniques

• Incremental loading strategies
• Slowly Changing Dimensions (SCD Type 2)
• Master calendar generation
• QVD optimization
• Synthetic key resolution
• Star schema design

Business Intelligence Skills

• Translating business requirements to technical specifications
• Metric calculation and definition
• Data modeling best practices
• Performance optimization
• Documentation and maintainability

Real-World Problem Solving

• Handling data from multiple systems
• Managing data refresh schedules
• Tracking historical changes
• Building scalable solutions
• Creating production-ready ETL processes

📈 Success Metrics

Your ETL solution will be considered successful when:

✅ Data Quality

• All source data successfully loaded
• Zero data duplication
• Consistent calculations across all uses
• No missing or null critical fields

✅ Performance

• Full initial load completes successfully
• Incremental load handles new data correctly
• QVD files created and optimized
• Data model loads without errors

✅ Business Requirements Met

• All 7 business asks addressed
• Metrics calculated correctly
• Historical tracking functional
• Time intelligence working

✅ Technical Excellence

• Zero synthetic keys in final model
• Clean star schema structure
• Proper documentation
• Maintainable code structure

✅ Stakeholder Satisfaction

• Sarah (Head of Analytics) can build dashboards
• CRO has unified revenue metrics
• Marketing can track customer changes
• Sales Ops can analyze discount impact
• BI team has fast incremental loads
• Analysts have master calendar
• Ops team sees geographic performance

🚀 Next Steps

After completing this scenario, you will have:

1. Working ETL Solution: Complete data pipeline from source to visualization
2. Technical Documentation: Detailed scripts with comments
3. Data Model: Production-ready star schema
4. QVD Library: Optimized files for quick reloads
5. Foundation Skills: Ready for advanced Qlik development

You'll be ready to:

• Build dashboards and visualizations
• Optimize existing ETL processes
• Design new data integration solutions
• Implement advanced analytics
• Train others on Qlik best practices

📚 Training Materials Structure

This scenario will be taught through:

1. This Document: Business context and requirements
2. Solution Guide: Detailed technical implementation (separate document)
3. Script Reference: Line-by-line code explanation (separate document)
4. Hands-On Labs: Guided exercises for each phase
5. Troubleshooting Guide: Common issues and solutions

✨ Remember

"Good ETL is invisible. Users should never think about data loading - they should just see accurate, timely insights."

Your goal is not just to move data - it's to transform raw data into business intelligence that drives decisions worth millions of dollars.

Let's build something amazing! 🚀