Search Results opi_edw_res_util_f




Overview
Explore in AI Chat

Overview

OPI_EDW_RES_UTIL_F is a fact table within the OPI (Operations Intelligence) product family of Oracle E-Business Suite, present in both 12.1.1 and 12.2.2. It stores resource utilization measurements: the relationship between the amount of time a production resource was actually consumed and the total resource time available during a given period. In the OPI enterprise data warehouse model, this table is the analytical counterpart to transactional resource usage captured in the manufacturing execution modules, and it is intended to support capacity analysis, bottleneck identification, and resource efficiency reporting.

From a Data Vault modelling perspective, the sparse FK-style key columns (RES_FK_KEY, TRX_DATE_FK_KEY, DEPARTMENT, and the USER_FK*_KEY set) suggest a dimensional fact structure rather than a traditional hub. The heuristic classification mined from the FK structure is standalone; there are no enforced foreign keys from this table to other fact or dimension tables. Analysts designing a Data Vault layer should therefore treat OPI_EDW_RES_UTIL_F as a candidate fact (or satellite-like measurement) that must be joined explicitly to its dimension partners by surrogate key.

Key Information Stored

The table carries 38 physical columns. The most operationally significant are listed below.

  • RES_UTIL_PK – part of the composite surrogate primary key OPI_EDW_RES_UTIL_F_U2; identifies a resource utilization record.
  • RES_UTIL_PK_KEY – the alternative unique business key, enforced by OPI_EDW_RES_UTIL_F_UKEY; uniquely identifies a utilization event across the warehouse load.
  • ACT_RES_USAGE – actual resource usage, the numerator of the utilization ratio.
  • AVAIL_RES – available resource time, the denominator; utilization is derived as ACT_RES_USAGE divided by AVAIL_RES.
  • TRX_DATE and TRX_DATE_FK_KEY – the transaction date and its corresponding date-dimension surrogate key.
  • DEPARTMENT – organizational context for the resource being measured.
  • RES_FK_KEY – surrogate key to the resource dimension.
  • LOCATOR_FK_KEY – surrogate key to the locator dimension, describing where the resource resides.
  • UOM_FK_KEY – surrogate key to the unit-of-measure dimension used to express usage and availability.
  • INSTANCE_FK_KEY – surrogate key identifying the originating instance or source system.
  • CREATION_DATE / LAST_UPDATE_DATE – audit columns for record creation and last modification.
  • USER_FK1_KEY … USER_FK5_KEY – five generic dimension hooks available for customer-specific conformed dimensions.
  • USER_MEASURE1 … USER_MEASURE5 – five generic numeric slots for customer-defined additive measures.
  • USER_ATTRIBUTE1 … USER_ATTRIBUTE15 – fifteen descriptive slots for customer-specific text or coded attributes.

The surrogate primary key is RES_UTIL_PK_KEY (or the pair RES_UTIL_PK, RES_UTIL_PK_KEY on the U2 index); the business key is RES_UTIL_PK_KEY via the UKEY index. The generic USER_ columns exist because the OPI warehouse is designed for extension without schema change.

Common Use Cases and Queries

The dominant reporting pattern computes utilization percentage per resource, department, or period:

SELECT res_fk_key,
       trx_date,
       SUM(act_res_usage)                    AS used_time,
       SUM(avail_res)                        AS available_time,
       SUM(act_res_usage) / DECODE(SUM(avail_res),0,NULL,SUM(avail_res)) AS util_ratio
FROM   opi.opi_edw_res_util_f
WHERE  trx_date BETWEEN :p_start AND :p_end
GROUP  BY res_fk_key, trx_date;

Typical scenarios include capacity planning (comparing AVAIL_RES against ACT_RES_USAGE to find idle or over-subscribed resources), departmental efficiency scorecards grouped by DEPARTMENT, trend analysis by TRX_DATE, and drill-through reporting where the dimension keys (RES_FK_KEY, LOCATOR_FK_KEY, UOM_FK_KEY) are resolved against their respective OPI dimension tables. The USER_MEASURE and USER_ATTRIBUTE columns support customer-specific KPIs layered on top of the standard utilization measures without altering the delivered model.

Related Objects

  • Resource dimension resolved via RES_UTIL_F.RES_FK_KEY.
  • Date dimension resolved via RES_UTIL_F.TRX_DATE_FK_KEY.
  • Locator dimension resolved via RES_UTIL_F.LOCATOR_FK_KEY.
  • Unit-of-measure dimension resolved via RES_UTIL_F.UOM_FK_KEY.
  • Instance/source dimension resolved via RES_UTIL_F.INSTANCE_FK_KEY.
  • The five generic dimension hooks (USER_FK1_KEY – USER_FK5_KEY) for customer-defined conformed dimensions.
  • Sibling OPI EDW fact tables in the same star (for example, production and inventory utilization facts) that share the same INSTANCE_FK_KEY and user-extension conventions.

Because no enforced foreign keys exist on this table, all of the above relationships are logical and must be declared in the reporting or ETL layer.