Search Results res_fk_key

Overview

OPI.OPI_EDW_RES_UTIL_F is a resource utilization fact table within the Oracle Process Manufacturing (OPM/OPI) analytic schema, delivered under the Oracle E-Business Suite 12.1.1 and 12.2.2 release families. The object is classified as a fact table, meaning it stores quantitative, additive measures at a defined grain rather than descriptive master data. Its documented purpose is to record resource utilization, defined as a measure indicating how much production resources are consumed relative to the total resource capacity available. Utilization is expressed as a comparison between resource time used and total resource time available, and the table stores availability and actual usage for each resource for every workday.

The table resides in the APPS_TS_SUMMARY tablespace with PCTFREE 10, a placement consistent with summarized reporting marts rather than high-volume transactional storage. It carries the FND Design Data identifier OPI.OPI_EDW_RES_UTIL_F and is marked VALID. From a Data Vault modeling perspective, the mined FK structure is classified heuristically as standalone; the table is best treated as a fact satellite-like structure that consolidates dimensional foreign keys and numeric measures, rather than a hub or link with dependent satellites. This classification is a modeling suggestion derived from the absence of explicit parent-child hub relationships in the documented key structure.

Key Information Stored

The table holds 38 documented columns spanning surrogate keys, dimensional foreign keys, business measures, and descriptive attributes. The most significant columns are:

• RES_UTIL_PK — User-defined primary key of type VARCHAR2(240), structured from the source tag (documented as beginning with "PRODTAG = OPI") and related identifying components.
• RES_UTIL_PK_KEY — Numeric surrogate key component of the primary key, listed as a unique business-key candidate in OPI_EDW_RES_UTIL_F_UKEY.
• RES_FK_KEY — Numeric foreign key to the Resource dimension at resource level; the subject of the user's search and the most direct entry point for resource-specific aggregation.
• ACT_RES_USAGE — Daily actual resource quantity charged to jobs and schedules for a department.
• AVAIL_RES — Daily available resource capacity for a department, computed as capacity units multiplied by shift hours per day, or capacity units multiplied by 24 hours when the resource is available around the clock.
• DEPARTMENT — VARCHAR2(240) describing the department that owns the resource.
• INSTANCE_FK_KEY — Foreign key to the Instance dimension.
• LOCATOR_FK_KEY — Foreign key to the Inventory Location dimension, defined at Inventory Org Group level for OPM and at Inventory Org level for OPI, identifying where the resource is charged.
• TRX_DATE and TRX_DATE_FK_KEY — Transaction date and its corresponding date dimension foreign key.
• UOM_FK_KEY — Foreign key to the Unit of Measure dimension applicable to the resource measures.
• CREATION_DATE and LAST_UPDATE_DATE — Standard who columns recording row creation and last modification.
• USER_ATTRIBUTE1 through USER_ATTRIBUTE15 — Fifteen descriptive flexfield-style attributes available for client-specific enrichment.
• USER_FK1_KEY through USER_FK5_KEY — Five additional foreign key slots for user-defined dimensions.
• USER_MEASURE1 through USER_MEASURE5 — Five numeric measure slots for user-defined facts.

The unique indexes OPI_EDW_RES_UTIL_F_U2 (RES_UTIL_PK, RES_UTIL_PK_KEY) and OPI_EDW_RES_UTIL_F_UKEY (RES_UTIL_PK_KEY) enforce and expose the business-key candidates, while five bitmap indexes (N1 through N5) cover UOM_FK_KEY, INSTANCE_FK_KEY, LOCATOR_FK_KEY, RES_FK_KEY, and TRX_DATE_FK_KEY respectively.

Common Use Cases and Queries

Typical usage centers on capacity-versus-consumption reporting. Analysts compare ACT_RES_USAGE against AVAIL_RES by plant, department, and workday to identify under- or over-utilized resources, and trend utilization across periods using TRX_DATE.

A representative aggregation query grouping by the resource surrogate key follows:

• SELECT RES_FK_KEY, DEPARTMENT, SUM(ACT_RES_USAGE) used, SUM(AVAIL_RES) available FROM OPI.OPI_EDW_RES_UTIL_F GROUP BY RES_FK_KEY, DEPARTMENT;

Because RES_FK_KEY is indexed by bitmap index OPI_EDW_RES_UTIL_F_N4, predicates on this column are well supported for filtering and dimensional joins. Additional scenarios include joining LOCATOR_FK_KEY for inventory-org-level reporting, joining TRX_DATE_FK_KEY to the date dimension for calendar hierarchies, and using the USER_FK*_KEY and USER_MEASURE* columns to extend the fact for site-specific requirements.

Related Objects

The table's dimensional integrity is enforced through its documented foreign key columns. The most significant related objects are:

• Resource dimension — joined via RES_FK_KEY at resource level.
• Instance dimension — joined via INSTANCE_FK_KEY.
• Inventory Location dimension — joined via LOCATOR_FK_KEY, at Org Group level for OPM and Org level for OPI.
• Date dimension — joined via TRX_DATE_FK_KEY, with TRX_DATE supplying the raw date.
• Unit of Measure dimension — joined via UOM_FK_KEY.
• OPI_EDW_RES_UTIL_F_U2 / UKEY / N1–N5 — the supporting unique and bitmap indexes in APPS_TS_SUMMARY.

These relationships make OPI.OPI_EDW_RES_UTIL_F a self-contained summary fact directly consumable by OPM resource utilization dashboards and custom BI extracts in both 12.1.1 and 12.2.2.