Search Results missing_date

Overview

The EDW_CDI_DIM_MISSING_KEYS_S view is a BIS (Business Intelligence System) dictionary object owned by the APPS schema and registered as VALID in Oracle EBS 12.1.1 and 12.2.2. It belongs to the EDW (Enterprise Data Warehouse) family of objects used to support the Oracle E-Business Suite data warehouse and Daily Business Intelligence (DBI) reporting layers. The view exposes records from the EDW_CDI_DIM_MISSING_KEYS table and enriches each row with calendar dimension surrogate keys resolved through the EDW time-level tables. Its principal role is to identify fact rows whose dimensional keys could not be matched to a valid dimension member during the ETL load process, while simultaneously attaching the correct Month and Year foreign keys for reporting alignment. In the context of a search for EDW_TIME_MONTH_LTC, this view is significant because EDW_TIME_MONTH_LTC is one of the joins used to derive the MONTH_FK column.

Underlying Base Objects

The documented view text references the following base objects:

• EDW_CDI_DIM_MISSING_KEYS — the primary driving table holding unresolved dimension-key records.
• EDW_TIME_DAY_LTC — joined on DAY_PK_KEY = EDW_CDI_DIM_MISSING_KEYS.MISSING_DATE.
• EDW_TIME_HALF_MONTH_LTC — linked via the half-month foreign key on the day level.
• EDW_TIME_MONTH_LTC — linked via HALF_MONTH_FK_KEY = MONTH_PK_KEY; supplies MONTH_PK_KEY as MONTH_FK.
• EDW_TIME_QTR_LTC — linked via QTR_FK_KEY = QTR_PK_KEY.
• EDW_TIME_HALF_YEAR_LTC — linked via HALF_YEAR_FK_KEY = HALF_YEAR_PK_KEY.
• EDW_TIME_YEAR_LTC — linked via YEAR_FK_KEY = YEAR_PK_KEY; supplies YEAR_PK_KEY as YEAR_FK.

Although the documented ETRM excerpt records no base reference view metadata, the embedded SQL text confirms these seven EDW tables. The join chain traverses the EDW time hierarchy from day to year, ensuring that each missing-key record is positioned within a valid calendar hierarchy for time-sliced reporting.

Key Columns

• DIM_ID — identifier of the dimension containing the unresolved key.
• FACT_ID — identifier of the fact table or fact record affected.
• KEY_VALUE — textual value of the orphaned key encountered during load.
• NUMBER_KEY_VALUE — numeric representation of the same key, where applicable.
• INSTANCE — identifies the warehouse instance or source system instance.
• MISSING_DATE — the date used to resolve the calendar hierarchy joins.
• MONTH_FK — surrogate key from EDW_TIME_MONTH_LTC; the month-level foreign key.
• YEAR_FK — surrogate key from EDW_TIME_YEAR_LTC; the year-level foreign key.

Note that the header column list in the ETRM documentation includes DIM_NAME and FACT_NAME; these are typically surfaced from the base missing-keys table for readability even though the exposed SELECT projection begins with DIM_ID.

Common Use Cases and Queries

This view is most commonly used to diagnose and audit data-quality exceptions in the EDW load, and to report them by month and year. A representative query retrieving unresolved dimension keys by month and year might appear as follows:

SELECT dim_name, fact_name, key_value, missing_date, month_fk, year_fk
FROM apps.edw_cdi_dim_missing_keys_s
WHERE instance = :instance
ORDER BY missing_date DESC;

A more targeted monthly reconciliation query, which directly leverages the EDW_TIME_MONTH_LTC linkage, would be:

SELECT month_fk, COUNT(*) missing_count
FROM apps.edw_cdi_dim_missing_keys_s
GROUP BY month_fk
ORDER BY month_fk;

Because the joins enforce a complete calendar hierarchy, results are guaranteed to carry valid MONTH_FK and YEAR_FK values, making the view suitable for trending missing-key volumes across reporting periods. Typical consumers include ETL validation reports, warehouse reconciliation dashboards, and DBI exception reports. In summary, EDW_CDI_DIM_MISSING_KEYS_S is a diagnostic dimension-integrity view whose calendar enrichment depends on EDW_TIME_MONTH_LTC and related time-level tables.