PeopleSoft Campus Solutions Warehouse 9.1 PeopleBook

Understanding the EPM Warehouses

This chapter provides an overview of the EPM Warehouses.

Overview of PeopleSoft EPM Warehouses

PeopleSoft delivers six EPM warehouses that provide you with the tools and technology to manage your organization's information that is used for reporting and analysis. Each warehouse is divided into multiple subject areas, or data marts. Each data mart is aligned with a business process, which enables you to answer strategic questions essential to your organization's bottom line.

The following sections describe these PeopleSoft EPM Warehouses:

PeopleSoft Customer Relations Management (CRM) Warehouse.
PeopleSoft Campus Solutions (CS) Warehouse
PeopleSoft Financial Management Solutions (FMS) Warehouse.
PeopleSoft Financials Warehouse for Public Sector and Higher Education.
PeopleSoft Human Capital Management (HCM) Warehouse.
PeopleSoft Supply Chain Management (SCM) Warehouse.

For detailed information about EPM, the Multidimensional Warehouse, and required setup tasks for the EPM warehouses, please refer to the PeopleSoft Enterprise Performance Management Fundamentals PeopleBook.

See Oracle's PeopleSoft Enterprise Performance Management Fundamentals 9.1 Preface.

CRM Warehouse

The CRM warehouse enables you to create reports related to these business processes.

Marketing
Support
Sales

The CRM Warehouse consists of these data marts:

Sales
Service
Marketing
Customer Segment

See PeopleSoft Customer Relationship Management Warehouse Preface.

Campus Solutions Warehouse

The Campus Solutions warehouse enable you to create reports related to these business processes:

Student Recruiting
Student Admission Application
Application Evaluations
Student Responses
External Test Scores
External Education
Class Meeting Patterns
Course Catalog, Class Scheduling, Instructor Workload
Program Activation & Management
Student Career Term Record Management
Enrollment
Award
Student Payments

The Campus Solutions warehouse consists of the following data marts:

Admissions and Recruiting
Campus Community
Student Records
Student Financials

See Understanding the Campus Solutions Warehouse.

FMS Warehouse

The Financial Management Solutions Warehouse enables you to create reports related to these business processes:

Order Fulfillment
Procurement
Financial Control and Reporting
Commitment Control
Grant Analytics
Project Management
Asset Lifecycle Management

The Financial Management Solutions Warehouse consists of these data marts:

Receivables
Payables
General Ledger and Profitability
Enterprise Services Automation (ESA)

See PeopleSoft Financial Management Solutions Warehouse Preface.

Financials Warehouse for Public Sector and Higher Education

The Financials Warehouse for Public Sector and Higher Education enables you to create reports related to these business processes:

Procurement
Spend
Order Fulfillment
Financial Control and Reporting
Commitment Control
Grant Analytics
Project Management
Asset Lifecycle Management

The Financials Warehouse for Public Sector and Higher Education consists of these data marts:

Procurement (from the Supply Chain Management Warehouse)
Spend (from the Supply Chain Management Warehouse)
Receivables (from the Financial Solutions Management Warehouse)
Payables (from the Financial Solutions Management Warehouse)
General Ledger and Profitability (from the Financial Solutions Management Warehouse)
Enterprise Services Automation (from the Financial Solutions Management Warehouse)

See PeopleSoft Financials Warehouse for Public Sector and Higher Education Preface.

HCM Warehouse

The HCM Warehouse enables you to create reports related to these business processes:

Deployment
Reward
Development
Recruiting

The HCM Warehouse consists of these data marts:

Workforce Profile
Compensation
Learning and Development
Recruiting

See PeopleSoft Human Capital Management Warehouse Preface.

SCM Warehouse

The Supply Chain Warehouse enables you to create reports related to these business processes:

Order Fulfillment
Procurement
Production

The Supply Chain Warehouse consists of these data marts:

Fulfillment and Billing
Procurement
Spend
Inventory
Manufacturing
Supply Chain Planning

See PeopleSoft Supply Chain Management Warehouse Preface.

Components of PeopleSoft EPM Warehouses

PeopleSoft delivers the following content with an EPM warehouse:

Extract Transform and Load (ETL) component
Infrastructure tables and tools
Security tables
Staging tables
Multidimensional Warehouse tables
Data Models
Measures

Each bullet is discussed in more detail below.

Extract Transform and Load (ETL) Component

PeopleSoft EPM warehouses are delivered with the IBM WebSphere DataStage ETL tool and prepackaged ETL jobs. Together they enable you to extract data from PeopleSoft source transaction systems, integrate your data into a single database, and populate prepackaged data models which optimize your data for analysis and reporting.

