Summarizing and Archiving Project Data

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

This chapter provides an overview of the data summary and data archive processes and discusses how to:

See Also

Enterprise PeopleTools 8.48 PeopleBook: Data Management

Click to jump to parent topicUnderstanding the Data Summary Process

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.

This section discusses:

Click to jump to top of pageClick to jump to parent topicSummarization Process Flow

This diagram shows the Project Data Compression process:

Project Data Compression process

Data compression is a four-step process:

  1. Copy the detailed transaction rows for a specified date range 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.

Click to jump to top of pageClick to jump to parent topicTables 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:

Click to jump to top of pageClick to jump to parent topicSummary Transactions

The Project Data Compression process summarizes transaction data within a date range. After removing all detail integration information, the resulting summary rows retain the date range begin date in the Transaction Date field (TRANS_DT), and the date range end date in the Accounting Date field (ACCOUNTING_DT).

For example, assume that you run the Project Data Compression process with a date range of 5/1/03 to 5/31/03 on these transaction rows:

ACTIVITY_ID

ANALYSIS_TYPE

RESOURCE_TYPE

TRANS_DT

ACCOUNTING_DT

FOREIGN_AMOUNT

MFG

ACT

MATER

5/1/2003

5/15/2003

1200 USD

MFG

ACT

MATER

5/5/2003

5/15/2003

1500 USD

MFG

ACT

MATER

5/17/2003

5/30/2003

3000 USD

MFG

ACT

MATER

5/26/2003

5/30/2003

1700 USD

The process summarizes the rows into this single line:

MFG

ACT

MATER

5/1/2003

5/31/2003

7400 USD

The Project Data Compression process groups the summary rows first by project business unit, project, activity, and general ledger business unit. The sequence of fields that the system uses to group summary rows at a more granular level are based on platform.

Click to jump to top of pageClick to jump to parent topicIntegration Processes

When transactions are summarized, the Project Data Compression process does not retain any information pertaining to the systems from which the individual transactions are received. The process does not retain journal lines, although it does retain journal header information.

Note. You can remove more detail from transactions during summarization by removing fields from the Project Summary History table that contain unwanted data and modifying the Project Data Compression process. Conversely, you can retain integration information from specific systems by adding the fields to the Project Summary History table and modifying the Project Data Compression process. However, these modifications to the Project Data Compression process require customizations that are not delivered with the PeopleSoft Project Costing system. Modifications to the delivered code are not supported by PeopleSoft Development.

Click to jump to parent topicUnderstanding the Data Archive Process

This section lists a prerequisite and discusses:

Click to jump to top of pageClick to jump to parent topicPrerequisite

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

Click to jump to top of pageClick to jump to parent topicArchive Process Flow

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

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

Project Data Compression process followed by the archive process

Click to jump to top of pageClick to jump to parent topicData Archive Manager

You archive data in PeopleSoft Project Costing by using the Data Archive Manager. Data Archive Manager provides an integrated and consistent framework for archiving data from PeopleSoft applications. Use the Data Archive Manager to:

The Data Archive Manager requires these components to archive data to history tables:

PeopleSoft Project Costing delivers archive history tables, archive object definitions, archive query definitions, and archive template definitions to archive project data based on business unit, project, or activity. You can modify the archive definitions, if needed, using the Data Archive Manager. You can also create your own archive definitions to archive other types of data.

Archive Object Definitions

Each table from which you archive data must be associated with a history table. An archive object definition identifies the tables that contain the data to archive and the history tables that are updated for each table.

Base tables contain all key fields by which data is archived from all other tables in the archive object. Each archive object has only one base table. PeopleSoft Project Costing delivers archive objects that contain only one archiving record, which is used as the base table for the object.

This table lists the archive object definitions that PeopleSoft Project Costing delivers:

Archive Object

Description

Archiving Record

Archive History Table

PC_RESOURCE_ARCHIVE

Project Resource Archive

PROJ_RES_ARCH (base table)

PC_RES_ARCH_H

PC_SUMMARY_ARCHIVE

Project Summary Archive

PROJ_SUM_ARCH (base table)

PC_SUM_ARCH_H

The archive history tables are identical copies of the transaction history and summary tables with two additional fields—Archive ID (PSARCH_ID) and Batch Number (PSARCH_BATCHNUM). Data in archive history tables is keyed by the archive template—also known as archive ID—and batch number for an archive run. You can run a query on the archive history tables if you want to see all transactions that are selected to archive.

