Search Results fact_longname

Overview

EDW_FACTS_MD is a metadata table owned by the BIS schema (Applications BIS product) in Oracle EBS 12.1.1 and 12.2.2. It functions as a dimensional or data-warehouse fact metadata catalog: a repository that describes the facts available in the enterprise data warehouse. Rather than storing transactional measures, it stores definitional records — the identity, short name, long name, and narrative description of each fact. This makes it a control or reference object consumed by ETL processes, BI reporting layers, and metadata-driven extraction logic that need to enumerate and label the facts (or fact groups) exposed by the EDW.

The ETRM documentation classifies this object as standalone under the heuristic Data Vault model. Under that heuristic, the table does not behave as a hub, link, or satellite in the strict Data Vault sense; it is a self-contained reference table whose rows are keyed by a surrogate identifier. Where an organization applies Data Vault conventions, EDW_FACTS_MD could be modeled as a reference table supporting other fact structures, but the documented schema exposes no inbound or outbound dependencies other than the foreign key described below.

Key Information Stored

The documented physical schema contains four columns in ETRM 12.1.1, which collectively identify and describe each fact entry:

• FACT_ID — The surrogate primary key. The documented foreign key EDW_FACTS_MD.FACT_ID → ASO_ER_DATA_BIN_FACT indicates that FACT_ID is the join column linking this metadata row to the underlying fact data bin. It is the primary integration point for downstream queries.
• FACT_NAME — The short business name or technical identifier for the fact, typically used as the label in pick lists, report parameters, and programmatic lookups.
• FACT_LONGNAME — The descriptive or user-facing long name, suitable for report headings and BI display layers where a concise but readable label is required.
• FACT_DESCRIPTION — Free-text documentation describing the purpose, scope, or semantics of the fact, supporting end-user understanding and metadata governance.

No unique indexes are documented beyond the primary key implied by the foreign key relationship. FACT_NAME and FACT_LONGNAME are the natural business-key candidates for human-readable lookup, but only FACT_ID is documented as the referential backbone.

Common Use Cases and Queries

The primary use case is resolving a fact identifier to its human-readable label during reporting and ETL. A typical join pattern pairs the metadata row with its fact data via the documented foreign key:

• Fact lookup for reports: SELECT m.FACT_ID, m.FACT_NAME, m.FACT_LONGNAME FROM BIS.EDW_FACTS_MD m WHERE m.FACT_ID = :fact_id; — resolves a single fact's display name.
• List all available facts: SELECT FACT_ID, FACT_NAME, FACT_LONGNAME FROM BIS.EDW_FACTS_MD ORDER BY FACT_NAME; — populates LOVs or report parameter lists.
• Joining to fact data: SELECT m.FACT_NAME, f.* FROM BIS.EDW_FACTS_MD m, BIS.ASO_ER_DATA_BIN_FACT f WHERE m.FACT_ID = f.FACT_ID; — enriches raw fact rows with descriptive metadata.
• Metadata search: querying FACT_DESCRIPTION with LIKE or Oracle Text to locate facts by keyword, useful when analysts search for terms such as enterprise data assets.

Because the table is small and reference-oriented, it is commonly cached in application-tier lookup caches or materialized into BI extracts to avoid repeated joins against the fact data bin.

Related Objects

The documented relationship set centers on the fact data bin. The most significant related objects are:

• ASO_ER_DATA_BIN_FACT — The referenced fact table; joined on EDW_FACTS_MD.FACT_ID = ASO_ER_DATA_BIN_FACT.FACT_ID.
• BIS schema dictionary views — Objects such as ALL_TAB_COLUMNS and ALL_CONSTRAINTS that document this table's structure when queried at runtime.
• EDW fact and dimension metadata tables — Sibling metadata catalogs in the BIS schema that describe dimensions and measures alongside the facts enumerated here.
• BI Publisher / Discoverer report definitions — Reporting artifacts that consume FACT_NAME and FACT_LONGNAME as display labels.
• Custom ETL packages — PL/SQL or OWB mappings that read this table to drive fact extraction and labeling across the warehouse.

No additional foreign keys or dependent views are documented in ETRM, so integration is best validated against the actual 12.1.1 or 12.2.2 instance before being relied upon.