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
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:
Selects the transactions that are targeted for compression and saves them in history tables.
Creates summary transactions and inserts them into the project transactions table.
Deletes the original transactions from the project transaction table.
This diagram shows the Project Data Compression process:
Project Data Compression process
The data compression and restoration process flow that the system performs is:
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).
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.
Restore rows from the Project Transaction History table to the Project Transaction table as detailed rows.
Delete compressed rows in 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)
Project Resource (PROJ_RESOURCE)
Project Compression Log (PC_COMPRESS_LOG)
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:
Business Unit |
Project |
Activity |
Amount |
Acct Date |
Transaction Date |
US004 |
ABC |
001 |
100.00 |
01/01/2007 |
01/05/2007 |
US004 |
ABC |
001 |
200.00 |
01/15/2007 |
01/15/2007 |
US004 |
ABC |
001 |
500.00 |
02/02/2007 |
01/31/2007 |
US004 |
ABC |
001 |
600.00 |
02/28/2007 |
02/28/2007 |
US004 |
ABC |
001 |
100.00 |
02/28/2007 |
02/01/2007 |
After the compression process runs, the detail rows in PROJ_RESOURCE table are:
Business Unit |
Project |
Activity |
Amount |
Acct Date |
Transaction Date |
US004 |
ABC |
001 |
300.00 |
01/01/2007 |
01/01/2007 |
US004 |
ABC |
001 |
500.00 |
02/01/2007 |
01/01/2007 |
US004 |
ABC |
001 |
700.00 |
02/01/2007 |
02/01/2007 |
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.
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):
ACTIVITY |
RES_ID |
RES_ID_FROM |
CONTRACT |
AN_TYPE |
SHEET_ID |
LINE_NBR |
AMOUNT |
FEEDER_SUM_ID |
A1 |
201 |
201 |
CN1 |
ACT |
EX1 |
1 |
100.00 |
|
A1 |
202 |
202 |
CN1 |
ACT |
EX1 |
2 |
100.00 |
|
A1 |
203 |
203 |
CN1 |
ACT |
EX2 |
1 |
100.00 |
When using transaction summary templates for expense transactions, these transactions are written to the Expense Detail table (PC_EX_DETAIL):
ACTIVITY |
RES_ID |
RES_ID_FROM |
CONTRACT |
AN_TYPE |
SHEET_ID |
LINE_NBR |
AMOUNT |
FEEDER_SUM_ID |
A1 |
201 |
201 |
CN1 |
ACT |
EX1 |
1 |
100.00 |
100 |
A1 |
202 |
202 |
CN1 |
ACT |
EX1 |
2 |
100.00 |
100 |
A1 |
203 |
203 |
CN1 |
ACT |
EX2 |
1 |
100.00 |
101 |
When using transaction summary templates for expense transactions, these transactions are written to the Project Transaction table (PROJ_RESOURCE):
ACTIVITY |
RES_ID |
RES_ID_FROM |
CONTRACT |
AN_TYPE |
SHEET_ID |
LINE_NBR |
AMOUNT |
FEEDER_SUM_ID |
A1 |
201 |
201 |
CN1 |
ACT |
EX1 |
0 |
200.00 |
100 |
A1 |
203 |
203 |
CN1 |
ACT |
EX2 |
0 |
100.00 |
101 |
This section lists a prerequisite and discusses the archive process flow.
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.
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:
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).
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.
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.
Delete archived rows in the Project Transaction History table and in the Project Summary History table.
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.
Restore rows from the Project Transaction History table to the Project Transaction table as detailed rows.
Delete compressed rows in the Project Transaction table.
You must complete these steps to compress and archive data in PeopleSoft Project Costing:
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.
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.
(Optional) Roll back data from the archive tables to the history tables to recover deleted data as needed.
(Optional) Restore data from the transaction history table to the project transaction table to recover detailed data as needed.
(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.
(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.
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.
The Compress Project Data (PC_COMPRESS) process compresses project transactions based on parameters from the run control. This process:
Selects the transactions that are targeted for compression and saves them in history tables.
Creates summary transactions and inserts them into the project transactions table.
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.
Page Name |
Definition Name |
Navigation |
Usage |
PC_COMPRESS_TPL |
Setup Financials/Supply Chain, Product Related, Project Costing, General Options, Compression Template, Project Compression Template |
Set up the project compression template. |
|
PC_COMPRESS |
Project Costing, Utilities, Compress Project Data, Compress Project Data |
Summarize project data. |
|
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 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
Compression Template ID |
Enter a unique identifier for the compression template. This ID is used on the Compress Project Data page when creating the run control ID for the Compress Project Data (PC_COMPRESS) process. |
Project Business Unit Amount and Currency Code |
Displays as selected. The project business unit transaction amount is always summarized by the project business unit base currency code. Therefore, this field is always selected. |
Quantity and Unit of Measure |
Displays as selected. The quantity is always summarized by unit of measure. Therefore, this field is always selected. |
Source Amount and Foreign Currency |
Select to optionally select Foreign Currency. Optionally, the source amount (foreign currency) may be summarized by the foreign currency code. If Source Amount is selected to be summarized, then the Foreign Currency field is automatically selected as well. If Source Amount is not selected, then the source amount in the resulting summary rows is set to zero. |
Date Rollup Options
Date Rollup Level |
Select from these options: Monthly, Quarterly, or Yearly. This field determines the dates on the summary rows. |
Calendar ID |
Displays the corresponding rollup calendar that is used for the date rollup logic. This field is dependant on the option selected in the Date Rollup Level field and comes from the Project Costing Installation Options page. |
Rollup Date to Use |
Select: Start of Period or End of Period. This field determines whether the start of period date or the end of period date is used in the summary rows. |
Accounting Date, Transaction Date. Billing Date, Journal Date, Journal Line Date, PO Delivery Due Date, Budget Date, and Pay Period End Date |
Select the dates to roll up for transaction rows. The date rollup logic is applied only to the dates that are selected. Dates that are not selected are set to NULL on the summary rows. The Accounting Date and Transaction Date fields are always selected. |
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.
Access the Compress Project Data page (Project Costing, Utilities, Compress Project Data, Compress Project Data).
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
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.
This section discusses the steps to setting up and using Feeder and Pricing Summarization.
Page Name |
Definition Name |
Navigation |
Usage |
Project Costing Integration |
INSTALLATION_PCINT |
Set Up Financials/Supply Chain, Install, Installation Options. Click the Project Costing Integration link. |
Select the product and pricing options. Determine summarization options in the Summarization Options group box. For additional information on the Project Costing Integration page, See Defining Project Costing Integration Installation Options. |
Summarization Template |
PC_SUM_TEMPLATE |
Set Up Financials/Supply Chain, Product Related, Project Costing, General Options, Summarization Template |
Define summarization templates, which are rules for feeder integration applications and the Project Costing Pricing process. For additional information about summarization templates, |
Transaction List |
PROJECT_EXPRESS |
Project Costing, Transaction Definitions, Transaction List |
View transaction information and drill to the transaction detail. Ffor additional information about the Transaction List page, |
To set up and use feeder and pricing summarization:
Select the desired product and pricing options in the Summarization Options group box on the Project Costing Integration page.
Define summarization templates using the Summarization Template page.
Run the integration processes from feeder applications.
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.
Drill to the transaction detail from the Transaction List page.
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.
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.
Page Name |
Definition Name |
Navigation |
Usage |
PC_COMPRESS_LOG |
Project Costing, Utilities, Compress Project Data Click the Project Compression Tracking link. |
View 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.
Compress ID |
Displays the system-generated compression ID. |
Run Date |
Displays the date and time at which the compression run took place. |
Status |
Displays the current status of the data from a compression run or an action. Valid values are:
|
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.
View SQL |
Click this icon to access the Project Compression Tracking - Custom SQL (PC_CMPRESS_LOG_SEC) page. This page displays the customized where clause for the compression ID. Note. This icon is only available if a customized SQL was used on the Compress Project Data run control page. |
Project Compression Tracking: Actions tab
View History |
Click an active link to access the Transaction Detail History page for the compressed row. This link is active when the status is In History, and it opens a new window when activated. |
Action |
Select an action that can be performed on the compressed data. If the status is Purged or Restored, then no options are available. If the status is In History or Archived, then these options are available:
|
Process |
Click to initiate the selection in the Action field. |