Search Results edw_bim_lead_status_m




Overview
Explore in AI Chat

Overview

The EDW_BIM_LEAD_STATUS_M table is a dimension table within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 environments, specifically for the now-obsolete Marketing Intelligence (BIM) module. Its primary role is to serve as a master reference for lead status codes and their descriptive attributes within the enterprise data warehouse (EDW) layer. As a dimension table, it provides the contextual, textual descriptions and hierarchical categorization for status codes that are used as foreign keys in transactional fact tables. This structure is fundamental to dimensional modeling, enabling efficient and user-friendly reporting and analytics on lead management processes by translating cryptic status keys into business-meaningful labels.

Key Information Stored

While the provided ETRM excerpt does not list specific column names, the table's description as a "Lead Status Dimension" and its primary key structure define its core content. The table is centered around the primary key column, L1_LEADSTATUS_PK_KEY. Typical columns in such a dimension table would include a surrogate key (the PK), a natural key from the source transactional system (e.g., LEAD_STATUS_CODE), and descriptive attributes such as LEAD_STATUS_NAME, DESCRIPTION, and potentially attributes for reporting hierarchies like STATUS_CATEGORY or IS_ACTIVE_FLAG. The table's purpose is to denormalize and store this descriptive information in a single, query-optimized location for analytical purposes.

Common Use Cases and Queries

The primary use case is to enrich lead-related fact data for reporting. Analytical queries joining lead transaction facts to this dimension allow users to slice and aggregate data by status, such as analyzing conversion rates, lead pipeline velocity, or the distribution of leads across different status categories. A common SQL pattern involves a simple join to retrieve status descriptions:

SELECT f.lead_id, f.creation_date, d.lead_status_name
FROM bim_edw_leads_f f,
     edw_bim_lead_status_m d
WHERE f.leadstatus_fk_key = d.l1_leadstatus_pk_key
AND d.lead_status_name = 'QUALIFIED';

Another critical use is in generating pre-built BI reports or dashboards within the obsolete BIM module, where this table provided the filter and grouping logic for all lead status-related analytics.

Related Objects

As documented in the ETRM relationship data, the EDW_BIM_LEAD_STATUS_M table is referenced as a foreign key constraint by three separate fact tables in the BIM schema. These relationships are:

  • BIM_EDW_LEADS_F: References this table via its LEADSTATUS_FK_KEY column. This is the core relationship for attributing status to individual lead records.
  • BIM_EDW_RVCT_MTH_F: References this table via its LEADSTATUS_FK_KEY column. This table likely stores monthly revenue cycle or funnel metrics segmented by lead status.
  • BIM_EDW_RVCT_DLY_F: References this table via its LEADSTATUS_FK_KEY column. This table likely stores daily granular data for the same revenue cycle metrics.

In all cases, the foreign key in the fact table (LEADSTATUS_FK_KEY) joins to the primary key (L1_LEADSTATUS_PK_KEY) in the EDW_BIM_LEAD_STATUS_M dimension table.