Search Results l9_tgtsgmt_pk_key

Overview

The EDW_BIM_TGSMT_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. It functions as the master Target Segment dimension, providing a centralized, normalized repository for segment definitions used across marketing analytics. As a dimension table in a data warehousing context, its primary role is to store descriptive attributes for target segments, enabling detailed analysis and reporting of key marketing performance indicators. The table is owned by the BIM schema and is integral to the Enterprise Data Warehouse (EDW) layer of the application, serving as a reference point for numerous transactional fact tables.

Key Information Stored

While the provided metadata does not list specific column names, the table's designation as a dimension table with a primary key named L9_TGTSGMT_PK_KEY indicates its structure. Typically, such a table would store attributes that define a target segment. The primary key column serves as the unique identifier for each segment record. Other columns would likely include descriptive fields such as segment name, description, classification codes, hierarchy levels (e.g., L1 through L9 as suggested by the PK name), effective dates, and status indicators. This structure allows for the consistent categorization and analysis of marketing activities, forecasts, and outcomes by segment.

Common Use Cases and Queries

This table is central to segment-based reporting and analysis in Oracle BIM. Common use cases include analyzing campaign performance by target segment, forecasting revenue by segment, and tracking lead and opportunity generation effectiveness. A typical analytical query would join this dimension table with one or more fact tables to aggregate metrics. For example, to report on the number of leads generated per target segment, a query would join EDW_BIM_TGSMT_M to the BIM_EDW_LEADS_F fact table.

SELECT tgt.segment_name, COUNT(lead.lead_id) AS lead_count
FROM bim.edw_bim_tgsmt_m tgt
JOIN bim.bim_edw_leads_f lead ON tgt.l9_tgtsgmt_pk_key = lead.tgtsgmt_fk_key
WHERE tgt.active_flag = 'Y'
GROUP BY tgt.segment_name;

Another critical use is in data validation and integrity checks, ensuring that foreign key references from fact tables point to valid segment records in this master table.

Related Objects

The EDW_BIM_TGSMT_M table is a foundational object referenced by multiple fact tables within the BIM module, as documented by its foreign key relationships. The primary key L9_TGTSGMT_PK_KEY is referenced by the following transactional fact tables via their respective foreign key columns:

• BIM_EDW_CMPFRCST_F.TGTSGMT_FK_KEY (Campaign Forecast Fact)
• BIM_EDW_EVTFRCST_F.TGTSGMT_FK_KEY (Event Forecast Fact)
• BIM_EDW_INTRCTNS_F.TGTSGMT_FK_KEY (Interactions Fact)
• BIM_EDW_LEADS_F.TGTSGMT_FK_KEY (Leads Fact)
• BIM_EDW_OPRNTIES_F.TGTSGMT_FK_KEY (Opportunities Fact)
• BIM_EDW_RVCT_DLY_F.TGTSGMT_FK_KEY (Revenue Commitment Daily Fact)
• BIM_EDW_RVCT_MTH_F.TGTSGMT_FK_KEY (Revenue Commitment Monthly Fact)

These relationships confirm the table's role as the authoritative source for target segment data, linking it directly to core marketing, sales, and financial forecasting processes within the E-Business Suite data model.