Understanding PeopleSoft Enterprise Performance Management (EPM) Foundation

This chapter provides an overview of EPM and EPM Foundation, and discusses:

• EPM Architecture.

• Extract, Transform, and Load (ETL) in EPM Foundation.

• Operational Warehouse - Staging (OWS).

• Operational Warehouse - Enriched (OWE).

• Multidimensional Warehouse (MDW).

• EPM Foundation Tools.

• PeopleSoft EPM Analytical Applications.

• PeopleSoft Performance Management Warehouse and reporting.

• EPM Foundation Implementation.

Overview

PeopleSoft EPM is a comprehensive, integrated analytic business solution designed to increase the efficiency of your organization by enabling you to make better business and strategy decisions. PeopleSoft EPM helps your organization achieve operational excellence by providing insight into the information you need to drive predictability, accountability, and manage operational risk. EPM enables you to produce detailed activity analyses and resource plans, understand the cause-and-effect relationship between cost and behavior, organize strategic thinking and performance measurement, use continuous, collaborative forecasting to manage the plan and budget in real-time, and clearly communicate strategy and success measures.

EPM is supported by a robust data warehousing solution—PeopleSoft Enterprise Performance Management Foundation. EPM Foundation is the central repository for PeopleSoft EPM business analytics and provides an enterprise platform for business intelligence. EPM Foundation provides all the necessary tools to gather and manage data from PeopleSoft, legacy, and external data sources, enrich that data, and store it in an intuitive analytic context for you to analyze in a variety of ways and at a variety of levels. EPM Foundation enables you to deliver a single, accurate view of information across your organization.

PeopleSoft EPM Architecture

PeopleSoft Analytical Applications and the Performance Management Warehouse (which includes all PeopleSoft functional warehouses), are built on a foundation of specialized target warehouse tables, data models, ETL jobs, metadata, tools, and processes that enable complex analysis and reporting of your data. Each component of the foundation performs a specialized function, which supports either the applications or the functional warehouses.

Target warehouse tables provide a way to consolidate and store your source data. EPM target warehouse tables reside in two high-level warehouse structures: the Operational Warehouse (OW) and the Multidimensional Warehouse (MDW). The Operational Warehouse can be further divided into the Operational Warehouse - Staging (OWS) and the Operational Warehouse - Enriched (OWE). Each warehouse structure has its own set of specialized target warehouse tables that are unique to that structure. For example, the Operational Warehouse - Enriched (OWE) structure stores enriched data that is arranged in a normalized format to promote complex analytics. And the Multidimensional Warehouse (MDW) structure stores data that is arranged in a denormalized format for enhanced reporting capabilities.

EPM architecture

The dual warehouse architecture helps to:

• Isolate and channel specific source data to the appropriate warehouse structure for individual enrichment and modeling.

  PeopleSoft provides extract, transform, and load (ETL) jobs to extract information contained in your source systems, load it into the Operational Warehouse - Staging (OWS) structure, and migrate that data to the Operational Warehouse - Enriched (OWE) and the Multidimensional Warehouse (MDW) structures. And because the warehouse structures are logically separated, the ETL jobs can isolate and channel specific source data to the OWE or the MDW.

• Facilitate specialized, or tailored, data enrichment for your source data.

  PeopleSoft provides EPM Foundation tools and processes (a set of specialized tools, processes, and metadata) that prepare and enrich your source data for the analytical applications and the functional warehouses.

The target warehouse tables, ETL jobs, and EPM Foundation tools work together to provide the underlying infrastructure on which the analytical applications and functional warehouses are built. Detailed information regarding the OWS, OWE, MDW, and EPM Foundation Tools can be found in this chapter.

See Also

Operational Warehouse - Staging (OWS)

Multidimensional Warehouse (MDW)

EPM Foundation Tools

Extract, Transform, and Load (ETL) in EPM Foundation

PeopleSoft has an original equipment manufacturer (OEM) agreement with Ascential to supply extract, transform, and load (ETL) technology that supports source data acquisition and data movement within EPM Foundation. The ETL tool, Ascential DataStage, is delivered with EPM.

PeopleSoft uses Ascential DataStage to deliver prepackaged ETL jobs that extract information contained in PeopleSoft source systems, load it into the Operational Warehouse - Staging (OWS), and migrate that data to the Operational Warehouse - Enriched (OWE) and the Multidimensional Warehouse (MDW). But ETL jobs do more than migrate data; they also identify data for extraction and ensure the consistency and validity of your data. Because the ETL jobs are so versatile, separate tools and engines that extract, stage, and move data are not necessary.

The following diagram demonstrates the ETL process in EPM Foundation.

ETL in EPM

As depicted in the diagram, source transaction data is extracted into OWS tables and migrated across warehouse layers using the aforementioned ETL jobs. You can also use Ascential DataStage to build custom jobs for mapping your data into EPM Foundation.

Detailed information regarding the ETL process can be found in the ETL section of this PeopleBook.

See Also

Loading Data Into EPM Foundation

