Results for “biv_res_sum_mv”

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

Overview

BIV_RES_SUM_MV is an Oracle E-Business Suite materialized view residing in the APPS schema and registered under the FND — Application Object Library product. Despite its classification in the data dictionary as a TABLE object, the "_MV" suffix and its structure confirm it functions as a materialized view that pre-aggregates service request resolution metrics for the Business Intelligence / analytics layer of Oracle EBS. It is primarily populated from the Oracle Service (CS) and TeleService data model, summarizing incident resolution performance across time, product, customer, owner group, and classification dimensions.

The mv_ prefix on a large subset of columns and the presence of a SYS_OP_MAP_NONNULL-based unique index (I_SNAP$_BIV_RES_SUM_MV) identify this as an Oracle materialized view with a snapshot-refresh mechanism, commonly generated by the Oracle Business Intelligence (BIV) application or an equivalent analytics configuration. The object is valid and exposed to query tools through the APPS schema. Heuristically, the mined foreign-key structure classifies this object as standalone in a Data Vault sense, meaning it does not participate in a hub/link/satellite dependency chain via enforced constraints. In modeling practice it should be treated as a derived reporting satellite or aggregate fact, not as a source-of-record transactional entity.

Key Information Stored

The materialized view holds 41 documented columns. Of these, the most significant are the dimensional keys and the aggregated resolution measures:

• Time dimensions: TIME_ID, PERIOD_TYPE_ID, MV_DAY_ID, MV_WEEK_ID, MV_PERIOD_ID, MV_ENT_QTR_ID, and MV_ENT_YEAR_ID define the aggregation grain across day, week, period, quarter, and year.
• Classification dimensions: INCIDENT_TYPE_ID (references CS_INCIDENT_TYPES_B), INCIDENT_SEVERITY_ID (references CS_INCIDENT_SEVERITIES_B), and VBH_CATEGORY_ID classify the incident.
• Product and party dimensions: PRODUCT_ID (references FND_DM_PRODUCTS), CUSTOMER_ID, and OWNER_GROUP_ID.
• Channel and outcome: SR_CREATION_CHANNEL and RESOLUTION_CODE describe how the service request was submitted and resolved.
• Measures: RESOLUTION_COUNT, TOTAL_TIME_TO_RESOLUTION, and the bucketed columns TIME_TO_RESOLUTION_B1 through B10 capture resolution counts and time-band distributions. Their MV_-prefixed counterparts (MV_TOTAL_TIME_TO_RESOLUTION, MV_TIME_TO_RESOLUTION_B1 … B10, MV_COUNT) hold the refresh-maintained aggregate values.
• Grouping and surrogate key: MV_GROUPING_ID and GRP_ID support aggregate grouping. The documented unique index I_SNAP$_BIV_RES_SUM_MV, built over the MV_* dims plus INCIDENT_TYPE_ID, INCIDENT_SEVERITY_ID, VBH_CATEGORY_ID, PRODUCT_ID, CUSTOMER_ID, OWNER_GROUP_ID, SR_CREATION_CHANNEL, RESOLUTION_CODE, and MV_GROUPING_ID, serves as the business-key candidate identifying a snapshot row.

Common Use Cases and Queries

The primary use case is resolution-time reporting without hitting the high-volume service request base tables. Typical queries join the MV_ dimension keys to resolve time buckets, then filter by product or severity:

• Average resolution time by severity: SELECT s.severity, AVG(m.MV_TOTAL_TIME_TO_RESOLUTION) FROM biv_res_sum_mv m, cs_incident_severities_b s WHERE m.incident_severity_id = s.incident_severity_id GROUP BY s.severity;
• Resolution trend by period: aggregate MV_COUNT over MV_PERIOD_ID to trend service request closings.
• Backlog distribution: sum MV_TIME_TO_RESOLUTION_B1..B10 to profile how many incidents resolved within each target band.
• Product and channel analysis: group RESOLUTION_COUNT by PRODUCT_ID and SR_CREATION_CHANNEL to assess channel effectiveness.

Because the object is a materialized view, queries should account for snapshot staleness; analysts typically run against the APPS schema or a reporting copy refreshed on a scheduled cycle.

Related Objects

The following objects are most directly related through the documented foreign keys and business lineage:

• CS_INCIDENT_TYPES_B — joined via INCIDENT_TYPE_ID.
• CS_INCIDENT_SEVERITIES_B — joined via INCIDENT_SEVERITY_ID.
• FND_DM_PRODUCTS — joined via PRODUCT_ID for product dimension attributes.
• CS_INCIDENTS_ALL / CS_INCIDENT_* base tables — the transactional source lineage for resolution measures.
• BIV_* materialized views and OBIEE / Business Intelligence repository objects — consume this MV as an aggregate fact source.
• FND_DM_* dimension tables — supply the time and customer/group attributes referenced by the MV_* keys.

These relationships make BIV_RES_SUM_MV a reporting-centered aggregate rather than an operational entity, and it should be treated as read-mostly in any customization or extension effort.