Results for “edw_fact_hier_md”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

EDW_FACT_HIER_MD is a table owned by the BIS schema within the Oracle E-Business Suite environment. In EBS 12.1.1 and 12.2.2, the BIS product (Business Intelligence System / Applications BIS) historically delivered the underlying repository for Oracle's embedded business intelligence and Daily Business Intelligence (DBI) functionality. EDW_FACT_HIER_MD resides in this analytical layer and functions as a metadata-bearing fact/hierarchy bridge that maps analytical fact records to the dimensions and hierarchies they participate in.

The table is documented with six physical columns and is classified as VALID in the ETRM repository. Its role is to associate fact instances with the dimensional hierarchies that structure them for reporting and aggregation. A heuristic Data Vault classification mined from the foreign-key structure labels this object as a standalone entity rather than a hub, link, or satellite, because it carries a direct FK to the fact table while providing its own descriptive attributes. From a modeling-suggestion perspective, this positions the table as a lightweight reference or bridge in the analytical schema, connecting fact identifiers to their dimensional and hierarchical context rather than representing an independent business entity.

Key Information Stored

The six documented columns capture the essential mapping between facts, dimensions, and hierarchies:

• FACT_ID — The foreign key linking back to the fact table ASO_ER_DATA_BIN_FACT. This is the principal join column and the identifying reference for the fact side of the relationship.
• FACT_NAME — The descriptive business name of the associated fact, used for reporting labels and human-readable identification.
• DIM_ID — The surrogate identifier of the dimension associated with the fact record.
• DIM_NAME — The descriptive business name of the dimension, providing the readable label corresponding to DIM_ID.
• HIER_ID — The surrogate identifier of the hierarchy that organizes the dimension.
• HIER_NAME — The descriptive business name of the hierarchy.

Where documented, the surrogate primary key is FACT_ID (via its FK relationship to ASO_ER_DATA_BIN_FACT). The name-based columns (FACT_NAME, DIM_NAME, HIER_NAME) serve as business-key candidates and descriptive attributes, while DIM_ID and HIER_ID function as dimension and hierarchy references respectively.

Common Use Cases and Queries

The table is principally consumed by BI reporting and DBI-style analytical queries that must resolve which dimensions and hierarchies structure a given fact. A typical join pattern correlates a fact record to its dimensional hierarchy definitions:

SELECT h.FACT_ID,
       h.FACT_NAME,
       h.DIM_NAME,
       h.HIER_NAME
FROM   BIS.EDW_FACT_HIER_MD h,
       BIS.ASO_ER_DATA_BIN_FACT f
WHERE  h.FACT_ID = f.FACT_ID;

Common scenarios include: validating which hierarchies are populated for a fact during report development; listing all dimensions tracked for a fact to drive period-end BI refreshes; auditing the FACT_ID values stored in fact table data bins against their declared hierarchy metadata; and producing metadata catalogs that document, for each fact, the dimensions and hierarchies available to end users.

Related Objects

• ASO_ER_DATA_BIN_FACT — Referenced by EDW_FACT_HIER_MD.FACT_ID; the primary fact table supplying the fact keys.
• Other EDW_FACT_* metadata objects — Sibling tables in the BIS analytical layer describing fact-level metadata.
• EDW dimension and hierarchy metadata tables — Objects resolving DIM_ID and HIER_ID to their definitions.
• BIS/DBI reporting views — Reporting layer views that consume this mapping to present hierarchy-aware facts.

Because EDW_FACT_HIER_MD is classified as standalone, its integrity depends primarily on the FACT_ID foreign key to ASO_ER_DATA_BIN_FACT; the DIM_ID and HIER_ID columns are not documented as enforced FKs in the ETRM metadata.