Search Results edw_bim_mktsgmts_m_pk

Overview

The EDW_BIM_MKTSGMTS_M table is a core dimension table within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2, specifically for the Marketing Intelligence (BIM) module. As a dimension table in the Enterprise Data Warehouse (EDW) layer, its primary role is to provide a centralized, master definition of market segments used for analytical reporting and performance measurement. It serves as the authoritative source for market segment descriptive attributes, enabling consistent categorization and segmentation of marketing data across various transactional fact tables. This table is fundamental for building dimensional models that support analyses of campaign forecasts, lead generation, opportunity pipelines, and customer interactions from a market segmentation perspective.

Key Information Stored

While the provided metadata does not list specific columns, the structure of a typical EDW dimension table in this context can be inferred. The table's primary key is L1_MKTSGMT_PK_KEY, which is a surrogate key used for efficient joins and to track historical changes (Type 2 Slowly Changing Dimensions). The table likely contains descriptive attributes for each market segment, which may include a unique segment code, a descriptive name, hierarchical levels (e.g., L1, L2), effective start and end dates for historical tracking, and a current flag. The key column referenced by all foreign keys is this primary key, stored as MKTSGMT_FK_KEY in the related fact tables, establishing the link between transactional metrics and their segment classification.

Common Use Cases and Queries

This table is central to any BI report or analysis that segments marketing performance. Common use cases include analyzing lead conversion rates by market segment, measuring campaign forecast accuracy per segment, and evaluating revenue contribution across different customer segments over time. A typical analytical query would join this dimension table to one or more fact tables to aggregate metrics.

Sample Query Pattern:
SELECT m.MKTSGMT_NAME, SUM(f.FORECAST_AMOUNT)
FROM BIM_EDW_CMPFRCST_F f
JOIN EDW_BIM_MKTSGMTS_M m ON f.MKTSGMT_FK_KEY = m.L1_MKTSGMT_PK_KEY
WHERE m.CURRENT_FLAG = 'Y'
GROUP BY m.MKTSGMT_NAME;
This query would summarize forecast amounts by current market segment name.

Related Objects

As documented, the EDW_BIM_MKTSGMTS_M table is a primary dimension referenced by several key transactional fact tables in the BIM schema. These foreign key relationships define its critical integration points:

• BIM_EDW_CMPFRCST_F: Campaign Forecast fact table.
• BIM_EDW_EVTFRCST_F: Event Forecast fact table.
• BIM_EDW_INTRCTNS_F: Customer Interactions fact table.
• BIM_EDW_LEADS_F: Leads fact table.
• BIM_EDW_OPRNTIES_F: Opportunities fact table.
• BIM_EDW_RVCT_DLY_F & BIM_EDW_RVCT_MTH_F: Revenue Commitment fact tables at daily and monthly granularity.

The table's primary key constraint, EDW_BIM_MKTSGMTS_M_PK, ensures referential integrity for these relationships. It is the central hub for market segment data within the BIM data warehouse structure.