See Enterprise PeopleTools 8.48 PeopleBook: Data Management, "Using PeopleSoft Data Archive Manager," Managing Archive Objects.

Archive Query Definitions

Queries are defined using PeopleSoft Query and contain the selection criteria to archive data from the transaction history and summary tables to archive history tables. Queries that are used by the Data Archive Manager are of the type Archive. Users select the archive query at runtime.

The following table lists the archive query definitions that PeopleSoft Project Costing delivers. The first three queries select rows from the PROJ_RES_ARCH table for specific business units, projects, and activities. The next three queries select rows from the PROJ_SUM_ARCH table for specific business units, projects, and activities.

Query Name

Description

PC_ARCHIVE_RES1

Resource by Business Unit

PC_ARCHIVE_RES2

Resource by Business Unit, Project

PC_ARCHIVE_RES3

Resource by Business Unit, Project, Activity

PC_ARCHIVE_SUM1

Summary by Business Unit

PC_ARCHIVE_SUM2

Summary by Business Unit, Project

PC_ARCHIVE_SUM3

Summary by Business Unit, Project, Activity

Review these queries to determine whether you want to add additional selection criteria for the transactions that are to be archived.

Note. Use the advanced search page in the PeopleSoft Query Manager component (QUERY_MANAGER) to search for archive query definitions. Select Archive as the Query Type. Enter PC in the Query Name field to view a list of PeopleSoft Project Costing archive query definitions.

See Enterprise PeopleTools 8.48 PeopleBook: PeopleSoft Query.

Archive Template Definitions

To archive data, you select an archive template definition on the Archive Data to History run control page. Each archive template definition includes one or more archive object definitions and archive query definitions that are used to select the data.

This table lists the archive template definitions that PeopleSoft Project Costing delivers:

Archive Template

Description

Archive Object

Query Name

PC_RES

Project Resource

PC_RESOURCE_ARCHIVE (base object)

PC_ARCHIVE_RES1

PC_ARCHIVE_RES2

PC_ARCHIVE_RES3

PC_SUM

Project Resource Summary

PC_SUMMARY_ARCHIVE (base object)

PC_ARCHIVE_SUM1

PC_ARCHIVE_SUM2

PC_ARCHIVE_SUM3

Users manage historical data by archive template and batch number of the run control. This granularity enables users to selectively roll back an archive process, if necessary, rather than rolling back all of the data in the history table.

See Enterprise PeopleTools 8.48 PeopleBook: Data Management, "Using PeopleSoft Data Archive Manager," Managing Archive Templates.

Click to jump to parent topicSummarizing and Viewing Transaction Row Data

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 will be required by downstream processes. For example, if you create accounting entries using the Process Project Accounting Application Engine process (PSA_ACCTGGL), always send detailed transactions to the general ledger before you summarize transaction data.

Click to jump to top of pageClick to jump to parent topicPages Used to Summarize and View Transaction Row Data

Page Name

Object Name

Navigation

Usage

Compress Project Data

PC_COMPRESS

Project Costing, Utilities, Compress Project Data, Compress Project Data

Summarize transaction rows for a given period.

Transaction Detail History

PROJ_RES_ARCHIVE

Project Costing, Utilities, Transaction Detail History, Transaction Detail History

View the original, uncompressed transaction rows after running the Project Data Compression process.

Transaction Summary History

PROJ_SUM_ARCHIVE

Project Costing, Utilities, Transaction Summary History, Transaction Summary History

View compressed data that results from the Project Data Compression process.

Click to jump to top of pageClick to jump to parent topicSummarizing Project Data

Access the Compress Project Data page.

Option

Select the parameters for the Project Data Compression process. Options are:

All: The process runs for all activities.

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.

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. The specified From Date becomes the transaction date for the compressed rows. The specified Through Date becomes the accounting date for the compressed rows.

Transaction Detail

Click to access the Transaction List search page to find and view resource transactions belonging to a project's activity.

Purge History without Archive

Select to purge transactions from the Project Transaction History table and Project Summary History table.

Note. You cannot view historical data or archive data that is purged from these tables.

Archive History Data

Click to access the Archive Data to History run control page to archive data using the Data Archive Manager Application Engine process (PSARCHIVE).

Note. Archive data after you complete the Project Data Summary process.

Run

Click to initiate the Project Data Compression process.

See Also

Enterprise PeopleTools 8.48 PeopleBook: PeopleSoft Process Scheduler

Click to jump to top of pageClick to jump to parent topicViewing Data After the Summary Process

After you run the Project Data Compression process, access the Transaction Detail History page 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 to view summarized data that is stored in the Project Summary History table.

