Search Results edw_sec_dim_access

Overview

EDW_SEC_DIM_ACCESS is a data warehouse–oriented security dimension table owned by the BIS (Business Intelligence System / Applications BIS) schema in Oracle E-Business Suite. The table exposes the intersection of three security-relevant dimensions — responsibility (RESP_ID), a dimension identifier (DIM_ID), and a hierarchy level (LEVEL_ID) — together with an ACCESS_VALUE that governs the level of access granted. Its purpose is to support reporting and analytical queries in which row-level or dimension-level security must be resolved without traversing the full transactional security model of the EBS database.

The table is documented as not implemented in this database, and therefore represents a delivered EDW/BI object definition rather than an actively populated transactional table in every environment. Its presence should be confirmed through the ETRM data dictionary or by querying the BIS schema directly. Under the heuristic Data Vault classification mined from the foreign-key structure, EDW_SEC_DIM_ACCESS is classified as standalone. In Data Vault modeling terms this suggests the table is best treated as a standalone reference or bridge-like structure rather than a true hub or link, since no strong hub-to-link network is exposed by the documented foreign keys.

Key Information Stored

The physical schema documented for ETRM 12.1.1 contains eleven columns. The most significant are:

• RESP_ID — the responsibility identifier; the primary security principal against which access is evaluated.
• DIM_ID — the identifier of the security dimension being secured (for example, a reporting dimension such as organization or ledger).
• LEVEL_ID — the hierarchy level identifier; this participates in a foreign key to MSD_LEVELS, anchoring the table to the level metadata used by the dimensional access model.
• ACCESS_VALUE — the access code or flag indicating the level of permission granted to the responsibility for the given dimension and level combination.
• DIM_SHORT_NAME — the user-facing short name of the dimension, denormalized for reporting convenience.
• LEVEL_SHORT_NAME — the short name of the level, also denormalized for reporting.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — the standard EBS audit columns tracking who created and last modified each row.

The documented business key is exposed through the unique index EDW_SEC_DIM_ACCESS_N1 on (RESP_ID, DIM_ID, LEVEL_ID, ACCESS_VALUE). This composite index defines uniqueness at the intersection of responsibility, dimension, level, and access value. No separate surrogate primary key column is documented in the metadata, so the composite unique index functions as the definitive business key for row identification.

Common Use Cases and Queries

Because the table is not populated in all environments, the primary use cases are definitional and reporting-oriented. Typical applications include:

• Generating a security matrix report that lists, for each responsibility, the dimensions and levels it may access.
• Resolving the effective access value for a responsibility during BI extraction, before applying row-level filters to a fact query.
• Auditing which responsibilities have been granted access at each level, using the audit columns to identify recent changes.
• Validating that every ACCESS_VALUE recorded has a valid corresponding LEVEL_ID in MSD_LEVELS.

A simple resolution query follows the documented join:

SELECT a.RESP_ID, a.DIM_SHORT_NAME, a.LEVEL_SHORT_NAME, a.ACCESS_VALUE
FROM EDW_SEC_DIM_ACCESS a, MSD_LEVELS l
WHERE a.LEVEL_ID = l.LEVEL_ID
AND a.RESP_ID = :p_resp_id;

Reporting queries should filter on RESP_ID and DIM_ID wherever possible, since these lead the unique index EDW_SEC_DIM_ACCESS_N1 and provide the most selective access path.

Related Objects

The most significant related objects are:

• MSD_LEVELS — referenced by the foreign key EDW_SEC_DIM_ACCESS.LEVEL_ID → MSD_LEVELS. This is the primary parent object supplying level metadata and is the only documented foreign-key relationship.
• FND_RESPONSIBILITY — the underlying EBS repository for responsibilities; RESP_ID values correspond to FND_RESPONSIBILITY.RESPONSIBILITY_ID, making this the natural join for responsibility names.
• FND_APPLICATION — provides the application context in which a responsibility is defined, useful when grouping access by module.
• EDW_SEC_DIM — the companion dimension table (implied by the shared DIM_ID / DIM_SHORT_NAME naming) that describes the dimensions secured by this table.
• FND_USER_RESP_GROUPS — links users to responsibilities and is commonly joined when expanding responsibility-level access to the user level.
• FND_COMPILED_MENU_FUNCTIONS and FND_MENUS — provide the functional access context that complements dimension-level security.

All object names not explicitly documented in the ETRM metadata above (FND_RESPONSIBILITY, FND_APPLICATION, EDW_SEC_DIM, and so on) are inferred from standard EBS naming and should be verified against the target environment before use.