A Star Schema data warehouse designed for analytical decision support in pharmaceutical Quality Management System (QMS), implemented in PostgreSQL following Kimball dimensional modeling principles.

Pharmaceutical QMS analytics require traceability of events like deviations and NCRs across dimensions such as time, product, and process. This warehouse enables trend analysis and pattern detection without operational workflow enforcement.

Disclaimer: The following metrics are based on industry benchmarks and case studies from pharmaceutical QMS implementations. Actual results vary significantly based on:

• Current quality system maturity
• Data completeness and accuracy
• Organizational commitment to data-driven decisions

*Results from published case studies in pharmaceutical quality management

What This Means:

• Not a guarantee, but a realistic target
• Requires clean data and proper implementation
• ROI typically realized over 6-12 months

1. Delayed Issue Detection
   Quality events like equipment drifts or process failures often remain unnoticed for months, leading to batch rejections.

2. Fragmented Data Analysis
   Manual aggregation from scattered sources delays root cause identification and trend spotting.

3. Limited Traceability
   Historical changes in products or processes are not easily tracked, complicating compliance audits.

4. Data-Driven Decisions
   Management lacks reliable metrics for resource allocation and risk prioritization.

A OLAP-focused warehouse that:

✅ Supports early pattern detection through dimensional analysis.
✅ Enables historical traceability with SCD Type 2.
✅ Provides reusable dimensions for cross-event analysis.
✅ Scales for educational to production use without redesign.

┌─────────────────────┐
│     dim_time        │
│   (Calendar Days)   │
│  ▪ id_time (PK)     │
│  ▪ full_date (UK)   │
│  ▪ year, quarter    │
│  ▪ month, week      │
└──────────┬──────────┘
           │
           │ FK: id_time
           ▼
┌──────────────────────────────────────────────────────────────┐
│                    FACT: fact_deviation                      │
│                  (Quality Deviation Events)                  │
├──────────────────────────────────────────────────────────────┤
│  Primary Key:   id_deviation (surrogate key)                 │
│  Business Key:  deviation_number (DEV-YYYY-NNN)              │
├──────────────────────────────────────────────────────────────┤
│  Foreign Keys (6):                                           │
│    ▪ id_time              → dim_time                         │
│    ▪ id_product           → dim_product                      │
│    ▪ id_equipment         → dim_equipment (NULL allowed)     │
│    ▪ id_organization      → dim_organization                 │
│    ▪ id_process_step      → dim_process_step                 │
│    ▪ id_root_cause        → dim_root_cause (NULL allowed)    │
├──────────────────────────────────────────────────────────────┤
│  Measures (Aggregatable):                                    │
│    ▪ affected_quantity    (units impacted)                   │
│    ▪ rejected_quantity    (units scrapped)                   │
│    ▪ rework_quantity      (units reworked)                   │
│    ▪ financial_impact     (USD)                              │
│    ▪ downtime_minutes     (production stoppage)              │
├──────────────────────────────────────────────────────────────┤
│  Attributes (Descriptive):                                   │
│    ▪ severity_level       (Critical/Major/Minor)             │
│    ▪ detection_method     (where caught)                     │
│    ▪ status               (Open/Under Investigation/Closed)  │
│    ▪ is_reportable        (FDA/EMA regulatory flag)          │
└──────────────────────────────────────────────────────────────┘
           │           │           │           │           │
           │           │           │           │           │
  ┌────────┘    ┌──────┘    ┌──────┘    ┌──────┘    ┌──────┘
  │             │           │           │           │
  ▼             ▼           ▼           ▼           ▼
┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐
│  dim_   │ │  dim_   │ │  dim_   │ │  dim_   │ │  dim_   │
│ product │ │equipment│ │  org    │ │ process │ │  root   │
│         │ │         │ │         │ │  step   │ │  cause  │
├─────────┤ ├─────────┤ ├─────────┤ ├─────────┤ ├─────────┤
│Product  │ │Equipment│ │Org Units│ │Process  │ │6M Cause │
│Catalog  │ │Inventory│ │Hierarchy│ │Workflow │ │Taxonomy │
│         │ │         │ │         │ │         │ │         │
│▪ SKU    │ │▪ Calibr.│ │▪ Sites  │ │▪ Critic.│ │▪ Man    │
│▪ Formula│ │▪ Status │ │▪ Depts  │ │▪ Valid. │ │▪ Machine│
│▪ Version│ │▪ Type   │ │▪ Parent │ │▪ Order  │ │▪ Method │
└─────────┘ └─────────┘ └─────────┘ └─────────┘ └─────────┘
 SCD Type 2   SCD Type 2   SCD Type 2   SCD Type 2   SCD Type 2
 (History)    (History)    (History)    (History)    (History)

Schema Characteristics:

• Design Pattern: Star Schema (Kimball)
• Fact Grain: One row = One deviation event
• Dimension Type: SCD Type 2 (full history tracking)
• Relationships: 6 many-to-one from fact to dimensions
• NULL FK: Equipment and Root Cause (business logic allows)

fact_deviation: One row per quality deviation event

Measures:

• affected_quantity, rejected_quantity, rework_quantity
• financial_impact (USD)
• downtime_minutes

Attributes:

• severity_level: Critical / Major / Minor
• detection_method: In_Process / Final_Inspection / Customer_Complaint / Stability_Testing / Audit_Finding
• status: Open / Under_Investigation / CAPA_Required / Closed
• is_reportable: FDA/EMA regulatory flag

Business Logic Constraints:

-- Quantity validation
CONSTRAINT chk_quantities CHECK (
    rejected_quantity + rework_quantity <= affected_quantity
)

-- Date sequence validation  
CONSTRAINT chk_dates_sequence CHECK (
    reported_date >= deviation_date AND
    investigation_completed_date >= reported_date AND
    closed_date >= reported_date
)

-- Status-date correlation
CONSTRAINT chk_closed_logic CHECK (
    (status = 'Closed' AND closed_date IS NOT NULL) OR
    (status != 'Closed' AND closed_date IS NULL)
)

Indexes:

• 6 Foreign key indexes (fast joins)
• 4 Filter indexes (severity, status, batch)
• 2 Composite indexes (common queries)

21 CFR Part 11 Ready:

• Complete audit trail via SCD Type 2
• Historical snapshots preserved
• Traceability to source systems

ICH E2A Compliant:

• is_reportable flag for adverse events
• Severity classification
• Timeline tracking

• PostgreSQL 12+
• Git

1. Clone Repository

   git clone https://github.com/hadiii1i/pharma-qms-olap
   cd pharma-qms-olap

2. Create Database

   createdb pharma_qms

3. Create Schema

   psql -d pharma_qms -c "CREATE SCHEMA qms;"

4. Run DDL Script

   psql -d pharma_qms -f schemas/qms_complete_schema.sql

5. Verify

   psql -d pharma_qms -c "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'qms';"

   Expected: 7 tables

python scripts/generate_dim_time.py --start-year 2020 --end-year 2025 --output data/seed/dim_time.sql
psql -d pharma_qms -f data/seed/dim_time.sql

Solution: Check PostgreSQL status and start if needed.

Solution: Drop and recreate schema if exists.

Solution: Load dimensions before facts.

Solution: Use EXPLAIN ANALYZE and update statistics.

Focus on technical contributions:

• Sample data generators
• Query library
• ETL scripts
• Documentation

Process: Fork, branch, commit, PR.

MIT License - See LICENSE file.

Hadi Yabari
Data Architect | QMS Analytics Specialist

LinkedIn | Email | GitHub

GitHub Discussions | Report Bug | Request Feature

• 6 dimensions with SCD Type 2
• 1 fact table with constraints
• Indexes and documentation

• Additional fact tables (NCR, CAPA, audit)

• Python ETL framework
• Analytical query library