Search Results missing_date

Overview

BIS.EDW_CDI_DIM_MISSING_KEYS is a table owned by the BIS (Business Intelligence System) schema within Oracle E-Business Suite 12.1.1 / 12.2.2. It is a supporting staging and diagnostic object within the Enterprise Data Warehouse (EDW) and Customer Data Integration (CDI) layer, which underpins the Oracle Business Intelligence Applications and Enterprise Performance Management reporting stack. Its name reflects its purpose: it records dimensional key values that were encountered during an ETL or dimension-conformance run but could not be resolved to a valid dimension member. These unresolved — or "missing" — keys are captured in this table so that data stewards and ETL developers can rectify source data gaps, materially improving the integrity of downstream star-schema loads.

Metadata classifies the object heuristically as standalone under the Data Vault model. A reasonable modeling suggestion would be to treat it as neither a hub nor a link in the strict Data Vault sense, but rather as a quality-audit satellite or landing table that records the fact of a failed dimensional resolution. Its design is deliberately narrow and denormalized, designed for fast insert during load and quick selective retrieval during exception review.

Key Information Stored

The table contains six documented columns, all of which are essential to its diagnostic function:

• DIM_ID — The numeric identifier of the dimension (or dimension role) against which the missing key was tested. A non-unique index, EDW_CDI_DIM_MISSING_KEYSN1, is built on this column, making dimension-centric filtering efficient.
• FACT_ID — The numeric identifier of the fact (or fact group) whose load produced the missing key. EDW_CDI_DIM_MISSING_KEYSN2 provides a non-unique index on this column, supporting fact-oriented exception queries.
• INSTANCE — A VARCHAR2(40) value identifying the source instance, ETL run, or environment in which the missing key was observed. This allows multiple sources or repeated runs to be reconciled.
• KEY_VALUE — A VARCHAR2(400) holding the character-form business key value that failed to resolve. This is the primary diagnostic payload for text-keyed dimensions.
• NUMBER_KEY_VALUE — The numeric counterpart of the business key, used when the unresolved dimension key is numeric rather than character.
• MISSING_DATE — A NUMBER representation of the date (typically Julian or Oracle date serial) on which the missing key was detected. This is the column most relevant to the user's "missing_date" search, enabling time-trended analysis of data quality defects.

No surrogate primary key is documented; the closest candidates for a compound business key are (DIM_ID, FACT_ID, KEY_VALUE, MISSING_DATE) or similar combinations. The two indexes are non-unique, so uniqueness is not enforced at the database level.

Common Use Cases and Queries

The principal use case is data quality monitoring. ETL developers run periodic queries to identify which dimension members are absent, then decide whether to add new members, correct source values, or map to an existing member. A typical retrieval is:

• Filter by DIM_ID to see all unresolved keys for a specific dimension.
• Filter by MISSING_DATE (>= a cutoff) to isolate recent defects, as in WHERE MISSING_DATE >= :run_date.
• Aggregate counts by INSTANCE or FACT_ID to identify the source system driving the majority of exceptions.

Reporting use cases include exception dashboards that trend the volume of unresolved keys over time, and post-load reconciliation reports that demonstrate the completeness of dimension conformance before releasing a fact load.

Related Objects

The table is connected to the fact layer through a foreign key from FACT_ID to ASO_ER_DATA_BIN_FACT, which is the most significant dependency. No other outbound foreign keys are documented, and no reporting views are listed as referencing this table in the ETRM metadata, indicating that consumption is primarily direct SQL by ETL routines and data stewards. The companion dimension and fact load programs within the BIS/EDW_CDI family operate alongside this table but are not formally recorded as dependent objects. Practitioners should reference the FND design data entry BIS.EDW_CDI_DIM_MISSING_KEYS when configuring the associated concurrent programs and loaders.