Search Results bim_sgmt_act_mv

Overview

BIM_SGMT_ACT_MV is an APPS-owned materialized view table registered in the FND – Application Object Library product within Oracle EBS 12.1.1 and 12.2.2. Despite its _MV suffix, the object is catalogued as a TABLE in the ETRM repository with a VALID status, indicating it functions as a materialized aggregate storing pre-computed segmentation activity metrics. The 26-column layout, high proportion of _C (calculated/compiled) columns, and presence of DUMMY1_FR through DUMMY6_FR placeholders are characteristic of Oracle's summary management and OLAP-style aggregate materialization pattern, where grouping sets require fixed dummy dimensions to produce stable query rewrite signatures.

From a Data Vault modeling perspective, the table exhibits satellite-like characteristics: it records measurable, time-variant metrics (counts, amounts, responses) keyed against descriptive dimensions. The heuristic classification is therefore satellite-style descriptive snapshot. However, the unique index definition with non-null-mapped (NVL-style) surrogate keys gives it a hub-adjacent role, since the combination SEGMENT_ID, CATEGORY_ID_C, and dummy foreign references effectively forms a business key composite used to enforce uniqueness. Organizations applying Data Vault 2.0 to EBS analytics would typically model this as a downstream sat/aggregate rather than a normative hub.

Key Information Stored

The most operationally significant columns fall into three categories:

• Time/period dimensions: TIME_ID and PERIOD_TYPE_ID anchor each row to a calendar period; these drive the partitioning and slice-and-dice behaviour expected from a summary object.
• Segmentation dimensions: SEGMENT_ID is the primary business-key candidate and carries a foreign key to CSF_TDS_SEGMENTS.SEGMENT_ID, tying metrics to a defined market segment hierarchy. CATEGORY_ID and CATEGORY_ID_C provide the category rollup and its compiled mirror.
• Activity/funnel metrics: ACTIVITIES_COUNT, ACTIVITIES_COUNT_C, RESPONSES, RESPONSES_C, LEADS, and LEADS_C capture top-of-funnel volume; NEW_OPPORTUNITY_AMT, NEW_OPPORTUNITY_AMT_C, NEW_OPPORTUNITY_AMT_S, NEW_OPPORTUNITY_AMT_S_C, BOOKED_AMT, BOOKED_AMT_C, BOOKED_AMT_S, and BOOKED_AMT_S_C capture pipeline and closed revenue in both base and secondary (_S) currency forms.
• Row identifier: ALL_CNT serves as an aggregate count helper, while the unique index I_SNAP$_BIM_SGMT_ACT_MV enforces uniqueness across the SYS_OP_MAP_NONNULL-mapped tuple of SEGMENT_ID, the six dummy foreign references, and CATEGORY_ID_C.

The _C and _S suffixes follow Oracle summary conventions: _C denotes a calculated or compiled companion value, and _S denotes a second-currency equivalent. The _FR columns are foreign-reference placeholder keys, not user data.

Common Use Cases and Queries

Typical usage centres on segmentation analytics and funnel reporting. Common query patterns include:

• Period-over-period pipeline comparison: joining to a time dimension on TIME_ID and grouping by SEGMENT_ID to trend NEW_OPPORTUNITY_AMT against BOOKED_AMT.
• Conversion-rate analysis: deriving responsive and lead-to-booking ratios from RESPONSES, LEADS, ACTIVITIES_COUNT, and amounts from the same row.
• Currency restatement: selecting the _S variants where a reporting ledger or corporate currency is required.
• Materialized view refresh monitoring: checking row counts and last refresh through DBA_MVIEWS and logs to detect stale snapshots.

A representative pattern:

SELECT s.segment_name, m.time_id,
       SUM(m.activities_count)   AS activities,
       SUM(m.responses)          AS responses,
       SUM(m.leads)              AS leads,
       SUM(m.new_opportunity_amt) AS pipeline,
       SUM(m.booked_amt)         AS booked
FROM   bim_sgmt_act_mv m,
       csf_tds_segments s
WHERE  m.segment_id = s.segment_id
AND    m.time_id BETWEEN :start_period AND :end_period
GROUP  BY s.segment_name, m.time_id
ORDER  BY s.segment_name, m.time_id;

Related Objects

The following object groupings are the most significant dependencies and joins:

• CSF_TDS_SEGMENTS — the only documented foreign-key target; joined on SEGMENT_ID to resolve segment names and hierarchy context.
• BIM_SGMT_ACT_MV refresh group members — the base tables and source queries feeding the materialization, typically transaction and activity fact tables that supply the counts and amounts.
• Calendar/time dimension source columns (TIME_ID, PERIOD_TYPE_ID) — linked to the period dimension supporting time-series slicing.
• DBA_MVIEWS / DBA_MVIEW_LOGS — administrative views used to monitor staleness, refresh mode, and rewrite eligibility.
• Category/master reference tables supplying CATEGORY_ID values, joined to enrich the _C compiled columns where the source category name is required.

Because the object is documented as essentially standalone apart from the SEGMENT_ID link, integration effort should focus on the segment dimension and refresh orchestration rather than on a broad web of referential constraints.