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:
Summarize and view transaction row data.
Archive data.
Restore archived data.
See Also
Enterprise PeopleTools 8.48 PeopleBook: Data Management
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:
Summarization process flow.
Tables populated by the Project Data Compression process.
Summary transactions.
Integration processes.
This diagram shows the Project Data Compression process:
Project Data Compression process
Data compression is a four-step process:
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).
Summarize detailed transaction rows in the Project Transaction History table and copy them to the Project Summary History table (PROJ_SUM_ARCH).
Delete the original transactions from the Project Transaction table.
Copy summarized transactions in the Project Summary History table to the Project Transaction table.
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)
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.
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.
This section lists a prerequisite and discusses:
Archive process flow.
Data Archive Manager.
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.
You must complete these steps to archive data in PeopleSoft Project Costing:
Create archive history tables.
This is a one time step that PeopleSoft completed for you by delivering archive history tables that you need to archive project data based on business unit, project, or activity.
Set up the archive definitions—archive objects, archive queries, and archive templates.
This is a one time step that is required for each type of data that you want to archive. PeopleSoft completed this step for you by delivering the required archive definitions for project data.
Run the Project Data Compression process to summarize transaction data into history tables.
This is a recurring step that you must complete prior to 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 impact the Project Transaction table.
Define and run the archive job to move summarized project data into the archive tables.
This is a recurring step that is known as the selection process because you are selecting the archive template that specifies the data to archive during this batch job.
Submit archive jobs using the PeopleSoft Process Scheduler.
(Optional) Restore (roll back) data from the archive tables to the history tables to recover deleted data as needed.
(Optional) Delete data from the Project Transaction History table and Project Summary History table.
You can delete data from the transaction history and summary tables only if the data is already archived in the archive history tables.
Data will exist in parallel in the Project Transaction History table and the Project Transaction Archive table until you clean up the transaction table by deleting archived data. Similarly, data will exist in parallel in the Project Summary History table and the Project Archive History table until you delete archived data from the transaction summary table.
(Optional) Audit archive jobs.
The Data Archive Manager retains information on each process that is executed—who ran the process, when they ran it, which archive ID and record were affected, and what SQL statement was executed.
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
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:
Predefine logical sets of data to archive.
After archiving data, purge the same logical sets of data from the transaction tables.
Restore the logical sets of data.
Delete data from archive tables.
The Data Archive Manager requires these components to archive data to history tables:
Archive object definitions.
Archive query definitions.
Archive template definitions.
Archive job definitions.
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.
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.
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.
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.
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.
Page Name |
Object Name |
Navigation |
Usage |
PC_COMPRESS |
Project Costing, Utilities, Compress Project Data, Compress Project Data |
Summarize transaction rows for a given period. |
|
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. |
|
PROJ_SUM_ARCHIVE |
Project Costing, Utilities, Transaction Summary History, Transaction Summary History |
View compressed data that results from the Project Data Compression process. |
Access the Compress Project Data page.
See Also
Enterprise PeopleTools 8.48 PeopleBook: PeopleSoft Process Scheduler
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.
This section discusses how to archive data using the Data Archive Manager.
Page Name |
Object Name |
Navigation |
Usage |
PSARCHRUNCNTL |
|
Select an archive template, query, and action that you want to take in the archiving process. |
|
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. |
|
PSARCHIVEAUDIT |
PeopleTools, Data Archive Manager, Audit Archiving, Audit Archiving |
View details of previous archiving processes. |
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.
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.
This section provides an overview of data restoration and lists the page used to restore data.
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.
Page Name |
Object Name |
Navigation |
Usage |
PSARCHRUNCNTL |
PeopleTools, Data Archive Manager, Archive Data to History |
Restore data from an archive template and batch number to transaction history and summary 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.