Appendix: Source, Staging, and Target Tables for Planning and Budgeting

Planning and Budgeting is delivered with the Extract, Transform, and Load (ETL) jobs and maps used to import data into the PeopleSoft EPM Warehouse. Additionally, we provide a spreadsheet-based utility that provides information about the ETL jobs that are required for PeopleSoft Planning and Budgeting . This appendix discusses:

• Source, staging, and target tables for PeopleSoft Financial Management data.

• Source, staging, and target tables for PeopleSoft Human Resource Management data.

• Tables used to move data from Planning and Budgeting to source systems.

• The EPM Planning and Budgeting ETL lineage spreadsheet.

Note. The PeopleSoft system delivers close to 6,000 jobs and maps that are used to load data into the PeopleSoft EPM Warehouse.

See Also

Understanding Planning and Budgeting Integrations

Bringing Source Data Into EPM Using Extract, Transform, and Load (ETL)

Source, Staging and Target Tables for PeopleSoft Financial Management Data

This section lists the source PeopleSoft Financial Management Solutions (FMS) tables, and PeopleSoft EPM Warehouse Staging (OWS) and target Operational Warehouse Enriched (OWE) tables for financial management-related data. The use of target tables here refers to the records and tables that are used by the Planning and Budgeting application. In some cases, you might use online maintenance pages to access and update the data. The list is organized by:

• General ledger records.

• Dimension (ChartField) records.

• Multiple currency records.

• Asset budgeting records.

• Commitment Control records.

• Combination edit records.

General Ledger Records

Note. There are no ETL jobs or maps for the Ledger Template records (PS_PF_TMPLT_TBL, PS_BP_TMPLT_TBL, and PS_GC_TMPLT_TBL) which are delivered as system data. If your organization uses different names, you can create new ledger template definitions in the EPM database.

Note. PeopleSoft Planning and Budgeting provides portal navigation that takes you directly to the Ledger For A Unit page within PeopleSoft Financial Management Solutions (FMS) where you can view setup information. Edit access in FMS is only allowed if your security permits it, but any changes would require you to rerun the map for the PS_BU_LED_GRP_TBL record.

This page does not exist in the EPM database, but the data is used in PeopleSoft Planning and Budgeting and is brought over during the ETL process. Use the Ledger For A Unit (FMS) link to view and understand data relationships; for example, if you are using combination editing and need to understand valid combination edit groups, scroll down to the budgeting ledger for your business unit, and then click Journal Edit Options. You can then determine the Combination Edit Process Groups that will be validated for that ledger. To access the Ledger For A Unit (FMS) link, navigate from EPM Foundation, EPM Setup, Ledger Setup, Ledgers menu. You must have single sign on set up to use this link.

Dimension (ChartField) Records

Note. You must ensure that the department data you load into the OWE target table (PS_DEPARTMENT_TBL) in the Enterprise Performance Management (EPM) database is the data that you want to use for planning and budgeting purposes. The ETL map you use to populate the PS_DEPARTMENT_TBL table with PeopleSoft HRMS department data is the same one used to populate the table with PeopleSoft Financial Management department data. This is because the respective ETL jobs for source HRMS and Financial Management department data store data in the same OWS table.

When the departments are the same in your HRMS and Financial Management source, choose one source to run your ETL job.

When the departments are unique for your HRMS and Financial Management source, run the ETL jobs from each source. Since the departments are unique, you will not be required to use Dimension Mapper.

Note. Planning and Budgeting does not use the PF_SCENARIO_DFN page or ETL maps; instead it supports scenarios from the PS_BD_SCENARIO_TBL source and target table, and any scenarios defined within the EPM database on the Scenario-Budget maintenance page.

Note. The budget ledgers used by Planning and Budgeting include three additional dimensions called Dimension 1, Dimension 2, and Dimension 3. These fields in the budget ledgers have no source record, are not stored in the source budget ledgers, and don't have any maintenance page; therefore, no ETL jobs are delivered for these dimensions. These fields (DIMENSION1, DIMENSION2, and DIMENSION3) can only be found in the following budget ledger records: PS_BP_LED_BUDG_F00, PS_BP_LED_PROJ_F00, and PS_BP_LED_KK_F00.

See Configuring Dimensions for Planning and Budgeting.

See Setting Up Single Sign On.

