Results for “l9_description”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

EDW_BIM_TGSMT_M is a target segment dimension table owned by the BIM schema and delivered as part of the Oracle E-Business Suite Marketing Intelligence (BIM) product family. It functions as the master reference for target segments — the market segments against which marketing campaigns, leads, opportunities, forecasts, and revenue are tracked and analyzed. In 12.1.1 and 12.2.2 environments, this object is an engineered warehouse (EDW) construct rather than a transactional Marketing table, meaning it is populated and consumed by the Marketing Intelligence extraction, transformation, and load processes and is intended for dimensional reporting and analytics across the marketing lifecycle.

The table is classified as VALID, with 161 documented columns in the 12.2.2 physical schema. Based on the heuristic Data Vault classification mined from its foreign key structure, EDW_BIM_TGSMT_M is best modeled as a hub: it carries a stable surrogate key (L9_TGTSGMT_PK_KEY) that is referenced by many downstream fact-style tables, and it holds descriptive segment attributes rather than transactional measures.

Key Information Stored

The table's primary key is EDW_BIM_TGSMT_M_PK, defined on L9_TGTSGMT_PK_KEY. Two unique indexes provide business-key candidates: EDW_BIM_TGSMT_M_U1 covers (L9_TGTSGMT_PK, L9_TGTSGMT_PK_KEY) and EDW_BIM_TGSMT_M_U2 covers L9_TGTSGMT_PK_KEY alone, confirming L9_TGTSGMT_PK_KEY as the principal surrogate identifier.

The most significant columns include:

• L9_TGTSGMT_PK_KEY — surrogate primary key for the target segment dimension.
• L9_TGTSGMT_PK, L9_TGTSGMT_ID, L9_TGTSGMT_CODE — the natural source identifiers and code for the target segment.
• L9_TGTSGMT_NAME, L9_NAME, L9_DESCRIPTION — descriptive labels used in reporting and dashboards.
• L9_MARKET_SEGMENT_FLAG — indicates whether the record represents a market segment.
• L9_ENABLED_FLAG — active/inactive status controlling availability to campaigns.
• L9_ORIGINAL_SIZE — the size of the target segment, typically used in campaign planning metrics.
• L9_INSTANCE — source instance discriminator for multi-instance staging.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, supporting data security and multi-org visibility.
• L9_LAST_UPDATE_DATE, L9_CREATION_DATE, LAST_UPDATE_DATE, CREATION_DATE — audit timestamps used to detect incremental changes during ETL.

The L1 through L8 prefix groups replicate the same attribute set at multiple hierarchy levels, reflecting a flattened segment hierarchy used for roll-up reporting. The five L*n*_USER_ATTRIBUTE columns per level provide extensibility for customer-defined segment characteristics.

Common Use Cases and Queries

EDW_BIM_TGSMT_M is primarily queried for target segment master data extraction and for joining to marketing fact tables. Typical SQL patterns include:

• Looking up a segment by code or name: SELECT L9_TGTSGMT_PK_KEY, L9_TGTSGMT_NAME, L9_ENABLED_FLAG FROM BIM.EDW_BIM_TGSMT_M WHERE L9_TGTSGMT_CODE = :code;
• Extracting only active segments for campaigns: ... WHERE L9_ENABLED_FLAG = 'Y' AND L9_MARKET_SEGMENT_FLAG = 'Y';
• Incremental loads based on audit columns: ... WHERE LAST_UPDATE_DATE > :last_run_date;
• Joining to fact-style tables on TGTSGMT_FK_KEY to report campaign, lead, opportunity, forecast, and revenue performance by segment.

Typical reporting uses include target segment performance analysis, segment-level revenue (daily and monthly revenue via RVCT_DLY_F and RVCT_MTH_F), opportunity conversion by segment, forecast attainment, and lead source attribution.

Related Objects

EDW_BIM_TGSMT_M is referenced by seven principal fact-style tables via the TGTSGMT_FK_KEY column, forming the core star-schema relationships:

• BIM_EDW_CMPFRCST_F.TGTSGMT_FK_KEY — campaign forecast facts.
• BIM_EDW_EVTFRCST_F.TGTSGMT_FK_KEY — event forecast facts.
• BIM_EDW_INTRCTNS_F.TGTSGMT_FK_KEY — interaction facts.
• BIM_EDW_LEADS_F.TGTSGMT_FK_KEY — lead facts.
• BIM_EDW_OPRNTIES_F.TGTSGMT_FK_KEY — opportunity facts.
• BIM_EDW_RVCT_DLY_F.TGTSGMT_FK_KEY — daily revenue facts.
• BIM_EDW_RVCT_MTH_F.TGTSGMT_FK_KEY — monthly revenue facts.

The table also holds a foreign key to FND_SECURITY_GROUPS through SECURITY_GROUP_ID, tying segment visibility to Oracle's security group framework. The FK column name TGTSGMT_FK_KEY differs from the referenced PK column (L9_TGTSGMT_PK_KEY), which is a common EDW convention for distinguishing surrogate keys in fact tables.