Search Results edw_sec_ref_info_t

Overview

EDW_SEC_REF_INFO_T is a metadata reference table owned by the BIS schema (Applications BIS / Business Intelligence System) in Oracle E-Business Suite 12.1.1 and 12.2.2. Its name and structure indicate that it is an Enterprise Data Warehouse (EDW) security reference information table, used to describe how warehouse fact data is secured and mapped to dimensional metadata. The table stores associations between application and responsibility context (APPL_ID, RESP_ID) and the underlying warehouse fact or dimension objects, along with the foreign-key column name that links them.

Under the ETRM 12.1.1 documented physical schema, the table contains seven columns and one unique index, EDW_SEC_REF_INFO_T_N1, defined on (APPL_ID, RESP_ID, FACT_ID, FK_COL_NAME). The ETRM metadata explicitly notes "Not implemented in this database" and classifies the object heuristically as a standalone table in Data Vault terms — that is, it does not behave as a conventional hub or link in the mined FK structure, although its composite unique key suggests it functions as a reference or mapping artifact rather than a transactional entity. The single documented foreign-key relationship is EDW_SEC_REF_INFO_T.FACT_ID → ASO_ER_DATA_BIN_FACT.

Key Information Stored

The most significant columns in EDW_SEC_REF_INFO_T are:

• APPL_ID — the application identifier, defining the EBS application context in which the security reference applies.
• RESP_ID — the responsibility identifier, which scopes the reference information to a specific responsibility.
• FACT_ID — the identifier of the fact record; this is the column that participates in the foreign-key relationship to ASO_ER_DATA_BIN_FACT.
• FACT_NAME — the descriptive name of the fact, useful for reporting and metadata presentation.
• DIM_ID — the identifier of the associated dimension record.
• FK_COL_NAME — the physical column name of the foreign key that connects the fact to the dimension; this is the crux of the mapping and forms part of the unique business key.
• DIM_NAME — the descriptive name of the dimension.

There is no documented single-column surrogate primary key in the ETRM extract. The unique index EDW_SEC_REF_INFO_T_N1 (APPL_ID, RESP_ID, FACT_ID, FK_COL_NAME) serves as the business-key candidate, and the combination of APPL_ID, RESP_ID, FACT_ID, and FK_COL_NAME should be treated as the logical identifier of a row.

Common Use Cases and Queries

Because the table is a security and metadata reference artifact, typical uses involve joining fact identifiers to dimension metadata for secured reporting. A representative query pattern is:

• Retrieve all FK column mappings for a given fact: SELECT FACT_ID, FACT_NAME, DIM_ID, DIM_NAME, FK_COL_NAME FROM EDW_SEC_REF_INFO_T WHERE FACT_ID = :fact_id;
• List security references for an application and responsibility: SELECT FACT_ID, FK_COL_NAME FROM EDW_SEC_REF_INFO_T WHERE APPL_ID = :appl_id AND RESP_ID = :resp_id;
• Join to the fact table to resolve fact names to data bins: SELECT s.FACT_ID, s.FK_COL_NAME, f.* FROM EDW_SEC_REF_INFO_T s JOIN ASO_ER_DATA_BIN_FACT f ON f.FACT_ID = s.FACT_ID;

These queries support BI Publisher reports, custom data-warehouse security models, and metadata-driven ETL that dynamically determines the FK column joining a fact to its dimension.

Related Objects

• ASO_ER_DATA_BIN_FACT — joined on EDW_SEC_REF_INFO_T.FACT_ID = ASO_ER_DATA_BIN_FACT.FACT_ID; the primary FK relationship documented.
• EDW_SEC_REF_INFO_T_N1 — the unique index on (APPL_ID, RESP_ID, FACT_ID, FK_COL_NAME) enforcing the business key.
• Applications BIS (schema owner) — the owning schema in which the table is defined.
• FND_APPLICATION / FND_RESPONSIBILITY — likely joins on APPL_ID and RESP_ID for descriptive resolution (not documented as FKs, but consistent with the column semantics).

The table’s narrow footprint and "not implemented" status in the ETRM extract imply limited deployment; validation against the target database is advised before reliance.