Case Study

JTI Product Quality Analytics Data Warehouse

A comprehensive data warehousing solution for tobacco product quality assurance using Kimball dimensional modeling methodology

Executive Summary

Japan Tobacco International (JTI) requires a robust data warehouse to track, analyze, and ensure product quality across their global manufacturing operations. This case study demonstrates the application of dimensional modeling principles to create a comprehensive QA analytics platform that enables real-time quality monitoring, defect tracking, compliance reporting, and predictive quality insights.

15+
Manufacturing Sites Globally
500M+
Quality Test Records/Year
99.8%
Target Quality Score

Business Requirements

Quality Monitoring Needs
  • •Real-time visibility into quality metrics across all manufacturing facilities
  • •Track defects by product, batch, machine, operator, and time period
  • •Monitor compliance with regulatory standards (ISO, FDA, local regulations)
  • •Identify quality trends and patterns for proactive intervention
  • •Root cause analysis for quality issues and defects
Analytical Requirements
  • •Compare quality performance across different manufacturing sites
  • •Analyze quality by product line, brand, and SKU
  • •Correlate raw material quality with finished product quality
  • •Measure OEE (Overall Equipment Effectiveness) impact on quality
  • •Predictive analytics for quality degradation and maintenance scheduling

Kimball Bus Matrix

JTI Quality Analytics Enterprise Data Warehouse Bus Matrix

Mapping business processes to conformed dimensions

Business ProcessDateProductManufacturing SiteProduction LineBatchTest TypeInspectorMachineRaw MaterialSupplierDefect Type
Quality Testing
Defect Tracking
Production Monitoring
Raw Material Testing
Compliance Audits
Machine Performance

Bus Matrix Key:

Dots indicate which conformed dimensions are used by each business process fact table. This ensures dimensional consistency across the enterprise, enabling drill-across analysis between different business processes.

Star Schema Design

Core Quality Testing Star Schema
Fact_Quality_Test
Foreign Keys:
date_key (FK)
product_key (FK)
site_key (FK)
line_key (FK)
batch_key (FK)
test_type_key (FK)
inspector_key (FK)
Measures:
test_result_value
pass_fail_indicator
deviation_from_standard
test_duration_minutes
sample_size
temperature_celsius
humidity_percent
Dim_Date
date_key (PK)
full_date
day, month, year
quarter, week
day_of_week
is_weekend
fiscal_period
Dim_Product
product_key (PK)
product_code
product_name
brand
product_line
category
tar_content
nicotine_content
pack_size
Dim_Manufacturing_Site
site_key (PK)
site_code
site_name
country
region
site_capacity
certification_level
Dim_Production_Line
line_key (PK)
line_code
line_name
line_type
capacity_per_hour
installation_date
site_key (FK)
Dim_Batch
batch_key (PK)
batch_number
batch_size
production_date
shift
ambient_conditions
Dim_Test_Type
test_type_key (PK)
test_code
test_name
test_category
standard_value
tolerance_range
test_frequency
Dim_Inspector
inspector_key (PK)
employee_id
inspector_name
certification_level
experience_years
site_key (FK)

Additional Fact Tables

Fact_Defect

Transaction fact

Grain:

One row per detected defect

Foreign Keys:
• date_key, product_key, site_key, line_key, batch_key
• defect_type_key, inspector_key, machine_key
Measures:
• defect_count
• severity_score
• scrap_quantity
• rework_time_minutes
• cost_impact
Fact_Production_Daily

Periodic snapshot fact

Grain:

One row per product per line per day

Foreign Keys:
• date_key, product_key, site_key, line_key
Measures:
• units_produced
• units_tested
• units_passed
• units_failed
• quality_rate_percent
• oee_percent
Fact_Raw_Material_Test

Transaction fact

Grain:

One row per raw material test

Foreign Keys:
• date_key, site_key, batch_key
• raw_material_key, supplier_key, test_type_key, inspector_key
Measures:
• moisture_content
• nicotine_level
• sugar_content
• ph_level
• pass_fail_indicator
Fact_Compliance_Audit

Transaction fact

Grain:

One row per audit finding

Foreign Keys:
• date_key, product_key, site_key, line_key
• test_type_key, inspector_key, regulation_key
Measures:
• compliance_score
• findings_count
• critical_findings
• corrective_actions

Data Pipeline Architecture

ELT Pipeline Design

Source Systems

  • • MES (Manufacturing Execution System)
  • • LIMS (Laboratory Information Management)
  • • QMS (Quality Management System)
  • • ERP (SAP)
  • • SCADA (Machine sensors)
  • • Document Management System

Staging Layer

  • • Raw data ingestion (Fivetran/Airbyte)
  • • Change data capture (CDC)
  • • Data validation
  • • Deduplication
  • • Audit logging

