PeopleSoft EPM Warehouse

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.
# Enable your organization to achieve operational excellence by providing insight into the information you need to drive predictability, accountability, and manage operational risk.



The following components comprise the Performance Management Warehouse:
• Data acquisition tool (Ascential DataStage ETL tool)
• OperationalWarehouse Staging (OWS)tables
• Operational Warehouse Enriched (OWE)tables
• Multidimensional Warehouse (MDW)tables
• Data warehouse tables
• Analytic applications
• Data marts.

PeopleSoft delivers six EPM warehouses

The following sections describe these PeopleSoft EPM Warehouses:
• PeopleSoft Enterprise Customer Relations Management (CRM).
• PeopleSoft Enterprise Campus Solutions (CS).
• PeopleSoft Enterprise Financial Management Solutions (FMS).
• PeopleSoft Enterprise Financials Warehouse for Public Sector and Higher Education.
• PeopleSoft Enterprise Human Capital Management (HCM).
• PeopleSoft Enterprise Supply Chain Management (SCM).

Step by Step Process:

• Import all of the appropriate *.dsx files containing your ETL jobs.
• Compile all jobs.
• Run hashed file setup jobs.
• Run initial OWS setup jobs to bring source data into the Warehouse.
• Run the Dimension Mapper setup jobs.
• Run shared lookup jobs.
• Run the Setup - OWE jobs.
• Run the Common Dimensions jobs.
• OWS
• GLOBAL_DIMENSION
• LOCAL_DIMENSION
• Global_D00
• OWE_E



The two main data warehouse structures in EPM are 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) layers.
Each warehouse structure has its own set of specialized target warehouse tables that are unique to that structure.

OWS: Data stored in the OWS is used as input for OWE and MDW, which are the designated areas for data consumption by EPM Analytic Applications and EPM Warehouses.

OWE: The OWE stores enriched data that is arranged in a normalized format and mapped to warehouse business units. Data is extracted into the OWE from the OWS and loaded into target dimension (D00) and fact (F00) tables.
Both OWS & OWE has Error table but the structure of Error tables are different.

MDW: The MDW stores dimensionalized data that is grouped into one or more business processes, better known as a dimensional schema, used for business itelligence and ad hoc reporting.
Data is extracted into the MDW from the OWS, but a few MDW tables contain data from the OWE. MDW data is stored in a start schema, which is central fact table joined a series of dimension tables.

Data arranged in a star schema depends on primary key and foreign key relationships:
A primary key is a column, or columns, in a fact table whose values match the primary key values of a given dimension table. This way references can be made between a fact and dimension table. Foreign keys enforce referential integrity by completing an association between two entities.

A foreign key is a column, or columns, in a fact table whose values match the primary key values of a given dimension table. This way references can be made between a fact a dimension table.Foreign keys enforce referential integrity by completing an association between two entities.

In the MDW, surrogate keys serve as primary keys in the dimension table and as foreign keys in the fact tables. Surrogate keys act as unique identifiers whose values, whith the exception of the Time and Cslrnfst fimrndiond, have no significance to the application using it and are strictly artificial values. The system uses surrogate keys specifically as a means of joining structures. Surrogate keys are generated from production keys during the MDW ETL process and have no relationship to the business or production key.

MDW Fact Tables

MDW fact tables (F_*) contain numeric performance measurement information that is used to build data marts, which are then used in multidimensional reports that categorize your business. In a star schema, fact table is the central table, each element of which is a foreign key derived from a dimension table.

Facts are typically numerical values in a data mart, such as quantity, sales and revenue that are related to elements of your business. Facts help to quantify a company's activities. A fact is a typically an additive business performance measurement. That is, you can usually perform arithmetic functions on facts.

Types of MDW Fact Tables:

The following table lists the four types of fact tables delivered in the MDW:

Conformed Dimensions:

Dimensions are sets of related attributes that you use to group or constrain detailed information that you measure in your data mart. Dimensions are usually text(Charater data type), relatively static, and often hierarchical.

The MDW uses conformed dimensions, which are dimensions that are reusable across several different fact tables.

Common Dimensions:

Common dimensions contain a set of dimensional attributes that can be shared by all subject areas or data marts. By definition, common dimensions are conformed dimensions. The PeopleSoft functional warehouses share these seven common dimensions:

1.Calendar
2.Time
3.Business Unit
4.Time Zone
5.Currency
6.Unit of Measure (UOM)
7.Language

MDW fact tables can use common dimensions, shared dimensions ( dimensions used by one or more functional warehouses or marts within a warehouse), and subject area dimensions (dimensions used by only one subject area, or mart)

ETL Load Strategies in EPM

PeopleSoft delivers ETL jobs that employ an incremental load strategy, which uses built-in logic to identify and extract only new or changed source records. When an incremental load job reads data from a source, it uses the date-time stamp to identify new or changed records. When an incremental load job writes data to a target, it updates records using update, else insert logic.

EPM ETL jobs employ two types of incremental load strategies:
# Incremental load with a data-time stamp
# Incremental load using Cyclical Redundancy Check ( CRC ) logic.

PeopleSoft Architecture:

Tables in PeopleSoft databases fall into three general areas.

The following table describes the types of PeopleSoft database tables:

System Catalog Tables:

System catalog tables contain database metadata.These tables:
# Track all objects in the entire database
# Are analogous to a table of contents.
# Vary based on platform

PeopleTools Tables:

PeopleTools tables contain metadata about the PeopleSoft application that is installed on the database.
PeopleTools tables:
# Are Structured consistently between applications.
# Vary in content between applications.
# Can be modified by PeopleTools such as Application Designer.
# Are converted to HTML and delivered to a browser.
# Use the naming convention PS*; for example, PSPNLDEFN.

PeopleTools Tables:

Definitions are the elements that make up the application user interface. They are stored in the PeopleTools tables.
These elements used to be referred to as objects, but as of PeopleTools 8.5, they are referred to as definitions.

Application Data Tables

Application data tables hold the data that users enter into the database.
Specific tables are determined by the application. Developers can create more of these tables.
Content is modified through the user interface.
Tables use the naming convention PS_*; for example, PS_CUSTOMER_TBL.

To examine PeopleSoft databases:

Example:-
1.Select Start, Programs, SQL Developer.
2.If prompted, enter SYSADM in the Login name field and sysadm in the Password field.
2.Click the OK button to connect.
4.In the Connections pane, select the database labeled PS85_SYSADM
5.Enter this query in the SQL pane:
SELECT * FROM DBA_OBJECTS
WHERE OBJECT_NAME LIKE 'PS%'
ORDER BY OBJECT_NAME
6. Run the query by pressing the F9 key or clicking the green triangle.
7. Click within the Results pane and press Ctrl-End to place your cursor at the end of the results.
8. Enter and run this query:
SELECT * FROM DBA_OBJECTS
WHERE OBJECT_NAME LIKE 'PS_PSU%'.
9. Enter and run this query:
SELECT * FROM PSPNLDEFN
The structure of the Peopletools PSPNLDEFN table determines the fields and characteristics by which all pages are defined for display. The rows are individual pages and their respective properties.
10. Enter and run this query:
SELECT * FROM PS_PSU_STUDENT_TBL
This is an application data table. The structure of this table is defined in a row of the PSRECDEFN table and a set of rows in the PSRECFIELD table. The PS_PSU_STUDENT_TBL table holds the actual data about students.

PeopleSoft Application Designer