Skip to Main Content
Return to Navigation

Operational Warehouse - Enriched (OWE)

The OWE structure is the second of two subcomponents that comprise the Operational Warehouse. The OWE stores enriched data that is arranged in a normalized format and mapped to warehouse business units (WBU). Enrichment can entail many transformations to your data, including (but not limited to) conversion to a common currency, common calendar, or a common ledger, or aggregating data to a common warehouse business unit. The PeopleSoft Analytical Applications use the enriched data in the OWE to perform analysis and reporting.

Image: Operational Warehouse - Enriched (OWE)

The following graphic illustrates the OWE component of the EPM architecture and the target tables that are present in the OWE.

 Operational Warehouse - Enriched (OWE)

Data is extracted into the OWE using prepackaged ETL jobs and loaded into target dimension (D00) and fact (F00) tables. The structure of these tables are quite different from the OWS tables because they are arranged in a normalized format and organized data around warehouse business units. In addition, OWE tables are augmented with subrecords which help facilitate the ETL process and tracking data lineage. OWE tables store data permanently and can maintain history (as opposed to temporary tables which remove data at the end of an ETL job).

Tools and Processes Associated with the OWE

EPM is delivered with several tools and processes that enable you to enrich and manage the data stored in the OWE. The following are some of the tools and processes used only with the OWE:

  • Performance ledger template setup.

  • Detail ledger setup.

  • Model and scenario setup.

  • Roll-up processing.

  • Profit manager.

  • EPM object auditing.

  • Mass validate processing.

  • Mass compile processing.

  • Tree utility setup.

  • Data manager processing.

  • Allocation manager processing.

OWE Dimension (D00) Tables

An OWE dimension table provides additional attributes about a fact for greater flexibility in reporting. Dimensions are derived from operational applications and are cleansed and transformed during data migration. Examples of dimension tables include: product, customer, channel, department, personal data, and accounts. Some of the fields associated with an OWE dimension table are:

  • SetID: Key column.

  • DIMENSION_ID: Key column.

  • EFFDT: This is the same date as the source. If an EFFDT or an alternative date, such as a date time stamp, does not exist, the system creates one and sets it to the date the dimension data is loaded.

  • EFF_STATUS.

  • KEY Fields from the source table.

  • SET CONTROL FIELD. This is BUSINESS_UNIT.

  • RELATED LANGUAGE RECORD. This is the same with an extension of _LNG.

Sample OWE Dimension Table

Image: OWE dimension - CUSTOMER_D00

The following is a sample OWE dimension table page shown in Application Designer.

 OWE dimension - CUSTOMER_D00

OWE Dimension Table Naming Convention

OWE dimension tables use the following naming convention, [table name]_D00

OWE Fact (F00) Tables

An OWE fact table contains measures for analyzing performance. Some of the fields associated with an OWE fact table are:

  • BUSINESS_UNIT: This field enables the fact data to be shared across different dimensions, as they are based on SetIDs.

  • FACT KEY.

  • ASOF_DT: This is for non-cumulative facts (for example, account balance).

  • PF_TRANS_DT: This is for cumulative facts (for example, billing transactions).

  • All KEY Fields: These are required for uniqueness.

  • DEFAULT VALUES include:

    • BUSINESS_UNIT: This will have a default table set to OPR_DEF_TBL_FS and a default field set to BUSINESS_UNIT.

    • EFFDT: This will have a default set to %DATE.

    • EFF_STATUS: This will have a default set to A.

    • TRANSLATE VALUES: These values, if any exist, must be set to the XLATTABLE.

  • PF_TRANS_DT: This is set to the source record's transaction date. In addition, the source transaction date field is included in the data warehouse fact table.

Sample OWE Fact Table

Image: OWE fact - ACCT_REC_F00

The following is a sample OWE fact table page shown in Application Designer.

 OWE fact - ACCT_REC_F00

OWE Fact Table Naming Convention

OWE fact tables use the following naming convention, [table name]_F00

OWE Temporary Tables

OWE temporary tables support parallel processing. A temporary table layout and key structure differs from its respective fact or dimension table in that the organizational unit (SetID or business unit) and the effective date are not keys.

