Search Results edw_bim_opp_status_m




Overview
Explore in AI Chat

Overview

The EDW_BIM_OPP_STATUS_M table is a dimension table within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 environments, specifically under the BIM (Marketing Intelligence) product module, which is documented as obsolete. Its primary role is to serve as a master reference for opportunity status codes within the Enterprise Data Warehouse (EDW) layer of the application. As a dimension table, it provides the descriptive context and categorization for status keys used in transactional fact tables, enabling consistent reporting and analysis of sales opportunities across the enterprise data model. Its existence indicates a structured data warehousing approach for marketing and sales performance metrics.

Key Information Stored

While the provided metadata does not list specific column names, the structure and purpose of a status dimension table imply it contains at least two critical types of fields. The primary key column, referenced as L1_OPPSTATUS_PK_KEY, is a surrogate key that uniquely identifies each status record and is used for joins to fact tables. The table will also contain descriptive columns for the status, such as a status code (e.g., 'OPEN', 'CLOSED_WON', 'LOST') and a corresponding full name or description (e.g., 'Open Opportunity', 'Closed - Won', 'Closed - Lost'). It may also include columns for controlling data warehouse loads, such as effective start and end dates (SCD Type 2), and an active flag.

Common Use Cases and Queries

This table is central to generating analytical reports on the sales pipeline. Common use cases include analyzing the distribution of opportunities by status, calculating conversion rates from one status to another, and tracking historical changes in opportunity status over time. A typical reporting query would join this dimension table to a fact table to translate foreign key IDs into business-meaningful labels.

Sample Query Pattern:
SELECT opp.SALES_AMOUNT, sts.STATUS_NAME
FROM bim_edw_oprnies_f opp
JOIN edw_bim_opp_status_m sts
ON opp.oppstatus_fk_key = sts.l1_oppstatus_pk_key
WHERE opp.period_key = 202403;

Related Objects

As documented in the provided relationship data, the EDW_BIM_OPP_STATUS_M table is referenced as a foreign key constraint by several transactional fact tables in the BIM schema. These relationships are critical for maintaining referential integrity in the data warehouse.

  • BIM_EDW_OPRNTIES_F: References this table via the column OPPSTATUS_FK_KEY. This is likely the primary Opportunities fact table.
  • BIM_EDW_RVCT_DLY_F: References this table via the column OPPSTATUS_FK_KEY. This appears to be a daily revenue or forecast fact table.
  • BIM_EDW_RVCT_MTH_F: References this table via the column OPPSTATUS_FK_KEY. This appears to be a monthly revenue or forecast fact table.

All joins to this dimension table from the listed fact tables are performed on the fact table's foreign key column to the dimension table's primary key, L1_OPPSTATUS_PK_KEY.