Search Results opi_edw_res_util_fstg

Overview

The table OPI.OPI_EDW_RES_UTIL_FSTG is the interface (staging) table for the Resource Utilization Fact within the Oracle E-Business Suite Operations Intelligence (OPI) product family, available in EBS 12.1.1 and 12.2.2. Its documented role is to stage data extracted from multiple transactional source systems so that it can be verified and cleansed before being loaded into the Resource Utilization fact table in the Operations Intelligence data warehouse. The table is owned by the OPI schema and holds a VALID status in the data dictionary. It contains 54 documented columns, reflecting a wide-grain staging structure designed to accommodate heterogeneous source feeds.

Because the staging table is populated by ETL extraction routines rather than maintained transactionally by end users, it functions as a transient landing area. Records pass through it during each collection cycle and are subsequently transformed into the dimensional fact structures used by OPI analytics dashboards and reports.

The heuristic Data Vault classification mined from the foreign-key structure is standalone. This suggests that, in a Data Vault–style model, the table is best treated as neither a conformed hub nor a link, but rather an independent staging or satellite-like construct that does not participate in a shared business-key backbone. Its only documented outbound foreign key reference is ROW_ID to CS_SYSTEMS_ALL_B_TEMP, which links each staged record to the source system that supplied it.

Key Information Stored

Although the table defines 54 columns, the most operationally significant ones fall into a small number of categories:

• Surrogate and system keys: ROW_ID is the row identifier and carries the foreign key to CS_SYSTEMS_ALL_B_TEMP. RES_UTIL_PK and its companion RES_UTIL_PK_KEY represent the primary-key surrogate and its key value for the resource utilization record.
• Business-key and dimension references: RES_FK/RES_FK_KEY (resource), LOCATOR_FK/LOCATOR_FK_KEY (locator), INSTANCE_FK/INSTANCE_FK_KEY, TRX_DATE_FK/TRX_DATE_FK_KEY, and UOM_FK/UOM_FK_KEY (unit of measure). These _FK / _FK_KEY pairs supply both the warehouse surrogate and the natural source key for each referenced dimension.
• Measures: ACT_RES_USAGE (actual resource usage) and AVAIL_RES (available resource) are the core quantitative facts, supplemented by generic USER_MEASURE1 through USER_MEASURE5 for extensibility.
• Descriptive attributes: DEPARTMENT, TRX_DATE, and the USER_ATTRIBUTE1 through USER_ATTRIBUTE15 columns provide contextual slicers and user-defined dimensions.
• Control and audit columns: OPERATION_CODE, COLLECTION_STATUS, ERROR_CODE, REQUEST_ID, EDW_COLLECTION_DATE, CREATION_DATE, and LAST_UPDATE_DATE drive the verification, cleansing, and error-handling workflow.

Common Use Cases and Queries

Typical usage centers on validating staged loads and diagnosing collection failures. A common pattern is to isolate rows that have not yet been loaded or that carry errors:

• Filter by COLLECTION_STATUS and ERROR_CODE to identify records requiring correction before promotion to the fact table.
• Join to CS_SYSTEMS_ALL_B_TEMP on ROW_ID to attribute staged rows to their originating source system.
• Aggregate ACT_RES_USAGE and AVAIL_RES by DEPARTMENT, TRX_DATE, or RES_FK_KEY for reconciliation against source transactional reports.
• Inspect REQUEST_ID and EDW_COLLECTION_DATE to determine which concurrent request populated a given batch.

A representative query groups staged measures by resource and transaction date:

SELECT res_fk_key, trx_date, SUM(act_res_usage) usage, SUM(avail_res) avail FROM opi_edw_res_util_fstg WHERE collection_status = 'NEW' GROUP BY res_fk_key, trx_date;

Related Objects

The most significant dependencies and companions for this staging table are:

• CS_SYSTEMS_ALL_B_TEMP — referenced via the ROW_ID foreign key; identifies the source system for each staged row.
• The Resource Utilization fact table in the OPI data warehouse — the downstream target that consumes cleansed rows from this staging table.
• OPI collection and ETL concurrent programs — populate and promote data through this interface table using REQUEST_ID and EDW_COLLECTION_DATE.
• Warehouse dimension tables corresponding to RES_FK, LOCATOR_FK, INSTANCE_FK, TRX_DATE_FK, and UOM_FK — resolved through the documented surrogate/natural key pairs.
• OPI Operations Intelligence reporting views and dashboards that ultimately present the loaded resource utilization metrics.