Results for “uom_fk_key”

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

Overview

BIM.BIM_EDW_EVTFRCST_F is a fact table within the Oracle E-Business Suite Marketing Intelligence (BIM) product family, specifically the Event Forecasting subject area of the Marketing EDW. It stores forecast and actual metrics for marketing events, campaigns, offers, and related targeting and source-list dimensions. The table is documented as VALID in the BIM schema and contains 63 columns in the 12.2.2 physical schema.

In Oracle EBS, this object serves as the analytical backbone for event ROI, cost, revenue, and attendance reporting. It is not a transactional table; it is populated by the Marketing EDW extraction and load processes that stage source data from CRM/Marketing transactional tables into denormalized dimension and fact structures.

From a Data Vault modeling perspective, the FK topology — a composite primary key (EVTFRCST_PK) with multiple foreign keys to dimension master tables and a mix of measures and descriptive attributes — suggests a link classification. The table behaves as a transaction/link fact connecting event, campaign, customer, item, organization, and time dimensions, rather than functioning as a standalone hub or a pure descriptive satellite.

Key Information Stored

The table carries a surrogate primary key plus a set of foreign keys to dimension master tables, together with numeric facts and user-defined extensibility columns. The most significant columns include:

• EVTFRCST_PK_KEY and EVTFRCST_PK — the surrogate and natural/business keys. The unique index BIM_EDW_EVTFRCST_F_U1 is defined on EVTFRCST_PK, identifying the business key candidate for a single event-forecast row.
• EVENT_FK_KEY — foreign key to EDW_BIM_EVENTS_M, identifying the marketing event.
• CMPGN_FK_KEY and CMPGN_STATUS_FK_KEY — foreign keys to EDW_BIM_CMPGNS_M and EDW_BIM_CMPSTATS_M, tying the fact to a campaign and its status.
• OFFER_FK_KEY, SRCLST_FK_KEY, MDCHNL_FK_KEY — foreign keys to EDW_BIM_OFFERS_M, EDW_BIM_SRCLSTS_M, and EDW_BIM_MDCHN_M for offer, source list, and marketing channel.
• MKTSGMT_FK_KEY, TGTSGMT_FK_KEY — foreign keys to EDW_BIM_MKTSGMTS_M and EDW_BIM_TGSMT_M for market and target segment context.
• EVENT_FRCST_COST_G, EVENT_FRCST_COST_B, EVENT_FRCST_COST_T — forecast cost measures in global, base, and transaction currency.
• EVENT_FRCST_REVENUE_G, EVENT_FRCST_REVENUE_B, EVENT_FRCST_REVENUE_T — forecast revenue measures across the three currency bases.
• EVENT_ACTUAL_COST_G, EVENT_ACTUAL_COST_B, EVENT_ACTUAL_COST_T — actual event cost measures for variance analysis.
• EVENT_NUMBER_AVAILABLE, EVENT_NUMBER_ENROLLED, EVENT_NUMBER_ATTENDED, EVENT_NUMBER_CANCELLED — attendance and enrollment volume metrics.
• CUSTOMER_FK_KEY, ITEM_FK_KEY, ORG_FK_KEY, TIME_FK_KEY — foreign keys to customer, item, inventory organization, and time dimensions.
• USER_MEASURE1–USER_MEASURE5 and USER_ATTRIBUTE1–USER_ATTRIBUTE15 — extensibility slots for customer-specific metrics and descriptive values.
• SECURITY_GROUP_ID — references FND_SECURITY_GROUPS and enforces Multi-Org / security group access control on forecast rows.

Common Use Cases and Queries

The dominant use cases are event forecasting, campaign ROI analysis, and funnel/attendance reporting in Marketing Intelligence dashboards and Discoverer/BI Publisher reports. Typical workloads compare forecast versus actual cost and revenue, aggregate attendance metrics by event or campaign, and slice metrics by channel, offer, or target segment.

• Forecast vs. actual variance by event: join to EDW_BIM_EVENTS_M on EVENT_FK_KEY, grouping by event name, summing EVENT_FRCST_REVENUE_B and EVENT_ACTUAL_COST_B.
• Campaign performance analysis: join to EDW_BIM_CMPGNS_M on CMPGN_FK_KEY and to EDW_BIM_CMPSTATS_M on CMPGN_STATUS_FK_KEY, then aggregate forecast revenue and attendance across campaigns or offer sources.
• Channel effectiveness reporting: join to EDW_BIM_MDCHN_M on MDCHNL_FK_KEY to compare forecast cost and revenue per marketing channel.
• Segment and offer performance: join to EDW_BIM_MKTSGMTS_M, EDW_BIM_TGSMT_M, EDW_BIM_OFFERS_M, or EDW_BIM_SRCLSTS_M to evaluate forecasts by audience and offer.
• Multi-currency normalization: use the _G, _B, and _T measure pairs for global, base, and transactional currency reporting; join to the currency dimension via BASE_CURRENCY_FK_KEY, GLOBAL_CURRENCY_FK_KEY, and TRX_CURRENCY_FK_KEY.

A representative query pattern joins the fact to the campaign and event dimensions, filters by security group, and summarizes forecast revenue versus actual cost:

SELECT e.event_name, c.campaign_name,
       SUM(f.event_frcst_revenue_b) AS forecast_revenue,
       SUM(f.event_actual_cost_b)   AS actual_cost,
       SUM(f.event_frcst_revenue_b) - SUM(f.event_actual_cost_b) AS variance
FROM   bim.bim_edw_evtfrcst_f f,
       edw_bim_events_m        e,
       edw_bim_cmpgns_m        c
WHERE  f.event_fk_key = e.event_fk_key
AND    f.cmpgn_fk_key  = c.cmpgn_fk_key
AND    f.security_group_id = :p_security_group
GROUP BY e.event_name, c.campaign_name;

Related Objects

The following dimension and reference objects are the most significant dependencies, joined through the documented foreign keys:

• EDW_BIM_EVENTS_M — joined on BIM_EDW_EVTFRCST_F.EVENT_FK_KEY; supplies event attribute context.
• EDW_BIM_CMPGNS_M — joined on CMPGN_FK_KEY; supplies campaign descriptors.
• EDW_BIM_CMPSTATS_M — joined on CMPGN_STATUS_FK_KEY; supplies campaign status.
• EDW_BIM_OFFERS_M — joined on OFFER_FK_KEY; supplies offer detail.
• EDW_BIM_SRCLSTS_M — joined on SRCLST_FK_KEY; source list used for event targeting.
• EDW_BIM_MDCHN_M — joined on MDCHNL_FK_KEY; marketing channel definition.
• EDW_BIM_MKTSGMTS_M and EDW_BIM_TGSMT_M — joined on MKTSGMT_FK_KEY and TGTSGMT_FK_KEY; market and target segment classifications.
• FND_SECURITY_GROUPS — referenced by SECURITY_GROUP_ID to enforce Multi-Org security on forecast rows.
• Additional dimensional FKs (CUSTOMER_FK_KEY, ITEM_FK_KEY, ORG_FK_KEY, TIME_FK_KEY, site, instance, channel, and currency keys) link the fact to the standard BIM star-schema dimensions resolved at load time.

Together, these relationships make BIM_EDW_EVTFRCST_F the central fact object for event-level marketing forecast analytics in the Oracle EBS Marketing Intelligence EDW.