Results for “edw_dim_view”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

EDW_DIM_VIEW is a lightweight reporting view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It belongs to the BIS (Applications BIS — Business Intelligence System) product family, which supplies the extract, transformation, and dimensional model objects used by Oracle's embedded data warehouse and Oracle Business Intelligence (OBIEE) integrations. The view is documented with a status of VALID and provides a normalized projection of the dimension catalog maintained by the EDW layer. Its purpose is to expose two columns — an identifier and a descriptive value — so that downstream reporting tools, concurrent programs, and analytic extracts can resolve dimension IDs into human-readable dimension names without joining directly to the internal multi-currency and multi-language dimension views. By presenting a simplified ID/VALUE pair, EDW_DIM_VIEW acts as a stable lookup interface between the raw dimensional metadata and the presentation layer of EBS analytics.

Underlying Base Objects

The view text is defined as a single SELECT over EDW_DIMENSIONS_MD_V:

• EDW_DIMENSIONS_MD_V — the source view that holds the master list of dimensional definitions used by the EBS data warehouse. EDW_DIM_VIEW selects DIM_ID and DIM_LONG_NAME from this source and aliases them to ID and VALUE respectively.

The ETRM metadata notes that no referenced base tables are separately documented for this view, because it is defined entirely over another view rather than a physical table. Practically, EDW_DIMENSIONS_MD_V resolves to the underlying dimensions metadata table populated during the warehouse build, so EDW_DIM_VIEW inherits its row set and refresh behavior from that hierarchy. Because it is a simple one-to-one projection, the view introduces no aggregation, filtering, or transformation logic beyond the column aliasing.

Key Columns

• ID — the dimension identifier, sourced from DIM_ID. This is the numeric or surrogate key used throughout the EDW star schema to reference a dimension definition.
• VALUE — the descriptive dimension name, sourced from DIM_LONG_NAME. This is the long display name of the dimension, suitable for report labels, list-of-values presentations, and BI metadata.

Only these two columns are exposed, which keeps the view narrow and efficient for lookup-style queries. There is no effective-date, language, or ledger context exposed directly; any such context would reside in the parent EDW_DIMENSIONS_MD_V object.

Common Use Cases and Queries

EDW_DIM_VIEW is typically used to translate stored dimension IDs into readable names for reports, extracts, and validation queries, or to populate selection lists in custom BI dashboards. A typical lookup follows the pattern:

• Join the view to a fact or staging table on ID to render VALUE in output.
• Drive LOV-style queries that present DIM_LONG_NAME to end users.
• Validate that dimension IDs referenced in custom extracts exist in the warehouse catalog.

Representative SQL statements include the following:

SELECT id, value FROM apps.edw_dim_view ORDER BY value;

SELECT v.value AS dimension_name FROM apps.edw_dim_view v WHERE v.id = :dim_id;

SELECT f.fact_key, v.value FROM apps.some_fact_table f, apps.edw_dim_view v WHERE f.dim_id = v.id;

These queries are read-only and benefit from the view's simple structure. Because the view is owned by APPS and marked VALID, it can be referenced directly in custom concurrent programs, OBIEE repository layers, and SQL*Plus reports without additional grants beyond standard APPS access.