Search Results mth_equipment_denorm_d




Overview
Explore in AI Chat

Overview

MTH_EQUIPMENT_DENORM_D is a denormalized dimension table owned by the MTH schema, the database schema that underpins Oracle Manufacturing Operations Center (MOC). MOC is an optional Manufacturing module that aggregates shop-floor and equipment data into a plant intelligence repository used for OEE (Overall Equipment Effectiveness), downtime, and yield analytics. Within that repository, MTH_EQUIPMENT_DENORM_D materializes a fully flattened equipment hierarchy — up to nine equipment levels plus the equipment leaf node — so that reporting tools can resolve ancestor–descendant relationships without recursive joins.

The table is documented in ETRM 12.2.2 with 84 columns and is flagged VALID. Its primary key is MTH_EQUIPM_DIMENSION_KEY_PK on the DIMENSION_KEY column. From a Data Vault modeling perspective, the table is classified heuristically as standalone; a reasonable modeling interpretation is that it behaves as a satellite attached to the equipment dimension, because it is a keyed, wide attribute table that hangs off the equipment hierarchy rather than acting as a hub or a link between two hubs. The denormalized layout is a deliberate performance trade-off: the recursive parent-child structure that exists in the normalized hierarchy tables is pre-computed here into repeating LEVEL1_* through LEVEL9_* column groups.

Key Information Stored

The table's most significant columns fall into three functional groups.

No business-key unique index is documented in the metadata beyond the DIMENSION_KEY primary key; the equipment identifier column EQUIPMENT_ID serves as the natural business-key candidate for tracing back to source equipment records.

Common Use Cases and Queries

The dominant use case is hierarchical roll-up reporting, where a query groups facts by any of the nine levels without invoking recursive SQL. A typical pattern joins this table to an equipment-level fact on DIMENSION_KEY and aggregates by a single LEVELn column.

  • Equipment list within a parent node: filter on LEVELn_ID = :id to return all descendant equipment rows.
  • Ancestry resolution for a leaf asset: select EQUIPMENT_ID together with LEVEL1_DESCRIPTION through LEVEL9_DESCRIPTION to display a full path.
  • Effective-date analysis: use EQUIPMENT_EFFECTIVE_DATE and EQUIPMENT_EXPIRATION_DATE to reconstruct the hierarchy as of a historical date.
  • Depth filtering: use LEVEL_NUM to isolate rows at a specific hierarchy depth or exclude shallow branches.
  • OEE and downtime reporting in MOC: join to MTH fact tables on DIMENSION_KEY to attribute production or downtime metrics to work centers, lines, or plants.

Related Objects

Because the metadata classifies the table as standalone, no enforced foreign keys are documented; relationships are logical and driven by shared keys.

  • MTH equipment dimension / hierarchy tables: joined on EQUIPMENT_ID or EQUIPMENT_HIERARCHY_KEY to retrieve normalized equipment definitions.
  • MOC fact and aggregate tables: joined on DIMENSION_KEY for OEE, downtime, and yield measures.
  • MTH hierarchy definition tables: referenced through the LEVELn_HIERARCHY_KEY columns to resolve hierarchy naming and configuration.
  • MTH_LEVEL / level-name reference data: used with LEVELn_LEVEL_NAME and LEVELn_LEVEL_KEY to translate numeric levels into business labels.
  • Standard EBS audit lookup (FND_USER): joined on CREATED_BY and LAST_UPDATED_BY for row-level audit reporting.

Administrators and report authors should treat MTH_EQUIPMENT_DENORM_D as a read-optimized, refresh-managed structure; it is populated from the MOC hierarchy load process rather than maintained transactionally.