Click to jump to parent topicArchiving Data

This section discusses how to archive data using the Data Archive Manager.

Click to jump to top of pageClick to jump to parent topicPages Used to Archive Data

Page Name

Object Name

Navigation

Usage

Archive Data to History

PSARCHRUNCNTL

  • PeopleTools, Data Archive Manager, Archive Data to History

  • Click Archive History Data on the Compress Project Data page.

Select an archive template, query, and action that you want to take in the archiving process.

Define Archive Query Binds

PSARCHRUNQRYBND

Select a query and click Define Binds on the Archive Data to History page.

Click Reset Query Bind Variables to enter run control options that the system uses to select the transactions to archive.

Audit Archiving

PSARCHIVEAUDIT

PeopleTools, Data Archive Manager, Audit Archiving, Audit Archiving

View details of previous archiving processes.

Click to jump to top of pageClick to jump to parent topicArchiving Data By Using the Data Archive Manager

Access the Archive Data to History page.

Use this page to define archive jobs to archive or delete, restore data from transaction history and summary tables, or delete data from archive history tables. Specify the archive template, archive and commit processing options, and submit archive jobs in a batch by using the Process Scheduler. As part of the process, Data Archive Manager prompts you for runtime parameters such as bind variables and which query to use.

See Enterprise PeopleTools 8.48 PeopleBook: Data Management, "Using PeopleSoft Data Archive Manager," Managing Archive Jobs.

The system generates a batch number and archive date time stamp each time that the process runs.

Note. To optimize performance, run the Data Archive Manager process when system usage is low.

Click to jump to top of pageClick to jump to parent topicAuditing Archive Data

Access the Audit Archiving page.

Use this page to view details of previous archiving processes. Select the Audit Row Count option on the Archive Data to History page for the system to include a count of the number of rows archived for each archive template, archive batch number, and table combination.

Important! If you select incorrect data to archive, use the Data Archive Manager to remove the data from the archive history tables before you rerun the archive selection process.

See Enterprise PeopleTools 8.48 PeopleBook: Data Management, "Using PeopleSoft Data Archive Manager," Auditing Archive Processes.

Click to jump to parent topicRestoring Archived Data

This section provides an overview of data restoration and lists the page used to restore data.

Click to jump to top of pageClick to jump to parent topicUnderstanding Data Restoration

The Selection option on the Archive Data to History run control page places a copy of the transaction data from the transaction history and summary tables into the archive history tables. The Delete option removes the transaction data from the transaction history and summary tables if it has been archived.

Always delete data from transaction history and summary tables for a specific archive template and batch number combination after you successfully archive the batch to the archive tables. If you delete data from the transaction history and summary tables, you can restore the data from the archive history tables by using the Rollback option on the Archive Data to History run control page. The rollback process restores all data for a specific archive template and batch number.

For example, assume that you archived rows from the Project Summary History transaction table to the Project Archive History table by using archive template PC_SUM and assigning batch number 3. After you archive the data, you will use the Archive Data to History page to run the delete process for the rows in the same template and batch combination. After the rows in archive template PC_SUM and batch number 3 are deleted from the Project Summary History transaction table, then you can run the rollback process to restore the rows, if required.

Note. Do not run the Rollback process for an archive template and batch number combination without first removing the data from the transaction and history tables. Doing so causes a SQL error because the Data Archive Manager will be trying to restore data that already exists.

You cannot restore a subset of data from a particular batch. For example, you cannot specify that you want to restore data for a specific customer or group control.

You can restore data to the transaction history and summary tables as long as it remains in the archive history tables.

Important! Always use a database utility to copy data to a flat file before you delete it from the archive history tables. To restore data to the transaction history and summary tables after you delete it from the archive history tables, you first must manually restore the data to the archive history tables from a flat file.

Click to jump to top of pageClick to jump to parent topicPage Used to Restore Archived Data

Page Name

Object Name

Navigation

Usage

Archive Data to History

PSARCHRUNCNTL

PeopleTools, Data Archive Manager, Archive Data to History

Restore data from an archive template and batch number to transaction history and summary tables.

Click to jump to top of pageClick to jump to parent topicRestoring Data to Transaction Summary and History Tables

Access the Archive Data to History page.

To restore data to transaction summary and history tables, select an archive template and the Rollback option, and enter the batch number for the process run of the data that you want to restore.

If any rows from the selected archive template and batch number combination already exist in the transaction history and summary tables, delete the batch from the transaction history and summary tables before you run the restore process.