Results for “edw_bim_cmpstats_m”

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

Overview

EDW_BIM_CMPSTATS_M is a dimension table owned by the BIM schema within the Oracle E-Business Suite Marketing Intelligence module (BIM). Its documented description identifies it as the "Campaign Status dimension star table," indicating that it functions as a conformed dimension in the marketing analytics star schema. In this role, it supplies descriptive campaign status attributes that qualify measures stored in the surrounding fact tables rather than holding transactional measures of its own.

Under the heuristic Data Vault classification mined from its foreign key structure, EDW_BIM_CMPSTATS_M is characterized as a hub. In Data Vault modeling terms, a hub captures a unique list of business keys associated with a core business concept. This classification is consistent with the table's design: a single surrogate primary key, a stable business key, and descriptive attributes attached externally through satellite-like structures and referencing facts. The classification should be treated as a modeling suggestion derived from the FK topology, not as a formal Data Vault implementation, since the underlying object remains a star-schema dimension.

The module is flagged as obsolete in the ETRM documentation. This status is significant for support and upgrade planning, as it implies the table may be retained for backward compatibility with historical or custom-built analytics objects while no longer receiving active functional enhancement.

Key Information Stored

EDW_BIM_CMPSTATS_M contains 28 documented columns. Its surrogate primary key, enforced by EDW_BIM_CMPSTATS_M_PK, is L1_CMPGN_STATUS_PK_KEY. This warehouse-level key is the column referenced by every dependent fact table, making it the join anchor for reporting queries.

Two unique indexes document the business-key candidates. EDW_BIM_CMPSTATS_M_U1 covers the composite of L1_CMPGN_STATUS_PK and L1_CMPGN_STATUS_PK_KEY, while EDW_BIM_CMPSTATS_M_U2 enforces uniqueness on L1_CMPGN_STATUS_PK_KEY alone. The pair L1_CMPGN_STATUS_PK / L1_CMPGN_STATUS_PK_KEY therefore represents the natural-to-surrogate binding that identifies a campaign status in the source system and its corresponding warehouse surrogate.

The principal descriptive attributes include:

• L1_CMPGN_STATUS_NAME and L1_NAME — display labels for the campaign status used in reports and drill-downs.
• L1_CMPGN_STATUS_ID — the source system identifier for the status.
• L1_SYSTEM_STATUS_CODE and L1_SYSTEM_STATUS_TYPE — the operational code and classification of the status.
• L1_ENABLED_FLAG, L1_DEFAULT_FLAG, and L1_SEEDED_FLAG — control flags indicating whether the status is active, the default value, or delivered as seed data.
• L1_START_DATE_ACTIVE and L1_END_DATE_ACTIVE — the effective-dating window of the status definition.
• L1_DESCRIPTION — free-text explanation of the status.
• L1_INSTANCE — the source instance from which the status was extracted.
• L1_USER_ATTRIBUTE1 through L1_USER_ATTRIBUTE5 — extensibility columns for customer-defined attributes.
• SECURITY_GROUP_ID, CREATION_DATE, and LAST_UPDATE_DATE — multi-tenant security scoping and audit tracking.

The L0_ALL_PK_KEY, L0_ALL_PK, and L0_NAME columns provide the highest level of the conformed "All" hierarchy, enabling aggregate reporting across all campaign statuses.

Common Use Cases and Queries

The primary use case is dimensional enrichment: joining campaign-status facts to this table to translate surrogate keys into reportable labels and codes. A typical pattern joins a fact table to the dimension on the warehouse key.

• Campaign forecast reporting by status, joining BIM_EDW_CMPFRCST_F.CMPGN_STATUS_FK_KEY to L1_CMPGN_STATUS_PK_KEY.
• Interaction and lead analysis broken down by campaign status, joining BIM_EDW_INTRCTNS_F or BIM_EDW_LEADS_F to the same key.
• Revenue trend analysis by campaign status, joining BIM_EDW_RVCT_DLY_F or BIM_EDW_RVCT_MTH_F, useful for comparing daily versus monthly revenue attribution.
• Opportunity and event forecast reporting via BIM_EDW_OPRNTIES_F and BIM_EDW_EVTFRCST_F.

A representative query fragment filters on the enabled flag and active window to restrict reporting to currently valid statuses: SELECT d.L1_CMPGN_STATUS_NAME, SUM(f.measure) FROM bim_edw_cmpfrcst_f f JOIN edw_bim_cmpstats_m d ON f.cmpgn_status_fk_key = d.l1_cmpgn_status_pk_key WHERE d.l1_enabled_flag = 'Y' AND SYSDATE BETWEEN d.l1_start_date_active AND NVL(d.l1_end_date_active, SYSDATE) GROUP BY d.L1_CMPGN_STATUS_NAME;

Because the table is obsolete and security-scoped, queries should also respect SECURITY_GROUP_ID and avoid assuming ongoing population by active concurrent programs.

Related Objects

EDW_BIM_CMPSTATS_M is referenced by seven documented fact tables through the CMPGN_STATUS_FK_KEY column, which maps to its primary key L1_CMPGN_STATUS_PK_KEY:

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

In the opposite direction, EDW_BIM_CMPSTATS_M references FND_SECURITY_GROUPS through its SECURITY_GROUP_ID column, tying dimension rows to Oracle EBS security group definitions. This relationship governs data visibility in multi-organization and multi-instance deployments and should be considered whenever status values appear missing from a report.