There are also several ETL objects that support the ETL process, such as routines, environment parameters, and hashed files.

See Preparing to Load Source Data Into EPM.

Infrastructure Tables and Tools

PeopleSoft EPM warehouses are delivered with infrastructure tables and tools, which serve as the underlying framework that supports the EPM Warehouses. Some examples of core infrastructure tables include the Currency Code (CURRENCY_CD_TB) table, which enables you to manage financial information in multiple currencies, and the Unit of Measure (PS_UNITS_TBL) table, which determine how specific resources are quantified.

Some examples of infrastructure tools provided by PeopleSoft include the Country and State Information component and the Business Unit Wizard, which automates the steps required to set up warehouse business units and set IDs

See EPM Core Infrastructure and ETL Setup Tasks.

Security Tables

EPM security controls access to specific data within the EPM database and enables you to grant user-access to specific rows, columns, fields, or dimensions in the multidimensional warehouse. An example of the security tables delivered with an EPM warehouse is the Security Join Table, which stores the security profiles for users and the corresponding dimension values for which they have access.

See Setting Up EPM Security.

Staging Tables

The Operational Warehouse - Staging tables act as an entry-point for your PeopleSoft source transaction data into EPM, and provide a platform to offload, consolidate, and stage your source transaction data in preparation for migration to prepackaged data models.

See Operational Warehouse - Staging (OWS).

Multidimensional Warehouse Fact Tables

In an EPM warehouse, fact tables typically consist of numerical values, such as quantity, sales, and revenue, that relate to elements of your business. Fact tables help to quantify a organization's activities. In addition, fact tables usually contain an additive business performance measurement. That is, you can usually perform arithmetic functions on facts. EPM multidimensional fact tables contain numeric performance measurement information that is used in multidimensional reports that categorize your business.

Multidimensional warehouse fact tables can contain either transactional data or snapshot data:

Transactional data: A transaction-dated fact source stores data by tracking individual events and when they occurred. To select the data for a particular date range, you retrieve all rows of data that have transaction dates between the start and end date in the desired range. For example, assume that you are measuring the number of units sold and you track the information using a transaction-dated structure. A row of data exists for each time a unit is sold, and each row has a date, or timestamp. To measure how many units sold in a week, you add all of the transactions—that is, the number of units sold—each day in that week.

In some situations, the application adds these events together over time to calculate an aggregated value.
Snapshot data: An as of dated fact source stores the data based as a snapshot of the data at a given point in time. This snapshot often represents events across multiple time periods. It reduces the amount of data stored on a system, because each individual transaction is not stored. For example, to track organization head count by month, you can determine how many employees you have on the last day of every month. You store that information instead of storing every new hire transaction and attempting to aggregate each one to the month.

Because this information is typically aggregated, this type of data is usually not additive across multiple as of dated snapshots. To aggregate this type of data, you typically use the last snapshot taken for the specific time period that you want to aggregate.

In some EPM warehouses there are factless fact tables, a fact table that does not have an amount field that you sum to derive the value that you want. Instead, it allows you to do counts based on the key relationships. For example, a question such as "How many employees participate in the 401(K) program?" could likely be answered by querying a factless fact table. Factless fact tables are not empty, rather, they are another type of fact table commonly used in data modeling.

Note. MDW fact tables use the following naming convention: F_[table name].

See MDW Fact Tables.

Multidimensional Warehouse Dimension Tables

In an EPM warehouse, dimension tables are sets of related attributes that you use to group or constrain fact-based information when reporting. Dimension tables are descriptive, usually text (in character data type), non-additive (that is, they cannot be used for arithmetic computations), and often hierarchical. In terms of data analysis, dimensions can be thought of as criteria, such as time, product, and location, used to locate a particular piece of data.

For example, in higher education a set of dimensions could be Student, Academic Career, Instructor, and Courses. The Career dimension might include Career, Term, and session attributes. Business intelligence reporting typically makes use of dimension values to filter criteria. For example, the department head of the School of Engineering might filter the data so that a report only displays information relating to that specific school. Dimension table data can originate from a PeopleSoft source system or a flat file.

Note. MDW dimension tables use the following naming convention: D_[table name].

See MDW Dimension Tables.

Shared and Global Dimensions

Certain dimensions, such as Account, Customer, Department, Item dimensions, or Person are used across all EPM warehouses. Conformity of structure in these dimensions is essential to provide a consistent view of data and to easily integrate business measurements between functional warehouses. Therefore, these dimensions are identical in structure and content across all EPM warehouses.

