Search Results edw_fact_dim_relations_md

Overview

The EDW_FACT_DIM_RELATIONS_MD table is a metadata repository object owned by the BIS schema (Applications BIS product family) within Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the relational metadata that binds fact entities to their associated dimension entities within the Oracle Enterprise Data Warehouse / Business Intelligence foundation. Rather than holding transactional business data, the table persists the structural "wiring" of the warehouse: which columns act as fact foreign keys, which columns serve as dimension unique keys, and the human-readable labels applied to each of those constructs. It functions as the semantic dictionary that downstream ETL, Discoverer, and OBIEE layers consult to resolve joins between fact and dimension tables.

From a Data Vault modeling perspective, the mined foreign-key structure classifies this object heuristically as standalone — that is, it does not exhibit the satellite-style dependency chain typical of hub, link, or satellite tables. The single documented foreign key (FACT_ID → ASO_ER_DATA_BIN_FACT) confirms that it references an external fact container without itself being a dependent satellite of a business key hub. This classification is a modeling suggestion only and reflects the observed FK topology rather than a declared Data Vault construct.

Key Information Stored

The table carries 14 documented columns. The most significant are grouped below by role:

• Fact identity: FACT_ID is the surrogate linkage column and the documented foreign key to ASO_ER_DATA_BIN_FACT. FACT_NAME, FACT_LONG_NAME, and FACT_FK_ID carry the fact's internal name, descriptive label, and foreign-key definition identifier.
• Fact foreign-key columns: FACT_FK_NAME, FACT_FK_COL_ID, and FACT_FK_COL_NAME describe the join column(s) on the fact side — the attributes that point outward to a dimension.
• Dimension unique-key columns: DIM_UK_COL_ID, DIM_UK_COL_NAME, DIM_UK_ID, DIM_UK_NAME, and DIM_UK_LONG_NAME identify the targeted dimension's unique key and its display names, completing the fact-to-dimension join contract.
• Dimension identity: DIM_ID and DIM_NAME record the surrogate identifier and name of the referenced dimension.

No separate unique index columns are documented beyond FACT_ID; business-key candidacy is therefore expressed through the composite of FACT_ID, DIM_ID, and the associated key column identifiers.

Common Use Cases and Queries

Typical usage centers on BIS/EDW metadata governance and automated join generation. Analysts query the table to enumerate valid fact-to-dimension paths, to reconcile naming conventions, or to drive tooling that generates SQL joins dynamically.

• Enumerate dimensions related to a given fact:

  SELECT FACT_NAME, DIM_NAME, FACT_FK_COL_NAME, DIM_UK_COL_NAME
  FROM   BIS.EDW_FACT_DIM_RELATIONS_MD
  WHERE  FACT_ID = :p_fact_id;

• Generate a join clause from stored metadata:

  SELECT 'F.'||FACT_FK_COL_NAME||' = D.'||DIM_UK_COL_NAME AS join_predicate
  FROM   BIS.EDW_FACT_DIM_RELATIONS_MD
  WHERE  DIM_ID = :p_dim_id;

• Validate completeness of fact/dimension coverage for a star-schema subject area.
• Feed ETL or BI repository loaders that require the physical key pairing between a fact and each of its conformed dimensions.

Related Objects

The table's principal documented relationship is its foreign key:

• ASO_ER_DATA_BIN_FACT — referenced via EDW_FACT_DIM_RELATIONS_MD.FACT_ID; the parent fact registry that anchors the relationship.

Additional BIS-layer objects that participate in the same metadata ecosystem and are commonly joined for reporting include the fact and dimension registries resolved through FACT_ID and DIM_ID, the column-definition catalogs referenced by FACT_FK_COL_ID and DIM_UK_COL_ID, and the EDW metadata views that surface these relations to Discoverer and OBIEE. Because the model is classified standalone, no additional satellite-style dependents are documented in the ETRM metadata.