Search Results edw_bim_leads_m_u1

Overview

The BIM.EDW_BIM_LEADS_M table is a core dimensional table in the Oracle EBS Business Intelligence (BIM) data warehouse architecture, specifically targeting sales force automation and lead management analytics. Operating under the BIM schema with APPS_TS_ARCHIVE tablespace, this table serves as the master dimension for lead line items within the Oracle EBS 12.1.1/12.2.2 environment. It stores conformed dimensions that enable enterprise-wide reporting on sales leads by providing surrogate-key-based hierarchical relationships and lookup attributes. The table is designated as "Oracle Internal Use Only," indicating it is not intended for direct custom development but is integral to the standard Oracle Business Intelligence Applications (OBIA) ETL processes that populate the EDW (Enterprise Data Warehouse) layer.

Key Information Stored

The table stores granular lead line information with a system-generated surrogate key architecture. At its core, L2_LEADLINE_PK_KEY (NUMBER, MANDATORY) serves as the primary key and unique identifier for each lead line record. The hierarchical dimensional model is evident through the following key columns:

• L1_LEAD_PK_KEY (NUMBER): Surrogate key linking to the parent lead dimension level.
• L0_ALL_PK_KEY (NUMBER) and L0_ALL_PK (VARCHAR2(40)): Top-level All dimension identifiers for aggregated reporting.
• L1_LEAD_PK (VARCHAR2(240)) and L1_LEAD_DP (VARCHAR2(240)): Business key and display column for unique lead identification in Discoverer-based reporting.
• L1_SALES_LEAD_ID (NUMBER): The OLTP surrogate key from the source system (Oracle Telesales/CRM).
• L1_LEAD_NUMBER (VARCHAR2(240)): The human-readable unique lead number assigned in the operational system.
• L1_TOTAL_SCORE (NUMBER) and L1_BUDGET_AMOUNT (NUMBER): Quantitative metrics for lead scoring and budget tracking.
• L1_VEHICLE_RESPONSE_CODE (VARCHAR2(240)): Categorizes the lead creation method (e.g., web, phone, campaign).
• L1_PARENT_PROJECT (VARCHAR2(240)): Links the lead to a parent project for project-based sales analysis.

Several columns including L1_ASSIGN_DATE, L1_INITIATING_CONTACT_ID, and L1_SCORECARD_ID are marked as "Not used" but remain in the structure for backward compatibility or future extensions.

Common Use Cases and Queries

This table is central to lead pipeline analytics in Oracle EBS BI. Typical use cases include:

• Lead Pipeline Analysis: Aggregating lead volumes and scores by lead number, source code, and budget amounts across time periods.
• Sales Funnel Reporting: Joining with fact tables to measure conversion rates—e.g., counting leads by L1_VEHICLE_RESPONSE_CODE while filtering by L1_BUDGET_AMOUNT thresholds.
• Data Quality & Deduplication: Using the unique indexes EDW_BIM_LEADS_M_U1 (on L2_LEADLINE_PK composite) and EDW_BIM_LEADS_M_U2 (on L2_LEADLINE_PK_KEY) to identify and remove duplicate lead lines.

Sample SQL patterns include:
-- Retrieve distinct leads with their total scores
SELECT L1_LEAD_NUMBER, L1_TOTAL_SCORE, L1_VEHICLE_RESPONSE_CODE FROM BIM.EDW_BIM_LEADS_M WHERE L1_TOTAL_SCORE > 0 ORDER BY L1_TOTAL_SCORE DESC;
-- Join with fact table to analyze daily revenue by lead
SELECT m.L1_LEAD_NUMBER, f.REVENUE_AMOUNT FROM BIM.EDW_BIM_LEADS_M m, BIM.BIM_EDW_RVCT_DLY_F f WHERE m.L2_LEADLINE_PK_KEY = f.LEAD_FK_KEY;

Related Objects

Based on the documented ETRM metadata, the table participates in the following primary-foreign key relationships:

• BIM.BIM_EDW_LEADS_F (Fact table for leads metrics): Foreign key LEADLINE_FK_KEY references EDW_BIM_LEADS_M.L2_LEADLINE_PK_KEY.
• BIM.BIM_EDW_RVCT_DLY_F (Daily revenue/contact fact): Foreign key LEAD_FK_KEY references EDW_BIM_LEADS_M.L2_LEADLINE_PK_KEY.
• BIM.BIM_EDW_RVCT_MTH_F (Monthly revenue/contact fact): Foreign key LEAD_FK_KEY references EDW_BIM_LEADS_M.L2_LEADLINE_PK_KEY.

The table also has a well-defined primary key constraint EDW_BIM_LEADS_M_PK on column L2_LEADLINE_PK_KEY, enforced via the unique index EDW_BIM_LEADS_M_U2. No direct APIs or views are documented in the metadata, but the table is implicitly consumed by Oracle BI Applications repository metadata (e.g., in DAC and OBIEE repositories) for ETL loads from source tables like AS_SALES_LEADS_ALL or AS_LEAD_LINES from the CRM module.