Search Results fact_long_name




Overview
Explore in AI Chat

Overview

EDW_SEC_FACT_INFO_T is a metadata repository table owned by the BIS (Business Intelligence System / Applications BIS) schema within Oracle EBS 12.1.1 and 12.2.2. Its purpose is to describe the fact constructs used by the Enterprise Data Warehouse (EDW) security and analytical model, cataloguing each fact by its internal identifier, its display name, and the dimension to which it is bound. Rather than storing transactional measures itself, the table acts as a dictionary that allows reporting, security, and ETL processes to resolve fact identifiers into meaningful business labels and to trace the dimensional context of each fact.

The ETRM documentation for this build records the object as not implemented in the current database, meaning the definition is delivered as part of the BIS/EDW metadata footprint but is not physically deployed in every environment. Where it does exist, the documented schema contains five columns under the BIS owner. The heuristic Data Vault classification mined from the foreign-key structure is standalone, indicating the table is not a classic hub, link, or satellite but a reference/descriptive entity. From a modeling perspective, it is best treated as a reference dimension or lookup whose rows qualify the fact definitions consumed elsewhere in the warehouse.

Key Information Stored

The documented column set is compact and oriented toward identification and labelling of fact definitions:

  • FACT_ID — The primary identifying attribute for each fact definition. It is also the column that participates in the documented foreign-key relationship, where EDW_SEC_FACT_INFO_T.FACT_ID references ASO_ER_DATA_BIN_FACT. This makes FACT_ID the practical surrogate key and the join column to the underlying fact tables.
  • FACT_NAME — The short internal name of the fact, typically the programmatic identifier used by ETL jobs and metadata-driven report definitions.
  • FACT_LONG_NAME — The descriptive, user-facing label for the fact, suitable for display in report headers, column captions, and BI tool presentation layers.
  • DIM_ID — The identifier of the dimension associated with the fact. This is the linkage that tells consumers which dimensional grain or conformed dimension the fact participates in.
  • FK_COL_NAME — The name of the foreign-key column through which the fact relates to its dimension. This provides the physical join guidance required for generated SQL.

Because the object is documented with only these five columns, no separate unique business-key index is recorded beyond FACT_ID itself. FACT_ID should therefore be treated as the single documented candidate key, with FACT_NAME serving as a secondary semantic identifier where uniqueness is enforced by the warehouse load process.

Common Use Cases and Queries

The principal use case is metadata-driven report generation: BI and EDW reporting layers consult this table to translate a fact identifier into a display name and to discover the dimension join required to build a query. Security administration is a second use case, where fact-level access definitions reference FACT_ID to determine which users or responsibilities may see a given measure.

A representative metadata lookup joins the fact definition to its underlying fact table:

  • SELECT f.FACT_ID, f.FACT_NAME, f.FACT_LONG_NAME, f.DIM_ID, f.FK_COL_NAME FROM EDW_SEC_FACT_INFO_T f WHERE f.FACT_ID = :p_fact_id;
  • Joining to the fact table for a dimensional analysis: SELECT i.FACT_LONG_NAME, b.* FROM EDW_SEC_FACT_INFO_T i JOIN ASO_ER_DATA_BIN_FACT b ON b.FACT_ID = i.FACT_ID;
  • Inventory of all defined facts and their dimensions: SELECT FACT_NAME, FACT_LONG_NAME, DIM_ID FROM EDW_SEC_FACT_INFO_T ORDER BY FACT_NAME;

Because the table is documented as not implemented in the reference database, scripts should guard against ORA-00942 by validating existence in ALL_TABLES before executing metadata-driven logic.

Related Objects

The most significant relationship documented is the foreign key from this table to the fact storage object:

  • ASO_ER_DATA_BIN_FACT — The referenced fact table; joined on FACT_ID. This is the primary dependent object and the target of the documented foreign key.
  • EDW_SEC_FACT_INFO_T.DIM_ID — Logically points to a dimension definition object in the BIS/EDW model (for example, a dimension metadata table in the same schema), forming the fact-to-dimension bridge.
  • EDW_SEC_FACT_INFO_T.FK_COL_NAME — Supplies the physical column name used to join the fact to that dimension, effectively documenting the relationship between ASO_ER_DATA_BIN_FACT and its associated dimension table.
  • Other BIS EDW metadata tables — Fact, dimension, and security definition tables in the BIS schema consume FACT_ID and DIM_ID to build the analytical and security model, and are typically queried alongside this object.

No additional foreign keys are documented for EDW_SEC_FACT_INFO_T, consistent with its standalone Data Vault classification.