Transformation Layer

  • • Dimension table SCD Type 2
  • • Fact table aggregations
  • • Business rule application
  • • Data quality checks (dbt tests)
  • • Surrogate key generation

Technology Stack:

Ingestion:
Fivetran, Airbyte
Storage:
Snowflake
Transformation:
dbt Cloud
Orchestration:
Airflow

Data Refresh Strategy:

  • • Quality tests: Real-time streaming (5-minute micro-batches)
  • • Defect tracking: Near real-time (15-minute incremental loads)
  • • Production metrics: Hourly aggregations
  • • Dimensions: Daily full refresh with SCD Type 2
  • • Raw material tests: Event-driven on receipt

Key Performance Indicators

Quality Metrics
  • First Pass Yield (FPY)Target: 98%
  • Defect Rate (PPM)Target: <100
  • Test Pass RateTarget: 99.5%
  • Scrap RateTarget: <2%
  • Customer ComplaintsTarget: <10/mo
Process Metrics
  • OEE (Overall Equipment Effectiveness)Target: 85%
  • Downtime due to QualityTarget: <3%
  • Rework TimeTarget: <5%
  • Batch Acceptance RateTarget: 99%
  • Test Turnaround TimeTarget: <2hr
Compliance Metrics
  • Compliance ScoreTarget: 100%
  • Audit FindingsTarget: 0 critical
  • Documentation AccuracyTarget: 99.9%
  • Regulatory ViolationsTarget: 0
  • Corrective Action TimeTarget: <48hr

Sample Analytical Queries

1. Quality Performance by Site and Product Line
SELECT 
  s.site_name,
  s.country,
  p.product_line,
  COUNT(*) as total_tests,
  SUM(CASE WHEN f.pass_fail_indicator = 1 THEN 1 ELSE 0 END) as passed_tests,
  ROUND(100.0 * SUM(f.pass_fail_indicator) / COUNT(*), 2) as pass_rate_percent,
  AVG(f.deviation_from_standard) as avg_deviation
FROM Fact_Quality_Test f
JOIN Dim_Manufacturing_Site s ON f.site_key = s.site_key
JOIN Dim_Product p ON f.product_key = p.product_key
JOIN Dim_Date d ON f.date_key = d.date_key
WHERE d.year = 2024 AND d.quarter = 4
GROUP BY s.site_name, s.country, p.product_line
HAVING COUNT(*) > 100
ORDER BY pass_rate_percent DESC
2. Defect Root Cause Analysis
SELECT 
  dt.defect_category,
  dt.defect_name,
  m.machine_type,
  l.line_name,
  COUNT(*) as defect_count,
  SUM(d.scrap_quantity) as total_scrap,
  SUM(d.cost_impact) as total_cost,
  AVG(d.severity_score) as avg_severity
FROM Fact_Defect d
JOIN Dim_Defect_Type dt ON d.defect_type_key = dt.defect_type_key
JOIN Dim_Machine m ON d.machine_key = m.machine_key
JOIN Dim_Production_Line l ON d.line_key = l.line_key
JOIN Dim_Date date ON d.date_key = date.date_key
WHERE date.full_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY dt.defect_category, dt.defect_name, m.machine_type, l.line_name
ORDER BY defect_count DESC
LIMIT 20
3. Raw Material Quality Impact on Finished Product
SELECT 
  rm.material_name,
  sup.supplier_name,
  AVG(rmt.moisture_content) as avg_moisture,
  AVG(rmt.nicotine_level) as avg_nicotine,
  AVG(pq.quality_rate_percent) as finished_product_quality
FROM Fact_Raw_Material_Test rmt
JOIN Dim_Raw_Material rm ON rmt.raw_material_key = rm.raw_material_key
JOIN Dim_Supplier sup ON rmt.supplier_key = sup.supplier_key
JOIN Dim_Batch b ON rmt.batch_key = b.batch_key
JOIN Fact_Production_Daily pq ON b.batch_key = pq.batch_key
WHERE rmt.pass_fail_indicator = 1
GROUP BY rm.material_name, sup.supplier_name
HAVING COUNT(*) > 50
ORDER BY finished_product_quality DESC
Business Value Delivered

Operational Benefits:

  • •15% reduction in quality-related downtime through predictive quality alerts
  • •25% faster root cause analysis with integrated dimensional views
  • •30% reduction in scrap rates through early defect detection
  • •Real-time visibility across 15 global facilities

Strategic Benefits:

  • •100% compliance with regulatory audit requirements
  • •Data-driven decisions for quality improvement initiatives
  • •Standardized metrics across global operations
  • •Self-service analytics empowering quality engineers