Search Results edw_sec_ref_info_t_n2

Overview

BIS.EDW_SEC_REF_INFO_T is a reference configuration table within the Oracle E-Business Suite Business Intelligence (BIS) schema, holding the security reference metadata used by the Enterprise Data Warehouse (EDW) reporting layer. Its functional description, "EDW Security Reference Table," indicates that it governs which responsibilities may access which fact and dimension combinations within EDW-based subject areas, thereby enforcing row-level and object-level security over analytical content.

The table is owned by BIS, resides in the APPS_TS_TX_DATA tablespace with a PCT_FREE of 10, and is defined as FND Design Data under the same object name. All four indexes (one unique, three nonunique) are placed in APPS_TS_TX_IDX, which follows standard Oracle EBS practice of separating index storage from table storage.

From a Data Vault modeling perspective, the mined dependency metadata classifies this object heuristically as a standalone construct. It is best treated as a reference or configuration entity rather than a transactional hub or link, because it carries descriptive attributes (FACT_NAME, DIM_NAME, FK_COL_NAME) that contextualize the relationships between security assignments and analytical metadata.

Key Information Stored

The table contains seven documented columns, all of which carry security-assignment semantics:

• APPL_ID (NUMBER) — Application identifier, scoping the security entry to a specific Oracle application.
• RESP_ID (NUMBER) — Responsibility identifier, identifying the EBS responsibility to which the security grant applies.
• FACT_ID (NUMBER) — Identifier of the fact being secured; this is the column the user searched for and is the primary discovery dimension for the row.
• FACT_NAME (VARCHAR2 255) — Denormalized display name of the fact, used for reporting and diagnostics.
• DIM_ID (NUMBER) — Identifier of the dimension associated with the fact, enabling dimension-level restriction.
• FK_COL_NAME (VARCHAR2 255) — Foreign-key column name in the fact table that maps to the secured dimension.
• DIM_NAME (VARCHAR2 255) — Denormalized display name of the dimension.

The unique index EDW_SEC_REF_INFO_T_N1 on (APPL_ID, RESP_ID, FACT_ID, FK_COL_NAME) is the business-key candidate: no two rows may define the same responsibility, fact, and foreign-key mapping within a single application. The surrogate aspect is implicit; the table's real identity is compositional. Nonunique indexes EDW_SEC_REF_INFO_T_N2 (FACT_ID), EDW_SEC_REF_INFO_T_N3 (DIM_ID), and EDW_SEC_REF_INFO_T_N4 (RESP_ID) support the most common lookup paths.

Common Use Cases and Queries

Typical usage includes validating that a given responsibility has access to a specific fact before rendering a dashboard, auditing which responsibilities are restricted from a fact, and joining to fact metadata to produce security matrices. A representative query filters by fact:

SELECT APPL_ID, RESP_ID, FACT_ID, FACT_NAME, DIM_ID, FK_COL_NAME, DIM_NAME FROM BIS.EDW_SEC_REF_INFO_T WHERE FACT_ID = :p_fact_id;

To enumerate all secured facts for a responsibility, filter on RESP_ID and index N4 is exercised. Reporting teams frequently denormalize FACT_NAME and DIM_NAME to avoid joins to metadata tables, making the table directly consumable by BI Publisher and OBIEE prompts.

Related Objects

• ASO_ER_DATA_BIN_FACT — the FACT_ID foreign key references this fact table, which is the primary consumer of the security definitions.
• APPS.EDW_SEC_REF_INFO_T — the APPS-synonym view through which application code and concurrent programs access the table.
• Dimension metadata tables referenced by DIM_ID and FK_COL_NAME, used to resolve dimension names and fact-to-dimension mappings.
• EBS responsibility and application definition tables (FND_RESPONSIBILITY, FND_APPLICATION) resolved through RESP_ID and APPL_ID.

Because the table is standalone in the modeled dependency graph, changes to its contents affect downstream EDW security rendering without cascading structural dependencies.