Summarizing and Archiving Project Data

In PeopleSoft Project Costing, you summarize data before you archive it. You use the Project Data Compression Application Engine process (PC_COMPRESS) to summarize project data into history tables. Then, you use the PeopleSoft Archive (PSARCHIVE) process to archive data from the history tables.

This chapter provides overviews of the data summary process, feeder and pricing summarization, and the data archive process and discusses how to:

• Summarize and view transaction row data.

• Set up feeder and pricing summarization.

• View and manage compressed data.

See Also

Enterprise PeopleTools PeopleBook: Data Management

Understanding the Data Summary Process

This section discusses:

• Summarization process flow.

• Tables populated by the Project Data Compression process.

• Summary transactions.

• Project compression log.

PeopleSoft Project Costing keeps all life-to-date transactions for each project, resulting in a high volume of data to manage. To reduce storage needs and increase performance, you can summarize transaction data by using the Project Data Compression Application Engine process that retains project costs while deleting some detail and integration information.

The Compress Project Data (PC_COMPRESS) process compresses project transactions based on parameters from the run control. This process:

1. Selects the transactions that are targeted for compression and saves them in history tables.

2. Creates summary transactions and inserts them into the project transactions table.

3. Deletes the original transactions from the project transaction table.

Summarization Process Flow

This diagram shows the Project Data Compression process:

Project Data Compression process

The data compression and restoration process flow that the system performs is:

1. Copy the detailed transaction rows based on the compression template and run control options from the Project Transaction table (PROJ_RESOURCE) to the Project Transaction History table (PROJ_RES_ARCH).

2. Summarize detailed transaction rows in the Project Transaction History table and copy them to the Project Summary History table (PROJ_SUM_ARCH).

3. Delete the original transactions from the Project Transaction table.

4. Copy summarized transactions in the Project Summary History table to the Project Transaction table.

5. Restore rows from the Project Transaction History table to the Project Transaction table as detailed rows.

6. Delete compressed rows in the Project Transaction table.

Tables Populated by the Project Data Compression Process

The Project Data Compression process stores data in transaction history and summary tables. The process updates these tables when you summarize data from the Project Transaction table:

• Project Transaction History (PROJ_RES_ARCH)

• Project Summary History (PROJ_SUM_ARCH)

• Project Resource (PROJ_RESOURCE)

• Project Compression Log (PC_COMPRESS_LOG)

Summary Transactions

The Project Data Compression process summarizes transaction data based on run control parameters, such as date, analysis group, contract, or customized SQL. The Project Data Compression process should be performed only on completed projects and activities. The system does not check the status of a project or activity before running the Project Data Compression process.

A compression template is used to define how project transactions are to be summarized during the process. The template enables users to choose the dates they want to roll up and the rollup calendar. Microsoft and Sybase database platforms have a limit of 32 fields of detail.

For example, a compression template is created using these parameters:

• Date Roll up Level is Monthly.

• Roll up Date to Use is Start of Period.

• Calendar ID is 01 Monthly.

• Dates Selected are Accounting Date and Transaction Date.

The 01 Monthly Calendar for year 2007 is set up:

• Period 01: January 01, 2007 to January 31, 2007

• Period 02: February 01, 2007 to February 28, 2007

• And so on

Before the compression process runs, the detail rows in the PROJ_RESOURCE table are:

After the compression process runs, the detail rows in PROJ_RESOURCE table are:

Project Compression Log

The system logs each compression run in the PC_COMPRESS_LOG table. The log retains compression details such as run date, parameters used, and status. Additional details, such as date option, analysis group, contract, and SQL, are logged if used.

The compression log enables the user to perform actions such as viewing history or archiving, purging, restoring, and rolling back data.

Understanding Feeder and Pricing Summarization

This section discusses:

• The Feeder Transaction Summary process.

• The Pricing Summary process.

• An example to feeder transaction summarization.

The Feeder and Pricing Summarization feature is an optional feature. It reduces the amount of transactions that are written to the Project Costing transaction table (PROJ_RESOURCE) using summarization templates that are specific to various product interface processes and the Project Costing Pricing process.

Feeder and pricing summarization is defined at the installation level. You determine whether you are going to summarize transactions from feeder products such as PeopleSoft Expenses, PeopleSoft General Ledger, Time and Labor, or third party applications. You also determine whether you are going to summarize transactions during the Project Costing Pricing and Variance Pricing processes.

After you determine the areas in which you want to summarize your data, you must set up summarization templates. Summarization templates must be defined for each area defined on the Project Costing Integration page. You determine the fields in which the system uses for summarization and the level that the data is summarized.

When you run the integration and pricing processes, the system uses the summarization templates to reduce the number of transaction rows that are written to PROJ_RESOURCE.

The Feeder Transaction Summary Process

When running an integration process, such as PC_EX_TO_PC, the data is added to a new staging table PC_xx_DETAIL, where xx is the product abbreviation, such as PC_EX_DETAIL. The integration process looks for a transaction summarization template to determine the summarization rules. If a transaction summarization template is found, the integration process uses the rules to summarize the data that is inserted into the project transaction table (PROJ_RESOURCE).

