Results for “dim_name”

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

Overview

EDW_SEC_DIM_INFO_V is a metadata view belonging to the Oracle E-Business Suite Business Intelligence System (BIS) product family, specifically the Enterprise Data Warehouse (EDW) metadata layer. It exposes descriptive information about the dimensions defined within the EDW metadata repository, combining dimension-level attributes with the associated unique key and lowest-level key column metadata. In Oracle EBS 12.1.1 and 12.2.2, this view is not implemented as a database object in the standard EBS schema; the ETRM documentation explicitly records it as "Not implemented in this database." Consequently, it functions as a documentation artifact describing the intended contract of the view rather than an active, queryable object in a typical production instance.

Its role within the EDW reporting and integration stack is to provide a single, denormalized lookup for dimension metadata—linking a dimension's identifier, name, long name, underlying table, and lowest-level (leaf) column. This metadata is typically consumed by ETL processes, security models, and reporting layers that must resolve dimension context information dynamically rather than through hard-coded references.

Underlying Base Objects

The view is defined over three EDW metadata views:

• EDW_DIMENSIONS_MD_V DIM — the primary source of dimension attributes, supplying DIM_ID, DIM_NAME, and DIM_LONG_NAME.
• EDW_UNIQUE_KEYS_MD_V KEYS — supplies the unique key definitions and is joined to the dimension on DIM.DIM_ID = KEYS.ENTITY_ID, restricted to primary keys via KEYS.PRIMARYKEY = 1.
• EDW_UNIQUE_KEY_COLUMNS_MD_V COLS — supplies the column-level detail of each unique key and is joined on KEYS.KEY_ID = COLS.KEY_ID, yielding the lowest-level column name.

The ETRM metadata records no directly referenced base tables and does not assign an owner, which is consistent with its "not implemented" status. The join chain is deliberately constrained to primary keys so that the returned column represents the dimension's lowest grain.

Key Columns

• DIM_ID — the numeric identifier of the dimension in the EDW metadata repository. Serves as the join key to the unique-key metadata.
• DIM_NAME — the short name of the dimension and the value used to populate TABLE_NAME, reflecting the conventional EDW naming that maps a dimension to its physical table.
• DIM_LONG_NAME — the descriptive, user-facing name of the dimension.
• TABLE_NAME — aliases DIM_NAME so that consumers can resolve the physical backing table directly from the view.
• LOWEST_LEVEL_COL_NAME — the column name of the primary unique key, indicating the lowest level of granularity available in the dimension.
• CONTEXT_NAME — derived as SUBSTR(DIM_NAME, 5, 30), stripping the leading four characters (typically a prefix such as "DIM_") to produce the context identifier used in security and context-sensitive reporting.

Common Use Cases and Queries

Typical uses include resolving a dimension's physical table and lowest-level column for ETL mapping, and deriving context names for application-context or security predicates. A representative query:

• SELECT DIM_ID, DIM_NAME, TABLE_NAME, LOWEST_LEVEL_COL_NAME, CONTEXT_NAME FROM EDW_SEC_DIM_INFO_V WHERE DIM_NAME = :p_dim;
• SELECT DIM_NAME, DIM_LONG_NAME, CONTEXT_NAME FROM EDW_SEC_DIM_INFO_V ORDER BY DIM_NAME;

Because the view is documented as not implemented, validation against a specific 12.1.1 or 12.2.2 instance should be performed before reliance in production SQL.