Search Results edw_dimensions_md_v




Overview
Explore in AI Chat

Overview

EDW_DIMENSIONS_MD_V is a read-only dictionary view owned by the APPS schema in Oracle E-Business Suite. It belongs to the BIS (Business Intelligence System / Applications BIS) product family, which supplies the extract, staging, and dimensional infrastructure used by Oracle's embedded data warehouse and the Enterprise Data Warehouse (EDW) reporting layer. The suffix _MD_V denotes a metadata view, distinguishing it from the fact and dimension value views published for end-user reporting. Its purpose is to expose the catalog of dimensions available to the warehouse so that ETL programs, report definitions, and BI metadata utilities can enumerate dimension identifiers, their source column names, display prefixes, and target table names without querying the underlying collection structures directly.

In ETRM 12.1.1 and 12.2.2 the object is documented as VALID, and its definition is intentionally thin: it is created WITH READ ONLY and projects columns from a single supplier view, CMPWBDIMENSION_V. This makes EDW_DIMENSIONS_MD_V a stable, presentation-layer contract that insulates downstream consumers from changes in the underlying BIS dictionary implementation.

Underlying Base Objects

The documented view text references exactly one object, CMPWBDIMENSION_V DIM, aliased as DIM, with no join, filter, or aggregation applied. Because CMPWBDIMENSION_V is itself a view over the BIS dimension registry, EDW_DIMENSIONS_MD_V is effectively a pass-through projection: it renames and reorders the supplier view's columns into the DIM_* naming convention expected by the EDW metadata consumers. The clause WITH READ ONLY guarantees that no DML can be issued against the view, which is appropriate for a metadata catalog consumed by reporting and extraction processes.

The documentation records no other referenced base objects. Tracing consumption therefore requires following CMPWBDIMENSION_V into the underlying BIS dictionary tables, which reside in the same APPS schema territory. Because the dependency is a single view-to-view hop, the object generally survives upgrades and patches with minimal disruption, but it should never be treated as a source of transactional data.

Key Columns

The view exposes six columns. The documented column list and the select list are not perfectly aligned in the ETRM excerpt, so the following reflects the published names and their evident meaning:

  • DIM_ID — The unique identifier of the dimension within the BIS metadata registry; sourced from ELEMENTID.
  • DIM_NAME — The internal dimension name used programmatically by the warehouse; sourced from NAME.
  • DIM_PREFIX — The short prefix applied to generated column names for this dimension; sourced from PREFIX.
  • DIM_LONG_NAME — The verbose or business-friendly dimension name; sourced from LONGNAME.
  • DIM_TABLE_NAME — The warehouse table that physically stores the dimension; sourced from the second NAME reference in the select list.
  • DIM_DESCRIPTION — Free-text description of the dimension's contents and intended use; sourced from DESCRIPTION.

Common Use Cases and Queries

Typical use cases include validating which dimensions are registered before running an EDW extract, generating dynamic SQL that resolves a dimension prefix to its physical table, and feeding BI metadata repositories with the dimension catalog.

Listing all registered dimensions:

  • SELECT dim_id, dim_name, dim_long_name, dim_table_name FROM apps.edw_dimensions_md_v ORDER BY dim_name;

Locating a dimension by prefix:

  • SELECT dim_name, dim_table_name FROM apps.edw_dimensions_md_v WHERE dim_prefix = 'GL';

Searching descriptions for a business term:

  • SELECT dim_id, dim_name, dim_description FROM apps.edw_dimensions_md_v WHERE UPPER(dim_description) LIKE '%CUSTOMER%';

Because the view is read-only, it is safe to expose to read-only reporting responsibilities, though access should be granted through a custom synonym rather than directly to APPS.