Configuring PeopleSoft Adaptor for Project Grants Management

Oracle BI Applications now provides out-of-the-box support for PeopleSoft Grants Management version 9.x, which helps you plan, manage, and track your institution's grant activities.

This topic explains how to enable PeopleSoft Adaptor for Project Grants Management.

Overview

Project Grants Management Analytics in Oracle BI Applications helps analyze the following key areas of PeopleSoft Grants:

  • Award Activity: Award activity includes detailed funding or budget information associated with an award. The following source tables contain grants data related to Award Activity:

    • PS_PC_BUD_DETAIL

    • PS_GM_AWARD

    • PS_PROJ_RESOURCE

    • PS_CA_DETAIL_PROJ

    Key metrics in this area are:

    • Direct Funding Amount

    • Indirect Funding Amount

    • Cost Share Direct Amount

    • Cost Share InDirect Amount

    • Award Amount

    • Funded Amount

    • Credit Amount

  • Award Analysis: Award Analysis addresses analytics for awards. It helps analyze the trends for awards that an institution receives or anticipates receiving. The following source tables contain grants data related to Award Analysis:

    • PS_GM_AWARD

    • PS_GM_AWARD_KEYWORD

    • PS_PROJECT_TEAM

Schemas and Dimensions

To support these key areas, Oracle Business Analytics Warehouse provides the following star schemas:

Star Schema Description

Project Award Activity (Funding)

  • Includes detailed funding or budget information associated with an award.

  • Includes the “actuals” activities, such as cost, revenue, and billing, which are recorded against an award.

  • Includes other award-specific information such as the following:

    • Budget amounts that are posted to date

    • Amount received

    • Amount spent

    • Amount billed

    • How awards this year compare to those from last year

    • Amount awarded, to date, for Diabetes research programs

    • Sponsor awards that have funded Diabetes research programs

Project Award Analysis

  • Helps users analyze award details such as Keywords, Projects, and Resources.

  • Includes the count metric exposed in the Fact folder to drive the analysis.

  • Includes Award- and Project-specific information such as the following:

    • Award or Project keywords

    • Resources that are assigned for an Award or Project

    • Projects that are associated with an Award

These star schemas support the following key dimensions:

Dimension New or Existing? Description
Award New  

Award Status

 New

Status of the award.

Sample values are Accepted, Closed, Holding, Pending, Refused, Pre-Award, Under Negotiation, and Withdrawn.

Award Type

 New

The type of award. This is an important dimension because it tells how well the institution is funded and the potential risks of existing funding. Different types of awards carry different risks and require different types of actions from the institution.

Sample values are Grant, License, Loan, Contract, Clinical Trial, and Cooperative Agreement.

Award Purpose

 New

Purpose of the award.

Sample values are Basic Research, Clinical Research, Construction, and Training.

Keyword

 New

Keyword associated with the award. An award can have multiple keywords associated with it.

Sample values are Bioengineering, Diabetes, and Learning Disabilities.

Award Contract

 Existing The contract associated with the award. A contract mentions the funded amount, and it is needed to process any billing-related activity.

Principal Investigator

 Existing This dimension is the same as Employee.
Institution > Department New

Institution and department that receives the award. Sample values are:

  • Purdue University > School of Civil Engineering

  • Stanford University > School of Computer Sciences
Award Budget Period New The budget period for the award.
Sponsor Existing This dimension is the same as Customer. It is required to understand how well the institution is funded. For example, grants from government organizations are typically more risky than those from private institutions.
Fiscal Calendar Existing The calendar to be used for financial purposes. A non-financial purpose could be that award analyses may also have to be reported on the basis of a Calendar year. For example, when looking at the performance or tenure of investigators (PIs).
Business Unit Existing  
Resources New People working on the projects. Resource assignment is done at the project level.

For existing facts, the following Award dimensions are supported:

  • Project Cost fact

  • Project Budget fact

  • Project Forecast fact

  • Project Revenue fact

  • Project Invoice fact

  • Project Commitment fact

Security

In the target environment, PeopleSoft Grants data is secured using Project and Business Unit security, and therefore, the respective initialization blocks must be enabled. This is explained in the Integration Steps section that follows.

Integration Steps

Perform the following steps to implement Project Grants Management Analytics:

  1. Enable the Grants-specific initialization blocks in the RPD file, as follows:

    1. In Oracle BI EE Administration Tool, edit the BI metadata repository (for example, OracleBIAnalyticsApps.rpd).

    2. Choose Manage, then Variables.

    3. Under Session – Initialization Blocks, open the following initialization blocks, and clear the Disabled check box:

      • Project List Grants PSFT

      • Award Business Unit List Grants PSFT

    4. Save the BI metadata repository (that is, the RPD file).

  2. Define a load plan in Oracle BI Applications Configuration Manager by selecting a data source and then the Grants-specific fact group, PRJAWARD_FG. This selection determines what steps must be performed during the ETL process.

    You can then generate the load plan to build it in the ODI repository, and execute the load plan to perform the ETL process.

    For more information about working with Oracle BI Applications load plans, see the Oracle BI Applications ETL Guide.