Note. PeopleSoft Planning and Budgeting uses the PS_PRODUCT_TBL table within the application, which is referred to as the Product-General Ledger menu item for maintenance. Planning and Budgeting does not use the PRODUCT_ID field in the PS_PRODUCT_D00 table in the EPM database.

Multiple Currency Records

Asset Budgeting Records

Commitment Control Records

Combination Edit Records

Source, Staging, and Target Tables for Human Resource Data

This section lists the source PeopleSoft Human Resource Management System (HRMS) tables and Enterprise Performance Management staging (OWS) and target (OWE) tables for human resource-related data. The use of target tables here refers to the records and tables that are used by the Planning and Budgeting application. In some cases, you might use online maintenance pages to access and update the data. The list is organized by:

• Job and Compensation.

• Jobcodes and Position.

• Salary Plan.

• Other Human Resource.

Job and Compensation Records

Jobcodes and Position Records

Salary Plan Records

Other Human Resource Records

Note. You must ensure that the department data you load into the OWE target table (PS_DEPARTMENT_TBL) in the EPM database is the data that you want to use for planning and budgeting purposes. The ETL map you use to populate the PS_DEPARTMENT_TBL table with HRMS department data is the same one used to populate the table with FMS department data. This is because the respective ETL jobs for source HRMS and FMS department data store data in the same OWS staging table.

When the departments are the same in your HRMS and FMS source, choose one source to run your ETL job.

When the departments are unique for your HRMS and FMS source, run the ETL jobs from each source. Since the departments are unique, you will not be required to use Dimension Mapper.

Tables Used to Move Data From Planning and Budgeting Back to Source Systems

After you have exported the data out of the planning model, you can use the ETL tool to move the data from Planning and Budgeting back to the source systems.

See Understanding Exporting General Ledger Budget Data from the Planning Model.

See Exporting Position Data from the Planning Model.

Records Used To Send Data Back to Financial Management System

Records Used to Send Data Back to Human Resources Management System

Note. For the PS_BP_COMP_F00 source record in the OWE for Planning and Budgeting, the ETL jobs to export data to HRMS use views to extract compensation distributions by category, they include: PS_BP_SAL_DIS_VW, PS_BP_BNFT_DIS_VW, PS_BP_EARN_DIS_VW, and PS_BP_TAX_DIS_VW.

The EPM Planning and Budgeting ETL Lineage Spreadsheet

This section provides an overview of the PeopleSoft EPM Planning and Budgeting ETL Lineage spreadsheet and discusses how to use the spreadsheet to view and generate lineage information:

Understanding the EPM Planning and Budgeting ETL Lineage Spreadsheet

The EPM Planning and Budgeting ETL lineage spreadsheet provides information about the ETL jobs that are required for PeopleSoft Planning and Budgeting. This spreadsheet acts like a reverse-engineering tool or family tree; it enables you to view the ancestry of source, target, and lookup tables and their relevant ETL jobs. The filename of the Budgeting ETL Lineage spreadsheet is ETL_P&B_Lineage_Spreadsheet.xls.

By using this spreadsheet, you can:

• View lineage information for staging and OWE ETL jobs (namely D00 jobs and F00 jobs).

• Generate lineage information for one or more ETL jobs, or for a specific budget type.

Spreadsheet Structure

The ETL_P&B_Lineage_Spreadsheet.xls spreadsheet includes several worksheets. The following table provides a description of each worksheet, by name, listed in the order in which it appears:

Column Descriptions

The following table provides descriptions of the columns in the worksheets.

Generating Lineage Information

This section discusses how to use the spreadsheet to:

• Find lineage information for a server job.

• Generate lineage information for one or more jobs.

• Generate lineage information for one or more budget types.

Finding Lineage Information for a Server Job

To find lineage information for a server job:

1. Access the worksheet in which the job is categorized.

2. Use Excel's Find feature to find the server job name in column B:

   1. Type Ctrl-F to access the Find and Replace Dialog box.

   2. Enter the name of the server job in the Find what edit box.

   3. Click Find Next until the job name is found in the ServerJob column (column B).

   4. Click Find Next until the job name is found in the ServerJob column (column B).

