Search Results bim_edw_leads_f




Overview
Explore in AI Chat

Overview

The BIM_EDW_LEADS_F table is a central fact table within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2, specifically architected for the now-obsolete Marketing Intelligence (BIM) module. As a fact table in a dimensional data model, its primary role is to store quantitative and measurable data related to marketing and sales leads. It serves as the core repository for lead-related transactional facts, enabling historical analysis and performance reporting by linking to numerous dimension tables via foreign keys. The table's design facilitates complex analytical queries to track lead generation, progression, and conversion metrics across various marketing dimensions such as campaigns, channels, and customer segments.

Key Information Stored

While the specific column list is not detailed in the provided metadata, the structure is defined by its foreign key relationships. The table stores fact data, which typically includes measurable metrics like lead counts, scores, response times, and associated revenue amounts. The core of its data model consists of foreign key columns that link to dimension master tables. Key foreign key columns include CMPGN_FK_KEY (Campaign), LEADLINE_FK_KEY (Lead), LEADSTATUS_FK_KEY (Lead Status), MDCHNL_FK_KEY (Marketing Channel), and OFFER_FK_KEY (Offer). Additional keys link to operational dimensions like CUSTOMER_FK_KEY, ITEM_FK_KEY, ORG_FK_KEY (Inventory Organization), and site details (BILL_TO_SITE_FK_KEY, SHIP_TO_SITE_FK_KEY), integrating marketing lead data with broader EBS transactional data.

Common Use Cases and Queries

This table is utilized for generating historical and trend-based marketing performance reports. Common analytical use cases include measuring campaign effectiveness by lead volume and quality, analyzing lead conversion rates by marketing channel or target segment, and tracking the lead pipeline through various statuses. A typical query would join the fact table to its dimension tables to aggregate lead metrics.

Sample Query Pattern:
SELECT c.Campaign_Name, s.Lead_Status, COUNT(f.LEADLINE_FK_KEY) AS Lead_Count
FROM BIM_EDW_LEADS_F f
JOIN EDW_BIM_CMPGNS_M c ON f.CMPGN_FK_KEY = c.
JOIN EDW_BIM_LEAD_STATUS_M s ON f.LEADSTATUS_FK_KEY = s.
WHERE f.CREATION_DATE BETWEEN :P_START_DATE AND :P_END_DATE
GROUP BY c.Campaign_Name, s.Lead_Status;
(Note: Actual dimension key column names are not specified in the metadata.)

Related Objects

The BIM_EDW_LEADS_F table has a defined star schema relationship with multiple dimension tables, as documented by its foreign keys. The following are the specific, documented foreign key relationships from the provided metadata:

Additional foreign keys (e.g., CUSTOMER_FK_KEY, ITEM_FK_KEY, ORG_FK_KEY) indicate relationships to standard EBS dimension tables, though the specific target tables are not fully qualified in the provided excerpt.