Search Results fact_fk_col_name

Overview

EDW_SEC_FACT_INFO_V is a dictionary-style view in the APPS schema that exposes metadata describing the relationship between fact entities and dimension entities in the Oracle E-Business Suite Enterprise Data Warehouse (EDW) / ETRM (Enterprise Transaction Reporting Model) layer. Rather than storing transactional data, the view publishes the descriptive information that reporting tools and integration processes require in order to navigate the EDW star-schema model correctly. It surfaces, for each fact-to-dimension relationship, the fact identifier, its names, the associated dimension identifier, and the foreign key column name that physically links the fact table to the dimension table.

In release 12.1.1 and 12.2.2, EBS reporting and analytics are increasingly driven by metadata-driven engines, and this view is designed to support that style of consumption. A repository or BI layer can query EDW_SEC_FACT_INFO_V to discover which columns serve as join keys between fact and dimension structures without hard-coding those relationships in report logic. The view is read-only and derives its content entirely from a single documented source view, making it a thin projection rather than an independent data store.

Underlying Base Objects

The ETRM documentation lists no base tables for this object; instead, the view is defined over another view, EDW_FACT_DIM_RELATIONS_MD_V. The stored SQL is:

• SELECT fact_id, fact_name, fact_long_name, dim_id, fact_fk_col_name fk_col_name FROM EDW_FACT_DIM_RELATIONS_MD_V

Two characteristics of this definition are notable. First, every column of the parent view is passed through unchanged except for the final column, which is relabeled from FACT_FK_COL_NAME to the shorter alias FK_COL_NAME. This suggests EDW_SEC_FACT_INFO_V exists specifically to present a simpler, more consumable column naming convention for downstream metadata consumers. Second, because the object is a view layered on another view, its performance and behavior are inherited from EDW_FACT_DIM_RELATIONS_MD_V and, transitively, from the underlying EDW metadata tables that populate the fact-dimension relations repository. No additional filtering, joins, or aggregation is applied at this level, so the row cardinality of EDW_SEC_FACT_INFO_V equals that of its parent view.

Key Columns

The view exposes five columns, each carrying metadata about a fact-to-dimension linkage:

• FACT_ID — The internal identifier of the fact entity whose relationships are being described.
• FACT_NAME — The short, system-level name of the fact, typically used as the technical identifier in generated SQL.
• FACT_LONG_NAME — The descriptive or user-facing name of the fact, suitable for display in reporting metadata browsers.
• DIM_ID — The identifier of the dimension entity related to the fact, enabling navigation from fact to dimension metadata.
• FK_COL_NAME — The foreign key column name in the fact structure that references the dimension. This is the aliased form of FACT_FK_COL_NAME and is the column most directly relevant to the user search term "fact_fk_col_name," since the view deliberately renames it to FK_COL_NAME.

Common Use Cases and Queries

Typical uses include metadata validation, automated join generation, and documentation of the EDW model. For example, to review all documented foreign key columns for a given fact:

• SELECT fact_id, fact_name, dim_id, fk_col_name FROM apps.edw_sec_fact_info_v WHERE fact_name = :p_fact;
• SELECT fact_name, fact_long_name, fk_col_name FROM apps.edw_sec_fact_info_v ORDER BY fact_name;

Because the view is a simple projection of EDW_FACT_DIM_RELATIONS_MD_V, queries against it are effectively queries against the parent view, and any predicate on FACT_NAME or DIM_ID is passed through for evaluation at the metadata source. Users searching for "fact_fk_col_name" should note that the column appears here under the alias FK_COL_NAME; querying the parent view EDW_FACT_DIM_RELATIONS_MD_V directly will return the same value under the original name.