Master Data Aligned but Reports Wrong? The Three-Layer Fix: Identity, History, Query Logic

Don't Rush to Declare "Master Data Only Manages Codes"

The article opens with a factory scenario: equipment codes are unified across asset ledger, maintenance, and production systems, yet a production manager finds that a report incorrectly attributes pre-replacement machining records to the new equipment. Investigation reveals maintenance logs use equipment codes while production logs use workstation codes, and the report joins them using the current workstation-equipment configuration, causing historical records to be misassigned.

The core issue is not duplicate codes or format errors but how relationships are used . The author identifies three distinct problems:

Identity alignment: Are two records referring to the same physical equipment? The asset ledger and maintenance system already maintain code mappings.

Historical relationships: Which equipment was at which workstation at a given time? Equipment installation and removal dates must be queryable.

Query semantics: Does the report actually use the historical relationship? Even if history exists, a query that only takes the latest configuration will still produce wrong results.

A data engineer might suggest fixing this with a slowly changing dimension (SCD Type 2) approach — adding effective start/end dates to preserve history, as described in Kimball Group's dimensional modeling techniques. However, the article stresses that business rules — what event defines the start/end, whether multiple devices can occupy a workstation simultaneously, how backfilled records are handled — must be clarified first. Existing history tables and shared query services that already resolve these issues should be reused directly.

Tracing Back from a Single Error Record

Concretely: Equipment A left workstation 3 on September 10, replaced by Equipment B. Investigating a September 8 anomaly batch requires linking to Equipment A. The article walks through the diagnostic steps:

Find trustworthy installation/removal records; missing history cannot be invented by adding ontology links.

Interpret timestamps correctly: a September 10 replacement recorded on September 12 must still match production records to the actual replacement time; same-day handovers need precise cutover times.

Verify workstation cardinality: if a workstation can host multiple devices concurrently, task allocation or equipment runtime logs are needed — don't force a one-to-one model for simplicity.

Fix the query program: if historical data and matching rules are correct but the report still picks only the latest record, correct the query before considering platform changes.

Build a Reuse-and-Gap Checklist Before Modeling

Following this trace yields a concrete checklist (originally presented as a table):

Stable equipment identification: Unified equipment codes and cross-system mapping tables exist → verify mappings, reuse existing codes.

Distinguish equipment from workstation: Asset ledger has equipment; production system has workstation dictionary → add explicit distinction, avoid merging just because names match.

Restore historical installation relationships: Replacement records exist but reports only receive latest configuration → complete historical relationships with effective times, preserve sources.

Retrieve the equipment that actually processed a batch: Production records carry workstation and occurrence time → for single-device workstations match by time; for multi-device workstations also check task allocation.

Make reports join correctly: Old reports hard-coded to current equipment → correct queries, validate pre/post-replacement records.

Enable consistent cross-application querying: Each application currently interprets "workstation-equipment" differently → jointly confirm relationship definitions and usage rules, preferring existing definitions.

The article emphasizes that ontology can support a shared business semantics layer without replacing master data . Master data continues to provide stable equipment identities; ontology clarifies the meaning of equipment, workstation, and processing tasks, and query services retrieve data per those definitions. Responsibilities must also align: equipment codes stay with the original master data owner, replacement facts are confirmed by the field change process, and modelers help articulate relationships and query rules — not guess what happened on the shop floor.

What Ontology Should Add: Only What Needs Shared Use

If only one report is fixed, the effort may stop there. But when maintenance analysis, quality traceability, and production dashboards all need the same equipment history, letting each team write its own interpretation leads to drift. Ontology can unify the expression of equipment-workstation-task relationships, temporal meanings, and constraints for shared use. The author prefers starting with OPM (Object-Process Methodology) diagrams drawn with business users to visualize the replacement process — Equipment A exits, Equipment B enters, the equipment-workstation relationship changes while device identities remain stable — then using that model to audit existing data and interfaces.

After model agreement, someone must still implement mappings, rewrite queries, and run test cases. Models don't auto-populate history or make legacy reports adopt correct time conditions. A lightweight approach — extending existing models and relation tables so several applications call a single historical query service — is often sufficient. Only when relationship complexity exceeds current tooling should specialized databases be considered. When replicating master data for querying, the source of truth, refresh frequency, and impact of sync lag must be explicit; the worst outcome is dual write paths leaving two conflicting answers for "which equipment is at which workstation".

Validate with Pre- and Post-Replacement Records

Verification starts with the basic single-device workstation case, then checks exceptions:

Pre-replacement records link to Equipment A, post-replacement to Equipment B; records near the cutover are validated against the field-confirmed handover rule.

If Equipment A later moves to workstation 4, its code stays A and its history at workstation 3 remains queryable.

Overlapping installation records for a supposedly single-device workstation must raise a conflict or follow approved resolution rules — not silently pick the latest entry.

Missing history or imprecise handover times should be explicitly flagged as unverified.

Access control must be re-checked: richer relationships should not inadvertently expose data previously restricted.

Finally, regression test with the original report's normal samples — fixing historical errors must not break current queries.

These test cases confirm that within the validated scope, the original master data, newly added relationships, and corrected queries work together. A successfully published model file does not prove this.

Summary

The investigation may conclude with just a query fix, or with a shared relationship and rule set for multiple applications — both are valid outcomes. The key is respecting existing data assets by reusing valid codes, definitions, history records, and maintenance processes . Filling missing relationships and correcting misused queries, while documenting what was reused and where gaps were filled, matters more than building another new platform. The next article will discuss how far the first phase should go to avoid both over-scoping and creating a new silo.

Reference

Kimball Group, Type 2: Add New Row . Explains how SCD Type 2 preserves history by adding version rows with effective/expiration dates. The replacement case and the ontology-vs-existing-service division are designed for this article and do not imply all SCD Type 2 implementations follow this pattern. https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/type-2/