Search Results dim_description




Overview
Explore in AI Chat

Overview

EDW_DIMENSIONS_MD_V is an Oracle E-Business Suite view owned by the APPS schema. It is a metadata-driven projection over the EDW_DIMENSIONS_MD table and exposes the descriptive attributes that describe each warehouse dimension known to the Enterprise Data Warehouse layer. Its name follows the EBS convention of suffixing a view with _V and prefixing EBS-facing objects with EDW, indicating that the object belongs to the analytics/warehouse metadata family rather than to a transactional module such as GL, AP, or AR. The view is available in both Release 12.1.1 and 12.2.2 and presents an identical column list across those releases.

The view contains no filtering or transformation logic: it is a straight column projection of its single base table. Its value therefore lies in the fact that it provides a stable, read-only access path to dimension metadata, allowing reporting tools, data-integration jobs, and custom BI extracts to enumerate dimensions and their physical landing tables without touching the underlying metadata table directly.

Underlying Base Objects

According to the documented view text, EDW_DIMENSIONS_MD_V is defined as:

The only referenced base object is EDW_DIMENSIONS_MD, the dimension metadata master table. No additional tables, joins, or synonyms beyond the APPS-resolved table are documented. Because the view performs a one-to-one projection from the base table, the cardinality and the row-level content of the two objects are identical; any row visible through the view is a row of EDW_DIMENSIONS_MD, and the view inherits the base table's privileges and object grants rather than introducing its own logic layer.

Key Columns

  • DIM_ID — The unique surrogate identifier of the dimension. It is the primary key used by downstream ETL mappings and by fact-table foreign keys.
  • DIM_NAME — The short internal name of the dimension, typically the identifier used in programmatic references.
  • DIM_PREFIX — The column-name prefix applied to the dimension's keys and attributes in the warehouse schema. This column is what enables generator tools and star-transformation queries to assemble dimension column names dynamically.
  • DIM_LONG_NAME — The fully descriptive business name of the dimension, suitable for display in reports, dashboards, and metadata catalogs.
  • DIM_TABLE_NAME — The physical object name holding the dimension's rows. ETL processes and BI layers use this value to build dynamic SQL against the correct dimension table.
  • DIM_DESCRIPTION — Free-text descriptive commentary for the dimension, used in documentation, data-lineage reports, and dictionary-style searches. This is the column most frequently targeted by ad hoc lookups.

Common Use Cases and Queries

Because the view is a metadata catalogue, the most frequent usage pattern is lookup rather than bulk extraction. Typical scenarios include locating the physical table behind a logical dimension, listing all registered dimensions for a data dictionary, and retrieving descriptions for documentation. A representative query returns the complete dimension inventory in business-name order:

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

To resolve the physical object for a specific dimension, filtering on the short name is effective:

  • SELECT dim_table_name FROM apps.edw_dimensions_md_v WHERE dim_name = :p_dim_name;

The view should be accessed with the APPS credentials, or from a custom schema granted SELECT on the underlying table through the standard APPS synonym mechanism. Reporting queries should be restricted to metadata browsing and schema-generation logic; the view carries no transactional data and must not be joined to fact tables for sales, costing, or accounting reporting. Because there is no documented WHERE clause or ROWNUM restriction, it is safe to query without concern for cursor instability, though appropriate ordering should be applied whenever the result is presented directly to a user.