GlobalTech Electronics - Implementation Roadmap

1

Foundation Setup

Objective: Establish the basic ETL framework

What You'll Build:

Create folder structure for data files
Set up data connections in Qlik Sense
Generate source QVD files from inline data
Define variables for paths and business rules

Key Learnings:

Qlik Sense file structure and connections
Variable declaration (SET vs LET)
QVD file creation and storage
Organizing script sections with tabs

✓ Deliverable: 5 source QVD files ready for extraction

2

Data Extraction

Objective: Load raw data from source systems

What You'll Build:

Load Product dimension from QVD
Load Customer dimension from QVD
Load Store dimension from QVD
Load Sales fact table from QVD
Apply initial date formatting

Key Learnings:

LOAD statement syntax
Reading from QVD files (optimized load)
Date formatting functions
WHERE clause for filtering
RESIDENT loads

✓ Deliverable: Raw tables loaded into Qlik memory

3

Dimension Transformation

Objective: Apply business logic to master data

What You'll Build:

Product Transformation:

Calculate profit per unit
Calculate profit margin percentage
Classify products by price tier
Add active status flag

Customer Transformation:

Calculate customer tenure in years
Assign loyalty tiers (Gold/Silver/Bronze)
Classify market types (Developed/Emerging)
Preserve all demographic attributes

Key Learnings:

Calculated fields in LOAD statements
IF statements for conditional logic
Mathematical calculations (percentages, differences)
Date functions (Year, Today)
Business rule implementation

✓ Deliverable: Transformed dimension tables with business metrics

4

Data Integration (Joins)

Objective: Enrich sales data with dimension attributes

What You'll Build:

LEFT JOIN sales with products:

Add product name, category, brand, pricing
Include profit metrics and price tier

LEFT JOIN sales with customers:

Add customer name, segment, geography
Include loyalty tier and market type

Key Learnings:

LEFT JOIN syntax and behavior
Join key selection (ProductID, CustomerID)
Field name conflicts and resolution
Synthetic key problems
Qualifying field names to avoid conflicts

✓ Deliverable: Enriched sales fact table with all dimensions joined

5

Business Logic Application

Objective: Calculate all final metrics and classifications

What You'll Build:

Revenue Calculations:

Gross Revenue = Quantity × List Price
Net Revenue = Gross Revenue × (1 - Discount)

Cost & Profit Calculations:

Total Cost = Quantity × Unit Cost
Gross Profit = Net Revenue - Total Cost
Discount Amount = Gross Revenue × Discount

Business Classifications:

Order Value Category (High Value vs Standard)
Discount Category (Heavy/Light/None)
Completed order flags

Time Intelligence:

Extract Year, Month, Quarter from OrderDate
Calculate Day of Week
Flag current year orders

ETL Metadata:

Add ProcessedFlag = 1
Add ETL_LoadDate for audit trail

Key Learnings:

Complex calculated fields
Nested IF statements
Variable usage in expressions
Business rule documentation
Audit field creation

✓ Deliverable: Final fact table with all business metrics

6

Incremental Load Implementation

Objective: Handle daily data updates efficiently

What You'll Build:

Reload dimension tables from QVD
Load only new transactions (ProcessedFlag = 0)
Join new transactions with dimensions
Apply same transformations to new data
Concatenate new data with existing data
Update ProcessedFlag to mark as processed

Key Learnings:

Incremental vs Full load strategies
WHERE clause for filtering new records
CONCATENATE statement usage
NoConcatenate keyword to prevent auto-concatenation
Maintaining data consistency across loads

✓ Deliverable: Working incremental load process that adds new sales daily

7

Slowly Changing Dimension (SCD Type 2)

Objective: Track historical changes in customer attributes

What You'll Build:

Identify changes in customer Segment or LoyaltyTier
Create new version records for changed customers
Close old versions with end dates
Maintain version numbers
Track effective date ranges
Keep IsCurrent flag updated

Key Learnings:

SCD Type 2 concept and importance
Change detection logic
Effective date management
Version numbering
Historical reporting capabilities
IF-THEN-ELSE logic for handling first run vs subsequent runs

✓ Deliverable: Customer history table tracking all segment/loyalty changes

8

Master Calendar Creation

Objective: Generate comprehensive date dimension

What You'll Build:

Find min/max dates from sales data
Generate date range using AUTOGENERATE
Add calendar attributes:
- Year, Quarter, Month, Week, Day
- Quarter names (Q1, Q2, Q3, Q4)
- Month names (January, February, etc.)
- Week names
- Year-Quarter combinations
Calculate fiscal year (starting in April)
Flag business days (Monday-Friday)
Flag current day/month/year
Link calendar to sales via OrderDate

Key Learnings:

AUTOGENERATE for row generation
WHILE loop for date sequences
Date functions (Year, Month, Quarter, WeekDay)
Peek function to read variable values
Calendar table design patterns

✓ Deliverable: Master calendar with full time intelligence

9

Data Persistence & Storage

Objective: Save transformed data for reuse and performance

What You'll Build:

Store fact tables to QVD:

FACT_Sales_Transformed.qvd (for next incremental load)
FACT_Sales_Complete.qvd (for visualization)

Store dimension tables to QVD:

DIM_Products_Transformed.qvd
DIM_Customers_Transformed.qvd
DIM_Stores_Transformed.qvd

Store special tables:

DIM_Customers_SCD.qvd (history tracking)
MasterCalendar.qvd (date dimension)

Key Learnings:

STORE statement syntax
QVD file benefits (speed, compression)
DROP TABLE to clean up memory
Folder organization for transformed data
ETL layering (source → transformed → visualization)

✓ Deliverable: Complete set of optimized QVD files

10

Visualization Data Model

Objective: Create clean, optimized data model for dashboards

What You'll Build:

Reload transformed data from QVDs
Qualify dimension fields to prevent synthetic keys
Create star schema structure:
- Sales (Fact) - center of model
- Products (Dimension) - connected via ProductID
- Customers (Dimension) - connected via CustomerID
- Calendar (Dimension) - connected via OrderDate
- Stores (Dimension) - independent
- Customer_History (Standalone) - for SCD analysis

Key Learnings:

Star schema design principles
Synthetic key problems and solutions
Field naming strategies (prefixing)
Data model optimization
Relationship establishment

✓ Deliverable: Production-ready data model with 0 synthetic keys

🎉 Congratulations!

You've completed the Implementation Roadmap

Ready to build your Data Warehouse ETL solution!

🗺️ Implementation Roadmap

📋 Phases Overview

Foundation Setup

What You'll Build:

Data Extraction

What You'll Build:

Dimension Transformation

What You'll Build:

Product Transformation:

Customer Transformation:

Data Integration (Joins)

What You'll Build:

LEFT JOIN sales with products:

LEFT JOIN sales with customers:

Business Logic Application

What You'll Build:

Revenue Calculations:

Cost & Profit Calculations:

Business Classifications:

Time Intelligence:

ETL Metadata:

Incremental Load Implementation

What You'll Build:

Slowly Changing Dimension (SCD Type 2)

What You'll Build:

Master Calendar Creation

What You'll Build:

Data Persistence & Storage

What You'll Build:

Store fact tables to QVD:

Store dimension tables to QVD:

Store special tables:

Visualization Data Model

What You'll Build:

🎉 Congratulations!