Results for “bim_edw_cmpfrcst_fstg”

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

Overview

BIM_EDW_CMPFRCST_FSTG is an interface staging table belonging to the BIM – Marketing Intelligence module (documented as obsolete in Oracle EBS 12.1.1 and 12.2.2). Its stated purpose is to serve as the staging interface for the campaign forecast fact, a warehouse-style fact table with a primary key of BIM_EDW_CMPFRCST_FSTG_U, mapped internally to the column CMPFRCST_PK. The table is owned by the BIM schema and, per the ETRM metadata, is not implemented in every database, so its presence depends on the deployment.

From a dimensional modeling perspective, the metadata designates a heuristic Data Vault classification of "satellite-leaning." This is a modeling suggestion rather than a documented constraint. The satellite-leaning designation derives from the fact that the table carries a large number of descriptor and measure attributes (82 documented columns) keyed back to a campaign master, rather than functioning as a pure hub or link. It is worth noting that the table also exhibits a classic fact-table shape, combining foreign-key-like dimension references with financial measures such as CMPGN_ACTUAL_COST_G, CMPGN_FRCST_COST_G, and CMPGN_FRCST_REVENUE_G.

Key Information Stored

The table stores campaign forecast facts along with the interface control columns required for an EBS staging/inbound process. The most significant columns fall into three groups:

• Surrogate primary key: CMPFRCST_PK, the column behind the unique index BIM_EDW_CMPFRCST_FSTG_U. This is the technical identity of each staged row.
• Dimension references (business context): CMPGN_FK / CMPGN_FK_KEY (campaign), EVENT_FK, MEDCHNL_FK (media channel), MKTSGMT_FK (market segment), TGTSGMT_FK (target segment), SRCLST_FK (source list), CUSTOMER_FK, TIME_FK, ORG_FK, and ITEM_FK.
• Measures and currency: CMPGN_ACTUAL_COST_G/B/T, CMPGN_FRCST_COST_G/B/T, and CMPGN_FRCST_REVENUE_G/B/T. The _G, _B, and _T suffixes correspond to global, base, and transaction currency amounts. Currency dimensions are captured through BASE_CURRENCY_FK, GLOBAL_CURRENCY_FK, and TRX_CURRENCY_FK.
• User extensions: USER_MEASURE1 through USER_MEASURE5, USER_FK1 through USER_FK5, and USER_ATTRIBUTE1 through USER_ATTRIBUTE15 provide extensibility for site-specific analytics.
• Interface controls: REQUEST_ID, ROW_ID, OPERATION_CODE, ERROR_CODE, COLLECTION_STATUS, and SECURITY_GROUP_ID support concurrent-program processing, error reporting, and multi-org security.

Common Use Cases and Queries

The principal use case is loading campaign forecast data from an external source or upstream EBS process into the EDW campaign fact. Interface control columns are used to filter only successfully processed rows, for example:

SELECT CMPFRCST_PK, CMPGN_FK, TIME_FK,
       CMPGN_FRCST_REVENUE_G, CMPGN_FRCST_COST_G
FROM   BIM_EDW_CMPFRCST_FSTG
WHERE  COLLECTION_STATUS = 'COMPLETE'
  AND  REQUEST_ID = :p_request_id;

Report writers frequently join the staged fact to EDW_BIM_CMPGNS_M to resolve campaign names:

SELECT m.campaign_name,
       SUM(s.CMPGN_FRCST_REVENUE_G) revenue,
       SUM(s.CMPGN_FRCST_COST_G)    cost
FROM   BIM_EDW_CMPFRCST_FSTG s,
       EDW_BIM_CMPGNS_M m
WHERE  s.CMPGN_FK_KEY = m.cmpgn_pk
GROUP BY m.campaign_name;

Error-triage queries target rows where ERROR_CODE is populated or OPERATION_CODE is not the expected load mode. Because the module is obsolete, most activity is legacy reporting rather than new development.

Related Objects

• EDW_BIM_CMPGNS_M — the campaign master dimension; joined via BIM_EDW_CMPFRCST_FSTG.CMPGN_FK_KEY.
• CS_SYSTEMS_ALL_B_TEMP — referenced through ROW_ID, tying staged rows to the EBS collection/interface framework.
• FND_SECURITY_GROUPS — referenced through SECURITY_GROUP_ID, controlling data access by security group.

Additional campaign and segment master tables (for events, media channels, market segments, and target segments) are implied by the _FK_KEY columns but are not explicitly documented among the foreign keys listed in the ETRM metadata. ETL and concurrent programs that populate this staging table operate through the standard EBS interface tables and request framework.