A comprehensive data warehousing solution for tobacco product quality assurance using Kimball dimensional modeling methodology
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.
Mapping business processes to conformed dimensions
| Business Process | Date | Product | Manufacturing Site | Production Line | Batch | Test Type | Inspector | Machine | Raw Material | Supplier | Defect 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.
Transaction fact
One row per detected defect
Periodic snapshot fact
One row per product per line per day
Transaction fact
One row per raw material test
Transaction fact
One row per audit finding
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
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
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