Search Results bim_sgmt_val_f_mv

Overview

BIM_SGMT_VAL_F_MV is a materialized view owned by the APPS schema in Oracle E-Business Suite, registered under the FND – Application Object Library product. The object's name and its "_MV" suffix confirm its role as a pre-aggregated, denormalized snapshot of segment-level value metrics. In ETRM terms, this is a fact-style table that stores quantified business measures — customers acquired, lost, and retained, segment size, booked amounts, and revenue — organized across time periods and segments. From the heuristic Data Vault classification mined from its FK structure, the object is standalone, with a single documented foreign key to CSF_TDS_SEGMENTS via SEGMENT_ID. Given that SEGMENT_ID behaves as a reference to a master dimension rather than a transactional parent, a modeling suggestion would be to treat it as a satellite or dimension-adjacent fact rather than a hub or link. In the EBS 12.1.1 / 12.2.2 data model, such materialized views typically back Oracle Marketing and segmentation analytics, providing fast access to pre-computed segment metrics without re-executing the underlying aggregation.

Key Information Stored

The view exposes 28 documented columns, whose naming pattern distinguishes raw measures from their calculated or currency-adjusted counterparts. The most operationally significant columns include:

• TIME_ID and PERIOD_TYPE_ID — the time dimension keys that define the reporting period and its grain (e.g., monthly, quarterly).
• SEGMENT_ID — the foreign key to CSF_TDS_SEGMENTS and the primary business grain of the row.
• ACQUIRED_CUSTOMERS, TOTAL_CUSTOMERS, LOST_CUSTOMERS, SEGMENT_SIZE — customer population measures.
• BOOKED_AMT, BOOKED_AMT_S, BOOKED_COUNT — booking value in functional and secondary currency, plus booking counts.
• REVENUE, REVENUE_S, REVENUE_S_C — revenue in functional and secondary currency, with calculated variants.
• DUMMY1_FR through DUMMY6_FR — placeholder columns used by the materialized view's unique index build.
• ALL_C — a calculated or "all" aggregate flag/measure used in roll-up reporting.

The documented unique index I_SNAP$_BIM_SGMT_VAL_F_MV is built over SYS_OP_MAP_NONNULL expressions of SEGMENT_ID and the six DUMMY columns, which is characteristic of a fast-refresh materialized view with a surrogate key component. No separate primary key column is documented; SEGMENT_ID combined with the dummy columns effectively constitutes the business-key candidate.

Common Use Cases and Queries

Typical usage is analytical: segment performance dashboards, campaign ROI analysis, and customer acquisition/loss trending. A representative query joins the view to CSF_TDS_SEGMENTS and to the time dimension:

• Segment revenue trend: SELECT s.segment_name, t.calendar_month, v.REVENUE, v.REVENUE_S FROM BIM_SGMT_VAL_F_MV v, CSF_TDS_SEGMENTS s, FND_CALENDAR... t WHERE v.SEGMENT_ID = s.segment_id AND v.TIME_ID = t.time_id;
• Acquisition vs. loss: compare ACQUIRED_CUSTOMERS against LOST_CUSTOMERS by PERIOD_TYPE_ID to derive net growth.
• Currency-agnostic reporting using BOOKED_AMT_S and REVENUE_S to bypass functional-currency noise.
• Netting SEGMENT_SIZE against TOTAL_CUSTOMERS to validate population counts.

Because the object is a materialized view, queries should assume refresh-lag; concurrent program schedules determine data currency.

Related Objects

The documented foreign key establishes CSF_TDS_SEGMENTS as the primary reference table. Additional significant dependencies include:

• CSF_TDS_SEGMENTS — joined on SEGMENT_ID, supplies segment master attributes.
• Time/period dimension tables referenced by TIME_ID and PERIOD_TYPE_ID.
• BIM_SGMT_VAL_F (base fact) — the underlying source the materialized view aggregates.
• CSF-related segmentation views that consume the same segment grain.
• FND concurrent refresh programs that maintain the materialized view.

These relationships make the view the natural consolidation point for segment-level customer and revenue analytics in Oracle EBS.