Commonly Used Dimensions

The following table describes dimension tables that are commonly used across EPM warehouses:

Commonly Used Dimension	Description
Business Unit Dimension	Business units are generally defined as distinct operational or organizational entities that maintain their own sets of books or transactional data. You can associate one source system with various types of business units, such as a general ledger business unit, an inventory business unit, and a manufacturing business unit. To facilitate EPM application and EPM foundation processing, a performance business unit (PFBU) is associated with each source business unit. PFBU is used for analytical and reporting purposes and has no equivalent in the source system. Each business unit must belong to one and only one PFBU. All business units that are members of the same PFBU must have the same fiscal calendar and default currency. A business unit can be associated with one or more business functions, as defined by its Business Unit Type attribute. Examples of Business Unit Type are Inventory business unit and General Ledger business unit. The multifunctional business unit can be associated with more than one business function. For example, business units with either Inventory Business Unit Type or Multifunctional Business Unit Type can be associated with the Inventory business function. You can relate one or more business units to a general ledger business unit. If a general ledger business unit has one or more business units associated to it, in the MDW that general ledger business unit is captured as a composite business unit, in additional to being a regular business unit in the Business Unit dimension table. Note. Business units that come from different source systems are different business units, even if they have the same name and the same BUSINESS_UNIT value.
Calendar Dimension	The Calendar dimension stores date-related attributes that are associated with a measure on a specific date. The Calendar dimension has a granularity of one day. In the MDW, the Calendar dimension accommodates storage of one regular, or Gregorian, calendar, plus any number of standard or custom calendars, such as fiscal, manufacturing, and sales calendars. In addition to having a granularity of day, the Gregorian calendar also provides hierarchies of week, month, quarter, and year. Because the application cannot consolidate calendar dates and fiscal patterns in the same hierarchy, the Calendar dimension is in the form of a snowflake dimensional structure. This is necessary because weeks do not roll up into the same hierarchy as months, and therefore require a separate hierarchy. For user-defined calendars, the lowest granularity is also a day, which can be rolled up into a user-defined period, such as fiscal period. User-defined calendars support the concept of detail and summary periods. A detail period consists of one or more days. A summary period consists of one or more detail periods. The user-defined calendar also supports fiscal calendars, which are limited to a specific fiscal year, as well as budget calendars, which can span multiple fiscal years.
Currency Dimension	Because transactional data can exist in any currency in which a organization does business, companies transacting business in multiple countries often must deal with data in multiple currencies. The Currency dimension enables you to present a unified view of your organization's data.
Language	Companies that do business in different geographic areas often process data in different languages. The Language dimension contains a language ID, a two-letter language code, a three-letter language code, and a description. The two and three-letter language codes are based on International Organization for Standardization (ISO) codes. These ISO two and three-letter language codes are not abbreviations for the language, but they do identify a given language or group of languages.
Time Dimension	The Time dimension enables you to properly define a time of day attribute outside of the context of a specific date. This supports situations in which the time-only portion of a calendar is captured—as opposed to date and time. The granularity of the Time dimension is one minute. The Time dimension includes a textural Time Period attribute. This attribute refers to specific periods of time, such as AM or PM.
Unit of Measure Dimension	Measurements, particularly those that relate to the supply chain, can be complicated. For example, manufacturing might measure product in carload lots or pallets. Distribution might want to see everything in shipment cases, while retail can only process items in individual scan units. To satisfy reporting requirements for the various entities that use unit of measure (UOM), the PeopleSoft application presents the measured facts in a single, standard unit of measure, with conversion factors to all of the other possible units of measure in a separate conversion table. Because some units of measure are different when used for different products or items, a unit of measure relationship table used to facilitate a multi-tier hierarchy exists for the Unit of Measure dimension. This multi-tier system helps categorize a unit of measure and its conversion rate by role and conversion type, both of which are attributes of the Unit of Measure relationship table. Some conversions of UOM are standard and are independent from the subject of measurement, such as from meters to feet. However, some conversions depend on a set of attributes, such as shipping vendor, business unit, a particular item, and so on. The Unit of Measure table facilitates this conversion process. Note. You must populate this relationship table according to your particular requirements.
Time Zone Dimension	The Time Zone dimension component of date and time is required if your organization tracks events in different geographical locations situated in different time zones. In this situation, recording the time zone component of date and time is crucial.

Data Models

