Operational Warehouse - Staging (OWS)

OWS core target tables contain data extracted from PeopleSoft source systems. OWS target tables are permanent tables (as opposed to temporary tables), and can store historical data. However, it is not the recommended location for historical data as the tables can be purged from time to time depending on your operational needs.

Generally, the structure of the OWS target tables match the structure of the source transaction tables with the addition of a source system identification column (SRC_SYS_ID) , which enables you to track the origin of your data. However, minor structural differences can occur when:

• Fields are removed from the source system record.

• New fields are added to the source system record but not used by the EPM warehouse.

  For example, the following fields were added to the FIN 91 source system: P6_INCL_INTGRTN, P6_TEMPLATE_ID, and P6_WBS_LVL. But these fields are not used by the FMS Warehouse, so EPM does not propogate these fields to the corresponding OWS table.

• The OWS table is designed as a common table and stages data from more than one PeopleSoft source system.

  For example, the PS_PROJECT OWS table is a common table that captures project related data from PeopleSoft Campus Solutions, Financials, and HCM source systems. The fields BURDEN_PLAN, SUMMARY_SW, and WBS_TREE_NAME are present in the Campus Solutions and HCM source system tables, but not in the Financials table.

Sample OWS Target Table

Image: OWS target table - ABS_CLASS_TBL

The following is a sample OWS target table page shown in Application Designer.

The OWS contains error tables used in the data validation process. The data validation process uses ETL jobs to verify the integrity and completeness of the data entering OWE and MDW target tables. The validation process can perform dimension key validation (for example, verifying that customer ID fact value has a corresponding customer ID dimension value) and general key validation (for example, verifying the pre-fact customer ID in the OWS table has a corresponding customer ID in the OWE or MDW table), as well as ensure source business unit and SetID are properly mapped to EPM values and source codes are properly mapped to EPM code values.

Data failing the validation process are sent to OWS error tables. It is important to note that the OWS error tables have a different structure than the error tables in the OWE and perform a very different function. The OWS error table mirrors the key structure and other columns of its corresponding data table and has additional fields to facilitate troubleshooting. The following OWS error table columns represent some of the columns provided for troubleshooting:

• LOAD_OWS_SBR: The values for these columns are copied from the failing data row. The reason for copying values from the failed data row is that it provides vital load information such as batch ID and load timestamp for the data row.

• Target Table: This column lists the target table for the job.

• Failed data source table and column name: The source table and column from which the failing data originated. Knowing the name of the failed source data table is especially useful when the job loading the failed data contains a multi-source-table join.

• Failed Data Value: The actual value that failed validation.

• Lookup table and column name: The table and column against which the failed lookup was performed.

Detailed information regarding the data validation process can be found in theUnderstanding Data Validation and Error Handling in the ETL Process topic.

Sample OWS Error Table

Image: OWS error table - E_ABS_CLASS_TBL

The following is a sample OWS error table page shown in Application Designer.