EPM is delivered with three sets of temporary tables. You can define additional sets of tables when needed. The project EPM_TEMP_TABLES contains one instance of every temporary table, enabling you to create new temporary table suites, if necessary. A temporary table layout and key structure differs from its respective fact or dimension data warehouse table in that the organizational unit (SetID or business unit) and the effective date are not keys.

Note: If you must create more temporary tables than the ones delivered with PeopleSoft EPM, see the delivered project, EPM_TEMP_TABLES. It contains one instance of every temporary table, enabling you to create new temporary table suites, if necessary.

See Creating Additional Instances of Temporary Tables for Record Suites.

OWE Temporary Table Naming Convention

OWE temporary tables use the following naming convention, [table name]_T

Specialized Reporting Tables

The OWE features tables that have been designed specifically to enhance reporting capabilities. Those tables are the performance ledger table (PF_LEDGER_F00), performance journal table (PF_JRNL_F00), and the performance statistics table (PF_STAT_F00). Creating specialized tables in this manner enables you to move away from storing all of your accounting data in your general ledger to make your general ledger perform as it should—as a method for compliance reporting only.

The performance journal and performance ledger tables are described in more detail later in this documentation.

Performance Ledger Table

The performance ledger table (PF_LEDGER_F00) is a central fact table within EPM. The performance ledger table is an accumulation of monetary amount facts over a period of time. The primary function of the performance ledger table is to support PeopleSoft EPM reporting. The PF_LEDGER_F00 is the source for one of the data marts.

Note: The performance ledger table should not be confused with a general ledger from an online transaction processing (OLTP) system. The performance ledger contains all information mapped from a general ledger and enriched through one (or more) of the PeopleSoft EPM engines.

Information that has been processed through an PeopleSoft EPM engine, for instance the ABM engine or Data Manager, is stored in a temporary performance journal staging table (PF_JRNL_T).

The PF Edit engine enables you to verify the data in the temporary journal table and moves valid data to the final table, the PF_JRNL_F00. Errors are placed in the PF_JRNL_E00, the error table for the journal table. The PF Post takes the detailed information from the performance journal table, aggregates it to the desired level of summarization and posts it to the PF_LEDGER_F00 for reporting.

PeopleSoft EPM reporting tools support multidimensional analysis based primarily on profitability dimensions such as customer, product, and channel. You can use one, two, or more of these dimensions within your models, or configure the application to add more dimensions, or change the existing ones. No matter which dimensions you select, however, you need to consider how to populate the performance ledger table with meaningful multidimensional data.

Performance Journal Table

The performance journal table (PF_JRNL_F00):

  • Contains data that is not yet summarized.

  • Is a fact table, or multiple fact tables, within EPM.

  • Is a collection of batches of amount facts staged for validation and posting to the performance ledger table.

  • Supports drill down from reports produced against the performance ledger table.

The PF Edit engines moves data to the performance journal fact table. The PF Post process accumulates valid transactions from the performance journal table, and inserts summarized rows into the performance ledger. There is a "many to one" relationship between the performance journal and the performance ledger tables.

Performance Statistics Table

The performance statistics table (PF_STAT_F00) is similar to the performance ledger table (PF_LEDGER_F00) in its layout.

OWE Error Tables (for Profit Manager only)

The OWE contains error tables used to identify flawed data in certain OWE target tables. There are a small number of delivered OWE error tables and they are used only for Profit Manager. Profit Manager uses specific business rules to validate and format data in its related OWE target tables. If the business rules are not met, then the flawed records are written to an OWE error table and a message describing the error is written to a detail error message table (TSE table). If your load results in errors, you can use PF Modification to correct the errors. You can correct the errors using the PeopleSoft Application Designer and then migrate the corrected tables to the target. The following OWE error tables are delivered:

  • BP_LED_BUDG_E00

  • BP_LED_E00

  • BP_LED_KK_E00

  • BP_LED_PROJ_E00

  • GC_JRNL_MGT_E00

  • LEDGER_E00

  • PF_JRNL_E00

See Understanding Profit Manager Tools.

Sample OWE Error Table

Image: OWE error table - BP_LED_E00

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

 OWE error table - BP_LED_E00

OWE Error Table Naming Convention

OWE error tables use the following naming convention, [table name]_E00