Search Results number_key_value

Overview

The Oracle EBS view APPS.EDW_CDI_DIM_MISSING_KEYS_S is a public database view registered in the APPS schema under the FND design data reference BIS.EDW_CDI_DIM_MISSING_KEYS_S. Its status is VALID in Oracle E-Business Suite 12.1.1 and 12.2.2. The view is part of the Enterprise Data Warehouse (EDW) component of the Oracle Business Intelligence System (BIS) product family, which supplies the extract, transform, and load (ETL) infrastructure used to populate the EBS data warehouse and analytical reporting layers.

The view presents a diagnostic record of dimension keys that were expected by a fact load but could not be resolved against the corresponding dimension. In a star-schema warehouse, every fact row must resolve its foreign keys to valid dimension rows. When a source transaction references a dimension member that has not yet been staged, EDW_CDI_DIM_MISSING_KEYS_S captures that unresolved reference. Because it exposes both a character representation (KEY_VALUE) and a numeric representation (NUMBER_KEY_VALUE) of the missing key, it directly supports the common search term number_key_value and the reconciliation of source identifiers to warehouse surrogate keys. The _S suffix conventionally denotes a staging or intermediate view used during ETL processing rather than an end-user reporting object.

Underlying Base Objects

The ETRM metadata documents that EDW_CDI_DIM_MISSING_KEYS_S references the following objects within the APPS schema: EDW_CDI_DIM_MISSING_KEYS, and the time-dimension lookups EDW_TIME_DAY_LTC, EDW_TIME_HALF_MONTH_LTC, EDW_TIME_HALF_YEAR_LTC, EDW_TIME_MONTH_LTC, EDW_TIME_QTR_LTC, and EDW_TIME_YEAR_LTC. No database object references this view, confirming its role as a leaf-level diagnostic consumer rather than a dependency of other warehouse code.

The primary base object is EDW_CDI_DIM_MISSING_KEYS, which stores the captured missing-key records. The EDW_TIME_*_LTC objects are the time-level lookup tables that translate date values into the day, half-month, month, quarter, half-year, and year foreign keys exposed by the view. This dependency pattern indicates that the view resolves a missing-key event to the time hierarchy in which it occurred.

Key Columns

• DIM_NAME (NUMBER) — Identifier of the dimension in which the key could not be resolved.
• FACT_NAME (NUMBER) — Identifier of the fact table whose load detected the missing key.
• KEY_VALUE (VARCHAR2(400)) — Character-form source identifier that failed to resolve.
• NUMBER_KEY_VALUE (NUMBER) — Numeric form of the same missing key, used when the source key is an integer surrogate.
• INSTANCE (VARCHAR2(40)) — Instance or source-system qualifier for the record.
• MISSING_DATE (NUMBER) — Date on which the missing key was recorded.
• MONTH_FK (NUMBER) — Foreign key to the month level of the time dimension.
• YEAR_FK (NUMBER) — Foreign key to the year level of the time dimension.

Common Use Cases and Queries

Typical usage involves auditing ETL completeness, identifying unresolved source identifiers, and reporting data-quality exceptions by period. The simplest access pattern retrieves all columns:

SELECT DIM_NAME, FACT_NAME, KEY_VALUE, NUMBER_KEY_VALUE, INSTANCE, MISSING_DATE, MONTH_FK, YEAR_FK FROM APPS.EDW_CDI_DIM_MISSING_KEYS_S;

To isolate numeric-key failures, filter on the numeric representation and order by period:

SELECT DIM_NAME, FACT_NAME, NUMBER_KEY_VALUE, MONTH_FK, YEAR_FK FROM APPS.EDW_CDI_DIM_MISSING_KEYS_S WHERE NUMBER_KEY_VALUE IS NOT NULL ORDER BY YEAR_FK, MONTH_FK;

For reconciliation against a specific source instance, constrain on INSTANCE and count exceptions per dimension:

SELECT DIM_NAME, COUNT(*) FROM APPS.EDW_CDI_DIM_MISSING_KEYS_S WHERE INSTANCE = :instance GROUP BY DIM_NAME;

These queries are read-only and suitable for custom reporting, since the view is documented as public and may be used for reporting or other data requirements.