Results for “offer_fk_key”

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

Overview

BIM_EDW_INTRCTNS_F is the interaction fact table in the Oracle E-Business Suite Marketing Intelligence (BIM) module, owned by the BIM schema. In Oracle EBS 12.1.1 and 12.2.2, it serves as the central warehouse-style repository where individual marketing interactions — clicks, responses, touches, and campaign activities — are captured and quantified. Because it records measurable events tied to both time and business dimensions, it functions as the analytical spine of marketing performance reporting, feeding such metrics as interaction counts, durations, campaign responses, and outcome analysis.

MetaLink/ETRM classifies this object as VALID with 54 documented columns. The heuristic Data Vault classification mined from its foreign-key structure is link. This is consistent with an intersection fact that binds multiple dimension keys (campaign, offer, customer, channel, time, and demographic attributes) into a single transactional record. Treating the table as a Data Vault link is a modeling suggestion rather than a physical enforcement; in practice it behaves as a Kimball-style fact table with a composite grain at the interaction level.

The row is uniquely identified by the unique index BIM_EDW_INTRCTNS_F_U1 on the INTERACTION_PK business-key column.

Key Information Stored

The table stores one row per recorded marketing interaction. The most significant columns are:

• INTERACTION_PK_KEY — surrogate primary key used for physical joins from dependent detail tables.
• INTERACTION_PK — business-key candidate, enforced by unique index BIM_EDW_INTRCTNS_F_U1.
• INTERACTION_ID and ACTIVITY_ID — the source-system interaction and activity identifiers.
• CUSTOMER_FK_KEY — references the customer/party for whom the interaction was recorded.
• CMPGN_FK_KEY and CMPGN_STATUS_FK_KEY — campaign and its status at interaction time.
• OFFER_FK_KEY — the offer presented or accepted during the interaction.
• MDCHNL_FK_KEY — marketing channel through which the interaction occurred.
• EVENT_FK_KEY — the event type that triggered or recorded the interaction.
• TIME_FK_KEY — the time-dimension key for trend and period analysis.
• BILL_TO_SITE_FK_KEY and SHIP_TO_SITE_FK_KEY — site-level location context.
• ACTIVITY_DURATION, ACTIVITY_START_TIME, and ACTIVITY_END_TIME — quantitative duration measures.
• INTRCTN_MEDIA_FK_KEY, INTRCTN_OUTCOME_FK_KEY, INTRCTN_REASON_FK_KEY, and INTRCTN_RESULT_FK_KEY — the media, outcome, reason, and result classifications.
• SECURITY_GROUP_ID — references FND_SECURITY_GROUPS for multi-org / data-security filtering.

The remaining columns consist largely of USER_MEASURE1..5, USER_FK1..5_KEY, and USER_ATTRIBUTE1..15 extensibility fields, plus CREATION_DATE and LAST_UPDATE_DATE audit columns.

Common Use Cases and Queries

BI publishers and custom reports typically aggregate interactions by campaign, channel, offer, and time to produce response rates, touch counts, and duration metrics. A representative pattern joins the campaign, channel, and time dimensions:

• SELECT c.campaign_name, m.media_name, SUM(f.activity_duration) interactions FROM bim_edw_intrctns_f f JOIN edw_bim_cmpgns_m c ON f.cmpgn_fk_key = c.cmpgn_fk_key JOIN edw_bim_ih_media_m m ON f.intrctn_media_fk_key = m.intrctn_media_fk_key GROUP BY c.campaign_name, m.media_name;
• Campaign attribution and ROI analysis by outcome: filter on INTRCTN_OUTCOME_FK_KEY joined to EDW_BIM_IH_OUTCM_M.
• Trend reporting by joining TIME_FK_KEY to the time dimension and grouping by period.
• Customer-level drill-down using CUSTOMER_FK_KEY, combined with security filtering on SECURITY_GROUP_ID.

Because the table is a fact, queries should aggregate rather than return raw rows, and joins should consistently use the FK_KEY surrogate columns rather than business IDs.

Related Objects

The table’s referential integrity is defined through the following documented foreign-key relationships:

• EDW_BIM_CMPGNS_M via CMPGN_FK_KEY
• EDW_BIM_CMPSTATS_M via CMPGN_STATUS_FK_KEY
• EDW_BIM_EVENTS_M via EVENT_FK_KEY
• EDW_BIM_IH_MEDIA_M, EDW_BIM_IH_OUTCM_M, EDW_BIM_IH_REASN_M, and EDW_BIM_IH_RESLT_M via the interaction media, outcome, reason, and result FK columns
• EDW_BIM_MDCHN_M via MDCHNL_FK_KEY
• EDW_BIM_MKTSGMTS_M and EDW_BIM_TGSMT_M via segmentation keys
• EDW_BIM_OFFERS_M and EDW_BIM_SRCLSTS_M via OFFER_FK_KEY and SRCLST_FK_KEY
• FND_SECURITY_GROUPS via SECURITY_GROUP_ID

These dimension masters supply the descriptive attributes that make the fact table analytically usable in Marketing Intelligence dashboards and custom ETL pipelines.