Search Results edw_cdi_dim_missing_keys

Overview

EDW_CDI_DIM_MISSING_KEYS is a utility table within the Oracle E-Business Suite Business Intelligence System (BIS) module, specifically belonging to the Enterprise Data Warehouse / Common Data Interface (EDW_CDI) family of objects. Its documented purpose is to record dimensional key values that could not be resolved against the corresponding dimension during the extract, transform, and load process. In an EBS 12.1.1 or 12.2.2 environment, this table functions as a diagnostic and reconciliation artifact for the data warehouse staging and conformance layer, capturing referential gaps between fact data and dimension data before or during aggregate loading.

The ETRM metadata classifies this object heuristically as a standalone entity under the Data Vault modeling heuristic — that is, the mined foreign key structure does not indicate that it participates as a hub, link, or satellite in a conformed Data Vault model. This classification should be treated as a modeling suggestion rather than a prescriptive design; in practice the table behaves as an operational logging or exception table rather than a core dimensional or transactional structure.

Note that the metadata explicitly states "Not implemented in this database," indicating that the object is documented in the ETRM repository but is not physically present in every EBS instance. Its presence depends on whether the EDW_CDI warehouse components have been installed and configured.

Key Information Stored

The documented physical schema for the 12.1.1 release lists an owner of BIS and six columns. The most significant columns are described below:

• DIM_ID — Identifies the dimension against which the key lookup failed. This is the principal attribute for grouping missing keys by target dimension.
• FACT_ID — The foreign key reference to EDW_CDI_DIM_MISSING_KEYS.FACT_ID → ASO_ER_DATA_BIN_FACT. This links each missing-key record to the fact row that produced the unresolved reference, providing traceability back to the source fact bin.
• INSTANCE — Denotes the source instance or environment context from which the missing key originated, useful where multiple source systems feed the warehouse.
• KEY_VALUE — The raw business key value that could not be matched to a dimension member. This is the primary business-key candidate for diagnosis.
• NUMBER_KEY_VALUE — A numeric representation of the key value, retained for keys that may be compared or joined numerically rather than as character data.
• MISSING_DATE — The date and time the unresolved key was detected, enabling trend analysis of data quality over load cycles.

No surrogate primary key is documented for this table in the ETRM metadata, nor are unique indexes identified. The combination of DIM_ID, FACT_ID, KEY_VALUE, and MISSING_DATE serves as the practical de-duplication and identification key for reporting purposes.

Common Use Cases and Queries

The primary scenario for this table is data-quality monitoring and exception resolution during ETL cycles. Typical queries include grouping by dimension to identify the most affected dimensions, and correlating missing keys with their originating fact rows:

• Aggregate missing keys by dimension: SELECT DIM_ID, COUNT(*) FROM EDW_CDI_DIM_MISSING_KEYS GROUP BY DIM_ID;
• Trend analysis by date: SELECT MISSING_DATE, COUNT(*) FROM EDW_CDI_DIM_MISSING_KEYS GROUP BY MISSING_DATE ORDER BY MISSING_DATE;
• Trace back to source facts: SELECT m.*, f.* FROM EDW_CDI_DIM_MISSING_KEYS m JOIN ASO_ER_DATA_BIN_FACT f ON m.FACT_ID = f.FACT_ID;

These patterns support root-cause analysis, remediation of dimension load failures, and reconciliation reporting for warehouse conformance.

Related Objects

• ASO_ER_DATA_BIN_FACT — Referenced through the FACT_ID foreign key; the primary relationship documented for this table and the source of the fact records that generated unresolved dimensional references.
• EDW_CDI dimension tables — The logical targets identified by DIM_ID against which key resolution is attempted.
• EDW_CDI ETL and conformance processes — Programs that populate this table during extraction and loading.

Because the ETRM metadata documents only a single foreign key relationship and classifies the object as standalone, related-object analysis is limited to the ASO_ER_DATA_BIN_FACT linkage and the broader EDW_CDI warehouse framework.