Search Results edw_bim_leads_m




Overview
Explore in AI Chat

Overview

The EDW_BIM_LEADS_M table is a core dimension table within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 environments, specifically for the Marketing Intelligence (BIM) module. As a dimension table in a data warehousing schema, its primary role is to store descriptive, context-rich information about sales and marketing leads. This table acts as a master reference point, providing consistent and detailed attributes for lead entities that are analyzed in conjunction with various transactional fact tables. Its existence within the BIM schema underscores its function in enabling complex analytical reporting, performance tracking, and data mining for marketing campaigns and lead generation efforts across the enterprise.

Key Information Stored

While the provided metadata does not list specific column names, the table's designation as a "Lead Dimension Table" and its primary key structure define its core contents. The primary key is L2_LEADLINE_PK_KEY, which uniquely identifies each record representing a lead or a line item within a lead. Typical attributes stored in such a dimension table would include lead source (e.g., web form, trade show), lead status, qualification level, geographic data, assigned sales representative, creation date, and demographic or firmographic information about the lead contact. These columns provide the textual and categorical context for numerical metrics stored in related fact tables.

Common Use Cases and Queries

This table is central to generating analytical reports on lead pipeline health and marketing effectiveness. Common use cases include analyzing lead conversion rates by source, tracking lead aging and status progression, and measuring campaign ROI by attributing revenue to originating leads. A typical analytical query would join this dimension table with a fact table to enrich transactional data.

Sample Query Pattern:
SELECT l.lead_source,
COUNT(f.lead_fact_key) AS lead_count,
SUM(f.revenue_attributed) AS total_revenue
FROM bim.edw_bim_leads_m l
JOIN bim.bim_edw_rvct_mth_f f ON l.l2_leadline_pk_key = f.lead_fk_key
WHERE l.creation_date BETWEEN :p_start_date AND :p_end_date
GROUP BY l.lead_source
ORDER BY total_revenue DESC;

Related Objects

The EDW_BIM_LEADS_M table is a foundational reference object with documented foreign key relationships to several transactional fact tables in the BIM schema. These relationships are critical for accurate dimensional analysis.

  • BIM_EDW_LEADS_F: This fact table references the lead dimension via the foreign key column LEADLINE_FK_KEY, which joins to the primary key (L2_LEADLINE_PK_KEY) of EDW_BIM_LEADS_M. This likely stores granular, transaction-level facts about lead activities.
  • BIM_EDW_RVCT_DLY_F: This daily-level revenue fact table references the lead dimension using the LEAD_FK_KEY column, enabling daily revenue attribution to leads.
  • BIM_EDW_RVCT_MTH_F: This monthly-level revenue fact table also references the lead dimension using the LEAD_FK_KEY column, supporting aggregated monthly revenue analysis by lead attributes.