Search Results fact_fk_name

Overview

EDW_FACT_DIM_RELATIONS_MD_V is a metadata view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It belongs to the Enterprise Data Warehouse (EDW) metadata layer that ETRM documents as the repository of fact-to-dimension relationship definitions. The view presents a curated, read-only projection of the fact/dimension relationship repository and is primarily consumed by EDW metadata-driven extraction, transformation, and load (ETL) routines, BI Publisher reports, and Oracle Business Intelligence (OBIEE) repositories that need to resolve how a fact table joins to its conformed dimensions.

Rather than storing transactional data, the view exposes the structural glue of the warehouse: which fact column is a foreign key ("FACT_FK_NAME"), which column of which dimension it targets, and which unique key column in the dimension resolves the join. This makes it a metadata control surface for generating join logic and conformed-dimension mappings dynamically.

Underlying Base Objects

The view is defined over a single base object, EDW_FACT_DIM_RELATIONS_MD, a metadata table in the APPS schema that holds one row per fact-to-dimension foreign key relationship. No additional base tables or joined objects are documented in the ETRM metadata; the view is a straight column projection of that table.

Because it is a simple SELECT ... FROM projection with no joins, filters, or aggregation, the view adds no transformation logic. Its purpose is interface isolation: metadata consumers can query the view without depending directly on the physical metadata table, allowing Oracle to evolve the base table's storage or indexing without breaking dependent reports and integration programs. In 12.1.1 and 12.2.2 the definition is identical, so no release-specific behavior is required.

Key Columns

• FACT_ID / FACT_NAME / FACT_LONG_NAME — surrogate identifier, short name, and descriptive long name of the fact object.
• FACT_FK_ID / FACT_FK_NAME — the foreign key column on the fact table that references a dimension; FACT_FK_NAME is the column users most frequently search for when locating a fact-to-dimension link.
• FACT_FK_COL_ID / FACT_FK_COL_NAME — the underlying fact table column metadata associated with that foreign key.
• DIM_ID / DIM_NAME — identifier and name of the referenced dimension.
• DIM_UK_ID / DIM_UK_NAME / DIM_UK_LONG_NAME — the dimension unique key object that the foreign key resolves to.
• DIM_UK_COL_ID / DIM_UK_COL_NAME — the physical column in the dimension unique key used in the join predicate.

Common Use Cases and Queries

Because the view exposes the full join specification, the most common use is generating or auditing dimension join predicates for ETL and OBIEE repository builds, and locating every fact that references a given dimension or foreign key column.

Find all fact-to-dimension joins for a dimension:

SELECT fact_name, fact_fk_name, dim_name, dim_uk_col_name
FROM apps.edw_fact_dim_relations_md_v
WHERE dim_name = :p_dim_name;

Resolve a specific foreign key to its target dimension column, the query implied by the "fact_fk_name" search term:

SELECT fact_name, fact_fk_name, dim_name, dim_uk_col_name
FROM apps.edw_fact_dim_relations_md_v
WHERE fact_fk_name = :p_fk_name;

Inventory all relationships for a fact:

SELECT fact_fk_name, dim_name, dim_uk_name
FROM apps.edw_fact_dim_relations_md_v
WHERE fact_name = :p_fact_name
ORDER BY fact_fk_name;

Results are seeded metadata and require no runtime context, but queries should be issued with the APPS schema (or a synonym) and appropriate read privileges. Note the view contains no base-object documentation beyond the single table, so extension columns must be sourced from EDW_FACT_DIM_RELATIONS_MD directly.