3. Review the lineage information in the adjacent columns.

   The SequencerJob column (column A) lists the sequencer which calls this job. The ServerJobCategory column (column C) lists the category this job is associated with. The TargetTable, TargetUpdateAction, SourceTable, and SourceExtractionType for this server job are listed in columns D, E, F, and G respectively. The LookupTables column (Column H) lists all the lookups used by this job.

   The source tables and the lookup tables are placed in separate rows. This enables you to find the lineage information for each of these tables by navigating through the other subsequent columns in the same row. Columns I through R list the dependent jobs that are required to populate the source and lookup tables, and entries in these columns indicate whether the table is populated by Setup jobs, (column I), MDW jobs (column K), OWS jobs (column M), OWE jobs (column O), or Foundation setup / Apps (column Q). The Category column (column R) lists the category that the dependent job is associated with.

   Source tables that are from a different datamart (inter-mart) or different warehouse (cross-warehouse) are indicated by the colors specified in the legend on the Template worksheet page.

The spreadsheet lists the lineage of a source or lookup table to the level of the job that directly populates it. The lineage information does not extend to the level of the last staging job. To get the complete lineage for a fact (F00) or dimension (D00) job fully extended through the lowest staging level, you can use the dynamic lineage generator tool, which generates a list of all the required dependent jobs that need to be run in order to load a particular F00 or D00 table.

Example

This example, takes you through the tasks you would complete to review the information for the F00 job J_F00_PS_FC_TIME_DTL.

1. Navigate to the OWE_FMS worksheet page.

2. Type Ctrl-F and type J_F00_PS_FC_TIME_DTL into the Find and Replace dialog box.

3. Click Find Next until you access the cell in the Server Job column that contains the J_F00_PS_FC_TIME_DTL.

4. Close the Find and Replace dialog box. You should see the following information:

5. Scroll to the right to review the columns shown here:

   The Target Table, Target Update Action, Source Table, and Source Extraction Type for the J_F00_PS_FC_TIME_DTL server job are listed in columns D, E, F, and G, respectively.

6. Continue to scroll to the right to view the remaining columns. The Lookup Tables column (Column H) lists all the lookups used in J_F00_PS_FC_TIME_DTL.

   In this example there is one source table: PS_FC_TIME_DTL. There are multiple lookup tables: PS_PERSONAL_D00, PS_PF_BUS_UNIT_MAP, PS_PROJ_ACTIVITY and PS_PROJECT_D00. The source tables and the lookup tables are each placed in a unique row one after the other. This enables you to view the lineage information for each of these tables by navigating through the succeeding columns within the same row.

   Columns I through R list the dependent jobs that are required to populate these source and lookup tables. In this example, the source table PS_FC_TIME_DTL has an entry in the OWS column, which means that it is populated from the OWS Job J_Stage_PS_FC_TIME_DTL, which is placed in the category FMS_E\OWS\Base\Load_Tables\Server.

   Similarly, the lookup table PS_PERSONAL_D00 is populated from the D00 job J_D00_PS_PERSONAL placed in the category OWE_E\Global_D00\Base\Load_Tables\Server.

Generating Lineage Information for One or More Jobs

The Dynamic_Lineage_Generator worksheet contains a macro that generates a list of all the dependent jobs that are required for any ETL job. This will easily help you identify all of the required jobs that must be run for a specific F00 or D00 job.

To use the Dynamic Lineage Generator:

1. Access the Dynamic_Lineage_Generator worksheet.

2. Enter the job name in cell B1.

3. Click the Get Job Lineage button.

   The macro retrieves the lineage required for running this ETL job from the setup, staging, and other categories and displays it in the cells below. The macro also copies the entire list of dependent jobs to the JobOrder worksheet, so you can identify the complete list to be run in sequence.

   Lineage for multiple jobs can be retrieved by listing multiple job names in column B1, separated by a comma sign and then clicking the Get Job Lineage button.

Generating Lineage Information for One or More Budget Types

Lineage for a specific budgeting type can be retrieved by selecting the budgeting type from the list box (for example Asset Budgeting, Line Item Budgeting, Position Budgeting).

To generate lineage information for one or more budget types:

1. Clear any existing data in cell B1.

2. Select the Budget Type in cell B2.

3. Click the Get Job Lineage button.

   This will generate the complete list of ETL jobs required to be run for the selected budgeting type.

   Lineage for multiple Budgeting types can be generated by selecting multiple budgeting types from the list box and then clicking the Get Job Lineage button. The entire list of dependent jobs to be run in sequence is copied to the JobOrder worksheet.