Operational Warehouse - Staging (OWS)

The OWS structure is one of two subcomponents that comprise the Operational Warehouse. The OWS acts as an entry-point for your source transaction data into EPM Foundation and can house data from one or more of your PeopleSoft, legacy, or external source systems. The main function of the OWS is to provide a platform to offload, consolidate, and stage your source transaction data in preparation for enrichment.

Operational Warehouse - Staging (OWS)

Source data is extracted into the OWS using prepackaged ETL jobs and loaded into target staging tables. No transformations are performed on your source data during this process and the system maintains the same source-level of granularity for your data. Source tables are extracted into the OWS, including all logically related tables, to ensure your source system data is semantically complete. For example, a table extracted into the OWS may have an associated related language table in the source system. The related language data from the associated table is also extracted into the OWS to maintain completeness and data integrity. Data stored in the OWS is used as input for the Operational Warehouse - Enriched (OWE) and the Multidimensional Warehouse (MDW) structures.

Note. The OWS does not contain reporting tables nor prepackaged reports built on the core OWS target tables.

OWS Core Target Tables

OWS core target tables contain data migrated from PeopleSoft Enterprise and Enterprise One 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. 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.

Note. Certain OWS target tables have specific non-key columns that can be “activated” as key columns if your business requirements necessitate it.

See PeopleSoft Enterprise Planning and Budgeting 8.9 PeopleBook.

Sample OWS Target Table

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

OWS target table - ABS_CLASS_TBL

OWS Target Table Naming Convention

OWS target tables use the following naming conventions:

• S_[source table name]

• [source table name]

OWS Error Tables

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 batchID 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 & column name: The table and column against which the failed lookup was performed.

Detailed information regarding the data validation process can be found in the ETL section of this PeopleBook.

See Data Validation and Error Handling in the ETL Process.

Sample OWS Error Table

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

OWS error table - E_ABS_CLASS_TBL

OWS Error Table Naming Convention

OWS error tables use the following naming conventions:

• E_[OWS table name]

• ES_[OWS table name]

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.

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 Foundation 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:

• Warehouse business unit setup.

• 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

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 (from across the enterprise) 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

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. 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 PeopleBook.

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 in its layout, except that it is used to support different decimal precision for statistical rather than monetary values. For example, the performance ledger can store monetary amounts with a decimal precision of two, while the performance statistics table can store statistical values with a decimal precision of eight.

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 Setting Up and Using Profit Manager.

Sample OWE Error Table

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

Multidimensional Warehouse (MDW)

The MDW structure is built on the principle of dimensional modeling. Using this principle, data entering the MDW is grouped as it is related to one or more business processes. The data is primarily stored in a star schema—that is, a central fact table joined to a series of dimension tables. The MDW contains a dimensional schema for business intelligence and ad hoc reporting.

Multidimensional Warehouse

More detailed information regarding the MDW can be found in this PeopleBook.

See Understanding the Multidimensional Warehouse.

EPM Foundation Tools

EPM Foundation is delivered with EPM Foundation tools. These set of tools enable you to enrich, audit, and manage the rich content included with EPM Foundation with a high degree of automation. For example, the Clone Metadata tool enables you to quickly and easily create a duplicate copy of your existing metadata. EPM Foundation tools can be used with content included in the Operational Warehouse and the Multidimensional Warehouse.

EPM Foundation Tools

The following sections provide additional details about EPM Foundation Tools.

Setup Tools

Implementing EPM Foundation requires that you specify parameters within the warehouse that reflect your organization's basic business processes and parameters. For example, you must define parameters for unit of measure, country, and accounting calenders in EPM Foundation.

EPM Foundation delivers several setup tools which enable you to quickly and easily setup basic information in the warehouse including unit of measure, multiple language and currency, and operator defaults.

See Setting Up EPM Foundation.

Security Tools

EPM security enables you to set up data access at a variety of entry points and control access to meet your business needs, right down to an individual field. Security tools enable you to:

• Use application security to control access to applications, menus, and objects. You can specify which applications are available to a group of users, which menus and EPM objects they can access.

• Use row-level security, for example, to implement dimension-level access to particular products, customers, or key performance metrics. This ensures that highly sensitive data is protected.

You can also set up a specific security for the Ascential ETL tool.

See Securing EPM.

Data Storage and Classification Tools

Implementing EPM Foundation involves configuring the system's structures to how your business operates. You can share common tables across reporting and analytic applications to minimize redundant data and system maintenance tasks.

Record metadata, for example, defines the first level of EPM Foundation metadata. It is used to identify and classify the tables that constitute the EPM Foundation data model. The record metadata identifies EPM tables as fact tables, fact reference tables, dimension tables, dimension reference tables, or transaction-dated tables. Each table is also classified to a specific data layer: the OWE or the MDW.

Tree manager provides an intuitive way to create, view, and maintain hierarchical definitions. An easy to understand user interface facilitates the creation and maintenance of trees. Tree mover allows you to moved PeopleSoft trees between different PeopleSoft application databases.

