Extracting, Transforming, and Loading Source Data

This section provides an overview of source data extraction, transformation and loading.

Page Name

Definition Name

Usage

Run Data Transformations Page

EOEW_RUN_ETL

Define run control criteria for and run the data transformation process.

Run Data Transformations - Run Summary Page

EOEW_RUN_ETL_SUM

View information about only the jobs related to a particular data transformation process run control.

The data transformation process can be run to extract, transform and load source data by a single map or by map groups. When a map or group is executed, it is compiled at runtime. No SQL or code, only metadata, is stored. This reduces the risk of encountering problems late in a multi-map process, guarantees that each parallel process is executing the same version, and insulates the current running job from any changes to the actual map definition.

The data transformation process is run using the Data Transformation Application Engine process (EOEW_ETL_EXE).

Use the Run Data Transformations page (EOEW_RUN_ETL) to define run control criteria for and run the data transformation process.

Navigation:

Enterprise Components > Data Transformation > Run Data Transformations > Run Data Transformations

This example illustrates the fields and controls on the Run Data Transformations page. You can find definitions for the fields and controls later on this page.

 Run Data Transformations page

Note: The data transformation process uses the Data Transformation Application Engine process (EOEW_ETL_EXE).

Field or Control

Description

Data Transformer Object Type

Select a Data Transformer object type. You can run either a Map or Map Group.

Note: PeopleSoft Catalog Management uses the run control ID RUN_MAP to load partner source data. When using this run control ID, the Data Transformer object type must be Map.

Map Object

Select a map object. The prompt for available field values is based on the Data Transformer object type.

Target Load Option

Select a target load option.

Full Load. Extracts all data from the source as defined by the source data object and inserts into the target.

Incremental Update. Copies all rows from the source table that have been updated or modified since the last load, based on the date/time the row was updated or modified. The Map Options page must have a date/timestamp field defined in order to use incremental load.

Destructive Load

Select to delete all rows from the target table before the new rows are inserted.

Warning! Use this option with caution, as this will delete all rows in the target table.

Parallel Processing

Select to run the process in parallel mode, which runs the processes simultaneously. Clear to run it in serial mode, which runs each process in the chunk sequentially.

Note: This option is only available if the Object Type is Map.

Chunking Criteria

Click to access the Map Group Filter page to define chunking criteria for the associated map.

See Map Group Filter Page.

Note: This link is only available for Group Types of Map. If you want to chunk a group, you need to go to that group's definition to define the criteria.

Run

Click to run the data transformation process. A process request is submitted.

Click the Process Monitor link to monitor the status of the request.

Run Summary

Click to view information that is related to the status of the data transformation process.

For more information, refer the product documentation for PeopleTools: Process Scheduler.

Use the Run Data Transformations - Run Summary page (EOEW_RUN_ETL_SUM) to view information about only the jobs related to a particular data transformation process run control.

Navigation:

Click the Run Summary link on the Run Data Transformations page.

This example illustrates the fields and controls on the Run Data Transformations - Run Summary page. You can find definitions for the fields and controls later on this page.

 Run Data Transformations - Run Summary page

After running a data transformation process by clicking Run on the Run Data Transformations page, you can access the Run Summary page just as you would access Report Manager or Process Monitor.

Although the Process Monitor provides information regarding a process run, the Run Summary feature offers a more granular view of the individual subprocesses, such as chunks, that are not exposed in the Process Monitor. For example, a single map containing chunks or a group can spawn numerous jobs. If you use the Process Monitor to view these jobs, you find that the numerous jobs that are associated with a single map are mixed in with all of the other jobs that are currently running. Depending on the number of jobs that are running, this can make it difficult to view only those jobs that are associated with a particular Data Transformer process run control.

However, by using the Run Summary feature you can view all of the jobs that were spawned for the run control that is associated with a particular run on of the Data Transformer process. The Run Summary feature is especially useful when running parallel processes that are associated with multiple maps. By using the Run Summary feature, you can associate a process instance with each chunk as it runs.

The Run Summary feature enables you to see:

  • Which subprocesses are involved within a particular data transformation process run control.

  • When a particular subprocess (chunk or map) begins.

  • When a particular subprocess (chunk or map) completes.

  • Which subprocesses didn't complete successfully.

  • Which process instance is associated with a particular chunk or map.

Main Information

Select the Main Information tab.

Field or Control

Description

Process Instance

Displays the PeopleSoft Process Scheduler process instance that is assigned to the individual process. This value also appears on the Times and Chunking Criteria tabs for consistent identification.

Note: For parallel processes, you see different process instances; for serial processes, you see the same process instance.

Run Status

Reflects the same status that appears in the Process Monitor. If the run status displays an error, go to the Process Monitor to troubleshoot and restart the process.

Times

Select the Times tab.

Use these times to track the performance of the processes.

Chunking Criteria

Select the Chunking Criteria tab.

Field or Control

Description

Chunking Where clause

Displays information about the chunking criteria that is specified for a particular map, including:

  • Relational operators (=, <, >, and so on).

  • Boolean operators (AND and OR).

Note: This page also displays information that is relevant only to the internal aspects of the PeopleSoft mapping functionality.

The field names that are used for chunking are converted to an internal format; therefore, the format of the Chunking Where clause may not necessarily be a true reflection. That is, it may contain an extra “AND (“), for example. However, determining the chunking criteria that is used can be very useful when you are troubleshooting.

Also, values that are similar to EOEW_FP_CHAR30_0 are used internally by the PeopleSoft system to store data in a temporary table while the data is being transformed and loaded.