Dimensional model vs normalized schema for change control audit reporting

QMS ETQ Reliance
, , , , , , , ,
1

I’m evaluating dimensional modeling versus normalized schema design for our change control audit reporting in ETQ Reliance 2022. We’re hitting performance issues with complex audit queries that join 8-12 normalized tables.

Our current normalized design follows 3NF principles, which is great for data integrity but terrible for reporting. A typical change impact analysis query takes 15-20 seconds because we’re joining change_requests → change_items → affected_parts → part_revisions → approval_workflows → audit_logs.

I’m considering moving to a star schema with a change_fact table and separate dimension tables for dates, parts, approvers, and change types. The denormalized fact table would store pre-calculated metrics and commonly queried attributes.

-- Current normalized query (slow)
SELECT cr.request_id, ci.item_desc, ap.part_number,
       aw.approver_name, al.audit_action
FROM change_requests cr
JOIN change_items ci ON cr.request_id = ci.request_fk
JOIN affected_parts ap ON ci.item_id = ap.item_fk
JOIN approval_workflows aw ON cr.request_id = aw.request_fk
JOIN audit_logs al ON cr.request_id = al.entity_fk;

Has anyone implemented dimensional modeling for change control? What are the tradeoffs between star and snowflake schemas in ETQ? How do you handle slowly changing dimensions for part revisions?

2

Having implemented both approaches across multiple ETQ installations, here’s my analysis of the key tradeoffs:

Star Schema vs Snowflake Schema Tradeoffs:

Star schema wins for query performance and simplicity. Your dimensional queries will be faster because you’re joining fact table to denormalized dimensions - fewer joins overall. The downside is storage redundancy. If you have 50,000 change requests all referencing the same approver, that approver’s details are duplicated 50,000 times in a denormalized approver dimension.

Snowflake schema normalizes dimensions into sub-dimensions, reducing redundancy but adding join complexity. For change control, I’d recommend a hybrid: star schema for simple dimensions (date, change type, priority) and snowflake for complex hierarchical dimensions (organizational structure, part taxonomy). This balances performance and storage efficiency.

Fact and Dimension Table Design:

Your fact table grain is critical. For change control audit reporting, I recommend change item level (not request level). This allows analysis like “which specific items take longest to approve” or “which part categories have most change frequency.” Your fact table should store:

  • Foreign keys to all dimensions
  • Numeric measures (approval_duration_hours, affected_part_count, revision_number)
  • Degenerate dimensions (request_number, item_sequence) that don’t warrant separate dimension tables

Dimension tables should be wide and denormalized. Don’t be afraid of 30-40 columns in a dimension if it eliminates joins. Include descriptive attributes that users filter and group by in reports.

Normalized vs Denormalized Data Storage:

My recommendation: Keep both. Maintain your normalized operational schema (3NF) for transactional processing - change request creation, approvals, updates. This ensures data integrity and supports ETQ’s application logic. Build a separate dimensional model (star/snowflake hybrid) specifically for reporting and analytics.

Use ETL to sync data nightly (or hourly if needed). The ETL process handles:

  • Slowly changing dimensions (Type 2 for parts, Type 1 for most others)
  • Fact table population with pre-calculated metrics
  • Historical snapshots for point-in-time compliance reporting

Change Impact Analysis Requirements:

For impact analysis, your dimensional model should support:

  1. Temporal analysis: Which time periods have highest change volume?
  2. Part impact: Which parts/families are most frequently changed?
  3. Approval bottlenecks: Where do changes get delayed?
  4. Cross-functional impact: How do changes ripple through departments?

Design your dimensions to enable these analyses. For example, a robust date dimension with fiscal periods, quarters, holidays, etc. A part dimension with full taxonomy hierarchy. An organizational dimension showing department rollups.

The 15-20 second query you showed would become sub-second in a star schema:

-- Dimensional model query (fast)
SELECT d.calendar_date, p.part_number, a.approver_name,
       at.audit_action, f.approval_duration_hours
FROM change_fact f
JOIN date_dim d ON f.date_key = d.date_key
JOIN part_dim p ON f.part_key = p.part_key
JOIN approver_dim a ON f.approver_key = a.approver_key
JOIN audit_type_dim at ON f.audit_type_key = at.audit_type_key
WHERE d.fiscal_year = 2024;

Five simple joins to pre-indexed dimension keys versus your current 12-table normalized nightmare.

Bottom Line: For audit reporting and analytics in ETQ change control, dimensional modeling is the right choice. The storage cost is negligible compared to the performance gains and analytical flexibility. Use Type 2 SCDs for parts to maintain historical accuracy. Implement a hybrid star/snowflake design based on dimension complexity. Most importantly, keep your operational normalized schema intact and treat the dimensional model as a separate reporting database populated via ETL.

3

Before you redesign the entire schema, have you tried indexed views or materialized query tables? Sometimes you can get dimensional model performance without full denormalization. Create an indexed view that pre-joins your common query patterns. ETQ 2022 supports materialized views on SQL Server and Oracle backends. This gives you the best of both worlds - normalized storage with denormalized query performance.

4

For slowly changing dimensions with part revisions, definitely use Type 2 SCD. Store effective_from and effective_to dates in your part dimension. When a part revision changes, insert a new dimension row rather than updating. This preserves historical accuracy for audit reports. Your fact table references the dimension row that was current when the change request was created. It’s more complex than Type 1 but essential for regulatory compliance where you need to reconstruct exactly what data looked like at any point in time.

5

We went through this exact decision last year. Star schema made our reporting 10x faster but increased storage by about 30%. The key is identifying which queries are read-heavy vs write-heavy. For audit reporting, reads dominate, so denormalization makes sense. We kept the normalized operational tables and built a separate star schema for reporting with nightly ETL jobs.

6

Star schema vs snowflake really depends on your change impact analysis requirements. We use snowflake because our part dimension has a complex hierarchy - product families, product lines, part categories, individual parts. Normalizing the part dimension into separate tables reduced redundancy significantly. Yes, it adds joins, but with proper indexing, the performance hit is minimal compared to the storage savings and data integrity benefits.