A benefit of the detail table is that you can drill back to the detail when viewing transactions on the Transaction List page.

Note. When running the third-party integration process, there are no corresponding detail tables. Summarized transactions are loaded directly into the project transaction table and there is no drill back capability because the detail exists on another system.

This diagram illustrates the process flow of the PeopleSoft Expenses, Time and Labor, General Ledger, and Third-Party interface processes:

Transaction Summarization process flow

The Pricing Summary Process

When the Pricing process (PC_PRICING) runs, the process takes transactions from the project transaction table (PROJ_RESOURCE) and identifies source rows that are eligible for pricing. The process then generates target rows and looks for a pricing summarization template. If a pricing summarization template is found, the process uses the pricing summarization rule to summarize the data, create target rows, and update the project transaction table (PROJ_RESOURCE).

This diagram illustrates the process flow of the Project Costing pricing summarization process when pricing is called separately from the feeder interface process:

Pricing Summarization Separate from Feeder Interface process flow

This diagram illustrates the process flow of the Project Costing pricing summarization process when the Pricing process is called with an individual feeder interface process:

Pricing Summarization Called from Feeder Interface process flow

Note. The Pricing Options group box, on the Project Costing Options page, determines if the Pricing process runs directly from the feeder interface processes.

Example of Feeder Transaction Summarization

This example shows the difference in the amount of rows that are written to the Project Transaction table when not using transaction summarization templates versus when using transaction summarization templates. The actual level of summarization and the number of transactions that are written to the Project Transaction table is determined by the applicable summarization template.

Note. Field names have been abbreviated in these examples.

When not using transaction summarization templates, these three transactions are written to the Project Transaction table (PROJ_RESOURCE):

When using transaction summary templates for expense transactions, these transactions are written to the Expense Detail table (PC_EX_DETAIL):

When using transaction summary templates for expense transactions, these transactions are written to the Project Transaction table (PROJ_RESOURCE):

Understanding the Data Archive Process

This section lists a prerequisite and discusses the archive process flow.

Prerequisite

Before you can archive data, you must run the Project Data Compression process to populate the Project Transaction History table and the Project Summary History table.

Archive Process Flow.

This diagram illustrates how the archive process follows the data compression process in PeopleSoft Project Costing:

The project data compression process is followed by the archive process

The data compression, archiving, and restoration process flow that the system performs is:

1. Copy the detailed transaction rows based on the compression template and run control options from the Project Transaction table (PROJ_RESOURCE) to the Project Transaction History table (PROJ_RES_ARCH).

2. Summarize detailed transaction rows in the Project Transaction History table and copy them to the Project Summary History table (PROJ_SUM_ARCH).

3. Delete the original transactions from the Project Transaction table.

4. Copy summarized transactions in the Project Summary History table to the Project Transaction table.

5. Archive or copy rows from the Project Transaction History table to the Project Transaction Archive table, and from the Project Summary History table to the Project Archive History table.

6. Delete archived rows in the Project Transaction History table and in the Project Summary History table.

7. Roll back archived rows from the Project Transaction Archive table to the Project Transaction History table, and from the Project Archive History table to the Project Summary History table.

8. Restore rows from the Project Transaction History table to the Project Transaction table as detailed rows.

9. Delete compressed rows in the Project Transaction table.

You must complete these steps to compress and archive data in PeopleSoft Project Costing:

1. Run the Project Data Compression process to summarize transaction data into history tables.

   You must complete this recurring step before each occurrence of the archive process. The archive process relieves only the Project Transaction History table and Project Summary History table. Archiving data does not affect the Project Transaction table.

2. Select the archive action to initiate the archive jobs that move summarized project data into the archive tables.

   For this recurring step, you select the archive action from the Project Compression Tracking page for the set of compressed data that you want to archive.

   By selecting the archive action, the archive jobs are automatically submitted in the background.

3. (Optional) Roll back data from the archive tables to the history tables to recover deleted data as needed.

4. (Optional) Restore data from the transaction history table to the project transaction table to recover detailed data as needed.

5. (Optional) Purge data from the Project Transaction History table and Project Summary History table.

   You can delete data from the transaction history and summary tables without archiving the data by selecting the purge option from the Project Compression Tracking page. You should only select this option if you no longer need to retain historical transaction data. Consider carefully before you select this option.

6. (Optional) Audit archive jobs.

   The Data Archive Manager retains information about each process that is executed, such as who ran the process, when they ran it, which archive ID and record were affected, and what SQL statement was executed.

Summarizing and Viewing Transaction Row Data

This section provides an overview of the Compress Project Data (PC_COMPRESS) process and discusses how to:

• Set up the project compression template.

• Summarize project data.

• Viewing data after the compression process.

Understanding the Compress Project Data (PC_COMPRESS) Process

The Compress Project Data (PC_COMPRESS) process compresses project transactions based on parameters from the run control. This process:

1. Selects the transactions that are targeted for compression and saves them in history tables.

2. Creates summary transactions and inserts them into the project transactions table.

