Search Results edw_bim_cmpstats_m




Overview
Explore in AI Chat

Overview

The EDW_BIM_CMPSTATS_M table is a dimension table within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 environments, specifically architected for the Enterprise Data Warehouse (EDW) layer. It functions as the Campaign Status dimension star table for the Marketing Intelligence (BIM) module. Its primary role is to provide a centralized, normalized reference for all valid campaign status codes and their descriptive attributes, enabling consistent dimensional analysis across multiple marketing fact tables. It is critical to note that the BIM - Marketing Intelligence module is documented as obsolete, indicating this table is part of a legacy analytical framework that may have been superseded by newer Oracle solutions, such as Oracle Business Intelligence Applications (OBIA) or Oracle Fusion Analytics.

Key Information Stored

As a classic dimension table in a star schema, EDW_BIM_CMPSTATS_M stores master data for campaign statuses. While the specific column list is not detailed in the provided metadata, the structure of such a table can be inferred. It is anchored by a primary key column, likely named L1_CMPGN_STATUS_PK_KEY or similar, which serves as the unique surrogate key for joining to fact tables. Typical columns would include a natural key from the source transactional system (e.g., STATUS_CODE), descriptive fields (STATUS_NAME, DESCRIPTION), and attributes for data warehousing such as effective start/end dates for tracking historical changes (Type 2 SCD), creation dates, and last update dates. The table's purpose is to translate a status code into a business-friendly label for reporting.

Common Use Cases and Queries

This table is used exclusively for analytical reporting and data warehouse queries. Common use cases include generating campaign performance dashboards segmented by status, analyzing the pipeline of leads and opportunities based on campaign status, and calculating historical trends for campaigns in specific statuses like "Active," "Completed," or "Cancelled." A typical analytical query would join this dimension to one or more fact tables to enrich fact records with status descriptions.

Sample SQL Pattern:

Related Objects

The EDW_BIM_CMPSTATS_M table is a central reference point for numerous fact tables within the obsolete BIM EDW schema, as evidenced by its foreign key relationships. The documented related objects are all fact tables that use the campaign status dimension as a foreign key:

These relationships confirm its integral role in the dimensional model for analyzing marketing forecasts, interactions, lead generation, opportunity management, and revenue tracking.