Each EPM warehouse is delivered with its own set of data models, which are abstract models that define your data and the relationships among the data. Specifically, EPM warehouse data models dimensionalize your data, grouping it into facts and dimensions in a star-schema format based on specific business processes.

See the PeopleSoft EPM Entity Relationship Diagrams located on My Oracle Support.

Measures

PeopleSoft EPM warehouses are delivered with prepackaged measures, which are numerical fact table values that have calculations (such as SUM, COUNT, or AVERAGE) applied to them. For example, the measure SUM(SALES) uses the Sales fact value and applies the SUM calculation to it.

Derived measures are also delivered with EPM warehouses. A derived measure includes a fact value and applies an arithmetic operator to it. Arithmetic operators are ADD, SUBTRACT, MULTIPLY, and DIVIDE. An example of a derived measure is SUM(SALES*QTY) where SALES and QTY are each separate fact values and * signifies the arithmetic operator multiply.

EPM Architecture and Data Flow

PeopleSoft EPM warehouses are built on a foundation of infrastructure tables and tools, ETL platform, and staging/multidimensional tables, all of which enable the warehouses to bring together data from different PeopleSoft source systems. Prepackaged data models enable complex analysis and reporting of your data.

To bring source data into an EPM warehouse and prepare your data for reporting, you must run prepackaged ETL jobs that extract information contained in PeopleSoft source systems and load it into multidimensional warehouse data models:

Use the ETL process to load your source data into the OWS.
Use the ETL utility to move data from the OWS to the MDW.
Complete setup of the Multidimensional Warehouse.
Review the chapters that describe the specific data marts that you are licensed to use and complete any additional setup that is necessary. Each data mart might have additional setup or processing steps that you must perform before creating the data mart. Review these steps in the chapter for that data mart in this PeopleBook.

See Implementing PeopleSoft EPM.

This graphic illustrates the various components comprising the EPM architecture and how data flows from source systems to the multidimensional warehouses via the ETL process:

EPM Data Flow

Operational Warehouse - Staging (OWS)

The first step in preparing your data for multidimensional reporting is to load source data from your PeopleSoft source transaction system into the OWS layer. You use PeopleSoft delivered ETL jobs to extract and load the data into the OWS. The ETL process does not transform the source data brought into the OWS, all table and field names and key structures are the same in the OWS as in the corresponding source table.

The ETL process brings dimension records, such as data for business units, calendars, and related language tables, from the source system, as well. In addition to the fields on the OWS tables that match those on the source tables, EPM ads additional fields to facilitate incremental loading (date stamps), and source and error tracking. These can typically be found in the LOAD_OWS_SBR subrecord.

See Operational Warehouse - Staging (OWS).

Operational Warehouse - Enriched (OWE)

If you use the PeopleSoft EPM Analytic Applications in conjunction with the FMS Warehouse, you can use the prepackaged ETL jobs to move OWE data to the MDW layer:

Profitability data (PS_PF_LEDGER_F00) is generated in the Analytic Applications and is stored in the OWE.
Global Consolidation data (GC_CLED_MGT_F00) is generated in the Global Consolidations Analytic Application and stored in the OWE.

Note. Even if you do not use the Analytic Applications or the FMS Warehouse, you still use ETL jobs to move HCM Warehouse external survey data to the OWE before moving it to the MDW.

See Operational Warehouse - Enriched (OWE).

Multidimensional Warehouse (MDW)

After you use ETL jobs to move your source data into the OWS, you use another set of ETL jobs to move your data into the MDW. The MDW is built on the principles of dimensional modeling—that is, logically modeling data for query performance starting from a set of base measurement events. Data in the MDW is grouped as it is related to one or more business processes. Data is in a star schema format—a fact table surrounded by one or more dimension tables. Generally, the star schema is in a denormalized form, which enables more efficient query processing.

In general, the MDW contains data at the most granular level—that is, the lowest level—found in the source system. This provides the most flexible choice regarding how report data is rolled up. The MDW data is based on surrogate keys rather than business keys, as this provides more efficient joining of tables. Values of surrogate keys contain no semantic content and are used specifically to join structures.

See Multidimensional Warehouse (MDW).

Reporting on the EPM Warehouses

In order to leverage your data, the EPM warehouses are delivered with an open reporting platform (open data models), which enable you to add the Oracle Business Intelligence Enterprise Edition reporting tool or another third party reporting tool. Because the PeopleSoft open reporting solution stores the data mart data in relational tables, virtually any reporting tool that has connectivity to the database is able to use them.

See the Oracle Business Intelligence Enterprise Edition (OBIEE) suite of products and documentation.