3. Deletes the original transactions from the project transaction table.

After summarizing transaction data, drill-to-source capabilities are no longer available unless you edited the Project Summary History table to retain integration details.

Note. Do not summarize detailed transaction data that is required by downstream processes. For example, if you create accounting entries using the Process Project Accounting Application Engine process (PSA_ACCTGGL), then always send detailed transactions to the general ledger before you summarize transaction data.

Pages Used to Summarize and View Transaction Row Data

Setting Up the Project Compression Template

Access the Project Compression Template page (Setup Financials/Supply Chain, Product Related, Project Costing, General Options, Compression Template).

The project compression template defines how project transactions are to be summarized during the Compress Project Data (PC_COMPRESS) process. The Project Compression Template page enables you to set up templates that specify the level of detail retained on the summary rows, the amount to summarize, and the date rollup option. The project compression template should be created before running the Compress Project Data (PC_COMPRESS) process.

Summarize Amounts

Date Rollup Options

Compression Detail

Select the fields that you want to retain for the summary rows. The fields are grouped into logical sections that are collapsible and expandable.

The Required Details section includes all the fields that must be retained. Each check box in this section is selected and not editable.

The more fields that you select, the more summary rows the system retains, reducing the effectiveness of the compression. Microsoft and Sybase database platforms have a maximum of 32 number fields that can be selected. The Project Business Unit Amount, Quantity, and Source Amount fields are not included in the maximum.

Summarizing Project Data

Access the Compress Project Data page (Project Costing, Utilities, Compress Project Data, Compress Project Data).

Option	Select the parameters for the Project Data Compression process. Options are: Business Unit: The process runs for a selected business unit. Business Unit/Project: The process runs for a selected business unit and project. Business Unit/Project/Activity: The process runs for a selected business unit, project, and activity. Custom SQL Text: The process runs for the values specified in the Where Clause field. Note. Only advanced users should use this option. Incorrect SQL can cause errors in the Compress Project Data process. The system does not check the SQL for correct syntax.
Date Option	Select Accounting Date or Transaction Date to specify the transactions to compress based on a time period.
From Date and Through Date	Enter the time period From Date and Through Date of the transactions to compress.
Transaction Detail	Click to access the Transaction List search page to find and view resource transactions belonging to a project activity.
Where Clause	This field appears only when Custom SQL Text is selected in the Option field. Enter your own SQL text that specifies which rows to include in the compression. Note. Only advanced users should use this option. Incorrect SQL can cause errors in the Compress Project Data process. The system does not check the SQL for correct syntax.
Compression Template	Select a compression template. The compression template is defined using the Project Compression Template page.
Contract	Select a contract number. This field appears only if PeopleSoft Contracts is licensed. This option enables you to filter the transaction rows by contract.
Analysis Group	Select All or Specific. This option enables you to filter the transaction rows by analysis group that contains only those analysis types to be included in the compression.
Project Compression Tracking	Click to access the Compression Log page.

See Also

Enterprise PeopleTools PeopleBook: PeopleSoft Process Scheduler

Viewing Data After the Compression Process

After you run the Project Data Compression process, access the Transaction Detail History page (Project Costing, Utilities, Transaction Detail History, Transaction Detail History) to view detailed data from the original (not summarized) Project Transaction table that is stored in the Project Transaction History table.

Access the Transaction Summary History page (Project Costing, Utilities, Transaction Summary History, Transaction Summary History) to view summarized data that is stored in the Project Summary History table.

Setting Up Feeder and Pricing Summarization

This section discusses the steps to setting up and using Feeder and Pricing Summarization.

Pages Used to Set up and Use Feeder and Pricing Summarization

Steps to Setting Up and Using Feeder and Pricing Summarization

To set up and use feeder and pricing summarization:

1. Select the desired product and pricing options in the Summarization Options group box on the Project Costing Integration page.

2. Define summarization templates using the Summarization Template page.

3. Run the integration processes from feeder applications.

4. Run the Pricing process to price transactions in the PROJ_RESOURCE table if the Pricing process is not automatically called from the feeder interface process.

5. Drill to the transaction detail from the Transaction List page.

Viewing and Managing Compressed Data

This section provides an overview of viewing and managing compressed data and discusses how to:

• View the details of the Compress Project Data process.

• View more details of the Compress Project Data process.

• Manage compressed data.

Understanding How To View and Manage Compressed Data

The Project Compression Log page serves two purposes:

• It is a log of every compression run that displays the run date and run parameters used.

• It allows the user to perform actions on the compressed data, such as: archiving, purging, restoring, and rolling back compressed data.

Pages Used to View and Manage Compressed Data

Viewing the Details of the Compress Project Data Process

Access the Project Compression Tracking page (Project Costing, Utilities, Compress Project Data).

Project Compression Tracking: Details tab

Use this page to view process details.

Project Compression Tracking: More Details tab

This page displays additional details from the compression run, such as analysis group and contract ID, if specified. In addition, users can view the SQL used if the Compress Project Data process used customized SQL text.

Project Compression Tracking: Actions tab