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.
|
Worksheet |
Description |
|---|---|
|
Template |
This worksheet contains overview information, a legend, and a definition of the columns used in the worksheets. |
|
OWS_FMS |
This worksheet contains the ETL lineage information for all of the staging jobs that are required for the Financials Warehouse. |
|
OWS_HCM |
This worksheet contains the ETL lineage information for all of the staging jobs that are required for the HCM Warehouse. |
|
OWS_SCM |
This worksheet contains the ETL lineage information for all of the staging jobs that are required for the SCM Warehouse. |
|
Setup_OWS_FMS |
This worksheet contains ETL lineage information for all of the setup jobs that are required for the Financials warehouse |
|
Setup_OWS_HCM |
This worksheet contains ETL lineage information for all of the setup jobs that are required for the HCM warehouse |
|
Setup_OWE |
This worksheet contains ETL lineage information for all of the setup jobs that are required for the OWE category. |
|
Setup_Dim_Mapper |
This worksheet contains ETL lineage information for the jobs that are required for setting up the dimension mapper. |
|
OWE_Global_D00 |
This worksheet contains ETL lineage information for all of the jobs that are required for the OWE global dimensions category. |
|
OWE_FMS |
This worksheet contains ETL lineage information for all of the jobs that are required for the OWE FMS category. |
|
OWE_HCM |
This worksheet contains ETL lineage information for all of the jobs that are required for the OWE HCM category. |
|
Dynamic_Lineage_Generator |
This worksheet provides a macro that enables you to enter the name of one or more ETL jobs and automatically generate a list of the complete lineage for those jobs. It also enables you to select one more budget types and automatically generate the list of the complete lineage for the selected budget types. |
Column Descriptions
|
Column |
Description |
|---|---|
|
SequencerJob |
The name of the job sequencer, which is responsible for invoking and running other ETL server jobs. |
|
ServerJob |
The name of the server job that is called by the job sequencer. |
|
ServerJobCategory |
The location of the server job in the IBM WebSphere DataStage project. |
|
TargetTable |
The name of the target table used in the server job. |
|
TargetUpdateAction |
The target load strategy for the server job. |
|
SourceTable |
The name of the source table used in the server job |
|
SourceExtractionType |
The type of extraction from the source table in the server job (for example, incremental date time or cyclical redundancy check). |
|
LookupTables |
The name of the lookup tables that are used in the server job. Lookups can be hashed files or direct DRS lookups. The lineage information captures the table names from which the hash files are populated and the table names for the direct DRS lookup. |
|
SetupJobs |
The name of the setup job that populates the source and/or the lookup table. |
|
SetupSequencer |
The name of the job sequencer that calls the setup server job. |
|
MDW |
The name of the MDW server job. This column has an entry if the source table or lookup table is populated from an MDW server job. |
|
MDWSequencer |
The name of the MDW sequence job |
|
OWS |
The name of the OWS server job. This column has an entry if the source table or lookup tables are populated from an OWS server job. |
|
OWSSequencer |
The name of the OWS sequence job |
|
OWE |
The name of the OWE server job. This column has an entry if the source table or lookup tables are populated from an OWE server job. |
|
OWESequencer |
The name of the OWE sequence job |
|
EPMFoundation |
The application or EPM foundation setup page that populates the source table or the lookup table, such as Global Consolidations, Dimension Mapper, or setup PIA pages. |
|
Category |
The categories in which the setup jobs, MDW jobs, OWS jobs or OWE jobs are placed. |
|
Comments |
Any additional comments, if applicable. |
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:
Access the worksheet in which the job is categorized.
Use Excel's Find feature to find the server job name in column B:
Type Ctrl-F to access the Find and Replace Dialog box.
Enter the name of the server job in the Find what edit box.
Click Find Next until the job name is found in the ServerJob column (column B).
Click Find Next until the job name is found in the ServerJob column (column B).
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.
Navigate to the OWE_FMS worksheet page.
Type Ctrl-F and type J_F00_PS_FC_TIME_DTL into the Find and Replace dialog box.
Image: Entering J_F00_PS_FC_TIME_DTL into the Find and Replace dialog box
This example illustrates the fields and controls on the Entering J_F00_PS_FC_TIME_DTL into the Find and Replace dialog box.
Click Find Next until you access the cell in the Server Job column that contains the J_F00_PS_FC_TIME_DTL.
Close the Find and Replace dialog box. You should see the following information:
Image: Results of Find Next
Scroll to the right to review the columns shown here:
Image: Scrolling to TargetTable, TargetUpdateAction, SourceTable columns
This example illustrates the fields and controls on the Scrolling to TargetTable, TargetUpdateAction, SourceTable columns.
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.
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.
Image: Viewing remaining columns
This example illustrates the fields and controls on the Viewing remaining columns.
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.
Image: OWS and OWSSequencer
This example illustrates the fields and controls on the OWS and OWSSequencer..
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.
Image: PS_Personal_D00 information
This example illustrates the fields and controls on the PS_Personal_D00 information.
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:
Access the Dynamic_Lineage_Generator worksheet.
Enter the job name in cell B1.
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.
Image: Dynamic Lineage Generator example
This example illustrates the fields and controls on the Dynamic Lineage Generator example.
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:
Clear any existing data in cell B1.
Select the Budget Type in cell B2.
Click the Get Job Lineage button.
This will generate the complete list of ETL jobs required to be run for the selected budgeting type.
Image: Lineage for a specific budgeting type
This example illustrates the fields and controls on the Lineage for a specific 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.