Search Results edw_bim_ih_reasn_m_u2




Overview
Explore in AI Chat

Overview

The table BIM.EDW_BIM_IH_REASN_M is a core dimension table within the Oracle E-Business Suite (EBS) Business Intelligence (BIM) schema, specifically targeted at the Oracle ETRM (Energy Trading and Risk Management) product family. As indicated by the “EDW” (Enterprise Data Warehouse) prefix and its presence in the BIM schema, this table serves as a conformed dimension that stores reference data for Interaction Reasons. An Interaction Reason is a controlled business classification used to categorize the purpose or driver behind a customer, counterparty, or operational interaction recorded in the transactional systems. The table acts as a bridge between raw transactional fact data (e.g., interactions captured in the warehouse) and the dimensional attributes required for analytic reporting, such as drill-downs by reason code, description, or user-defined categories. The ETRM metadata explicitly warns that this object is intended for use only from standard Oracle Applications programs, confirming it is a warehouse-managed dimension, not a base ERP table.

Key Information Stored

The table stores a rich set of attributes that define each Interaction Reason dimension member. The most critical columns are:

  • RESN_INTRCTN_REASON_PK_KEY (NUMBER, Mandatory): System-generated unique surrogate key for the dimension row. This is the primary key of the table.
  • RESN_INTRCTN_REASON_PK (VARCHAR2(120)): Natural business identifier for the Interaction Reason. This is the logical key often used for lookups.
  • RESN_REASON_CODE (VARCHAR2(80)): The short, alphanumeric code representing the Interaction Reason (e.g., “COMPLAINT”, “INQUIRY”, “TRADE_ADJ”).
  • RESN_SHORT_DESCRIPTION and RESN_LONG_DESCRIPTION (VARCHAR2(240) and VARCHAR2(1000)): Human-readable text providing the meaning and extended context of the reason code.
  • RESN_NAME (VARCHAR2(240)): Reserved for future use in Oracle Express (OLAP). Currently unused.
  • ALL_ALL_PK and ALL_ALL_PK_KEY: Denormalized attributes for a higher-level “All” hierarchy, enabling rapid drill-down from summary to detail without extra joins.
  • RESN_USER_ATTRIBUTE1–5 (VARCHAR2(240)): Five open, customizable attribute columns that extend the dimension. These are commonly used in ETRM implementations for regional, departmental, or regulatory classifications.
  • RESN_INSTANCE (VARCHAR2(40)): Identifier for the source instance or operating unit from which the reason originated.
  • Standard Who Columns: CREATION_DATE, LAST_UPDATE_DATE, and SECURITY_GROUP_ID for audit trail and multi-org access control.
  • RESN_REASON_DP (VARCHAR2(240)): A discoverer-friendly display column that concatenates enough attributes to make every row uniquely identifiable to business users.

Common Use Cases and Queries

The primary use case is to provide dimension lookup and hierarchy support for fact tables that record interactions, such as BIM_EDW_INTRCTNS_F (referenced below). Typical queries include:

  • Drill-down reporting: Joining this dimension to interaction fact tables to analyze volumes, durations, or outcomes by reason. Example: “Show me the count of all interactions by reason code and user attribute 1.”
  • Code-to-description mapping: Translating reason codes stored at the source into full descriptions for operational dashboards. Example: SELECT RESN_REASON_CODE, RESN_SHORT_DESCRIPTION FROM BIM.EDW_BIM_IH_REASN_M;
  • Hierarchy traversal: Using the ALL_* columns to generate rollup reports that do not require multi-level parent-child joins.
  • Data validation and referential integrity checks: Verifying that every reason code appearing in fact tables exists in this dimension. Example: SELECT f.* FROM BIM.BIM_EDW_INTRCTNS_F f WHERE f.INTRCTN_REASON_FK_KEY NOT IN (SELECT RESN_INTRCTN_REASON_PK_KEY FROM BIM.EDW_BIM_IH_REASN_M);

Because the table resides in the APPS_TS_ARCHIVE tablespace, queries should be optimized for read-only, heavy select access typical of BI dashboards.

Related Objects

The metadata explicitly documents the following parent-child relationship:

  • Parent table referenced by this table: None. EDW_BIM_IH_REASN_M is a top-level dimension.
  • Child table referencing this table: BIM.BIM_EDW_INTRCTNS_F (Interaction Fact table). The foreign key column INTRCTN_REASON_FK_KEY in that fact table references RESN_INTRCTN_REASON_PK_KEY in this dimension. This is the primary join path for star-schema queries.
  • Indexes: Two unique indexes enforce integrity: EDW_BIM_IH_REASN_M_U1 (on both RESN_INTRCTN_REASON_PK and RESN_INTRCTN_REASON_PK_KEY) and EDW_BIM_IH_REASN_M_U2 (on RESN_INTRCTN_REASON_PK_KEY alone).