See Setting Up EPM Foundation.

See Setting Up and Working with EPM Foundation Metadata.

Performance Management Related Tools

EPM Foundation utilizes shared components that provide functionality key to supporting high-volume analytic applications:

• Reusable filters and constraints stored in the metadata enable you to define sets of rules that can be shared across applications.

• Jobstreams streamline analytic processes and enable applications to run concurrently.

See Setting Up and Working with EPM Foundation Metadata.

See Streamlining Processing with Jobstreams.

PeopleSoft EPM Analytical Applications

EPM provides the applications necessary to analyze business situations, model business scenarios, and monitor performance.

EPM Analytical Applications

PeopleSoft analytical applications is comprised of the following individual applications:

• PeopleSoft Enterprise Activity-Based Management

• PeopleSoft Enterprise Customer Behavior Modeling

• PeopleSoft Enterprise Demand Planning

• PeopleSoft Enterprise Financials Services Industry

• PeopleSoft Enterprise Funds Transfer Pricing

• PeopleSoft Enterprise Global Consolidations

• PeopleSoft Enterprise Inventory Policy Planning

• PeopleSoft Enterprise Performance Management Portal Pack

• PeopleSoft Enterprise Planning and Budgeting

• PeopleSoft Enterprise Project Portfolio Management

• PeopleSoft Enterprise Risk-Weighted Capital

• PeopleSoft Enterprise Sales Incentive Management

• PeopleSoft Enterprise Sales Incentive Management for High Tech and Industrial

• PeopleSoft Enterprise Scorecard

• PeopleSoft Enterprise Supply Planning

• PeopleSoft Enterprise Supplier Rating System

• PeopleSoft Enterprise Workforce Analytics

• PeopleSoft Enterprise Workforce Analytics Applications

• PeopleSoft Enterprise Workforce Planning

• PeopleSoft Enterprise Workforce Rewards

For more details on the application or applications you have licensed, please refer to the specific PeopleBook or PeopleBooks.

PeopleSoft Performance Management Warehouse and Reporting

The PeopleSoft Performance Management Warehouse provides a comprehensive reporting and analysis tool set including PS/Query, Crystal Reports, PS n/Vision, and SQR. The Performance Management Warehouse is comprised of the following subject-specific functional warehouses:

• Financials Warehouse

• HRMS Warehouse

• CRM Warehouse

• Supply Chain Warehouse

Each functional warehouse is comprised of:

• Predefined ETL jobs for moving data from operational systems to the OWS and MDW.

• Dimensional models for multidimensional reporting and analysis.

Performance Management Warehouse

PeopleSoft's reporting architecture allows you to report when and where it makes the most sense. Reporting tables are built in the MDW to enable offloading of operational reports from your transactional systems. As part of your implementation, you need to consider which operational reports it makes sense to offload to the Performance Management Warehouse.

In addition, the PeopleSoft Performance Management Warehouse open reporting platform enables you to use the delivered PeopleSoft reporting tools or integrate third-party reporting tools. The Enterprise Performance Management Warehouse 8.9 PeopleBook provides more details on the Performance Management Warehouse and each of its functional warehouses.

Implementing EPM

To implement EPM Foundation you must:

• Set up EPM tables (for example, CURRENCY_CD_TBL, META_REC_TBL).

• Populate warehouse target tables (staging, D00, F00, DIM, and FACT) with your source transaction data.

Most of the implementation tasks required to set up and populate these tables are mandatory. However, some tasks relate only to implementing the analytical applications or the functional warehouses. Consequently, if you have only licensed the analytical applications, for example, you need not perform the implementation tasks related to the functional warehouses.

PeopleSoft Setup Manager can help you determine which implementation tasks you are required to perform by generating a list of setup tasks based on the features you have licensed. The setup tasks include the components that you must set up, listed in the order in which you must enter data into the component tables, and links to the corresponding PeopleBook documentation.

If you choose not to use the Setup Manager, the diagram below can provide you with a high-level understanding of the tasks required to implement EPM Foundation and the sequence in which the tasks should be performed. The tasks in the diagram are listed in a suggested implementation sequence. The actual sequence by which you implement EPM Foundation may differ.

Implementation roadmap

The first task in the diagram begins after the successful installation of EPM Foundation and Ascential third-party products. Installation tasks are not shown. Tasks in the diagram are represented at a high-level and may include several smaller tasks. It should also be noted that tasks are organized according to common tasks (tasks that must be performed) and analytical application or functional warehouse tasks (tasks which may be optional to you, depending on the products you purchased). To help guide you through the implementation, this book is also organized in a similar manner with sections clearly indicating whether a section relates to the analytical applications or the functional warehouses.

Before you begin implementing EPM Foundation, be sure to read this book thoroughly and take advantage of all PeopleSoft sources of information that can help, including installation guides, ETL job reports, and data models.

See Also

Setting Up EPM Foundation

Setting Up and Configuring Data In the Operational Warehouse - Enriched (OWE)

Processing Trees and Recursive Hierarchies