Understanding the EPM Lineage Spreadsheets
The EPM lineage spreadsheets provide information about the ETL jobs that are delivered with the EPM warehouses. The spreadsheets act like a reverse-engineering tool or family tree; they enable you to view the ancestry of source, target, and lookup tables and their relevant ETL jobs. Each spreadsheet provides lineage information for a single warehouse. The following table lists the lineage spreadsheets that are currently available:
Lineage Spreadsheet Filename |
Warehouse |
---|---|
ETL_CS_Lineage_Spreadsheet.xls |
Campus Solutions Warehouse |
ETL_CRM_Lineage_Spreadsheet.xls |
CRM Warehouse |
ETL_FMS_Lineage_Spreadsheet.xls |
FMS Warehouse |
ETL_HCM_Lineage_Spreadsheet.xls |
HCM Warehouse |
ETL_SCM_Lineage_Spreadsheet.xls |
SCM Warehouse |
By using the spreadsheets, you can:
View lineage information for staging, dimension, and fact ETL jobs, or source, target, and lookup tables.
Identify the sequence of jobs to run for a specific data mart.
Identify inter-mart and cross-warehouse dependencies.
Generate lineage information for a specific ETL job.
Spreadsheet Structure
Each EPM lineage spreadsheet includes several worksheets. The following table provides a description of each worksheet, by name, listed in the order in which it appears:
Worksheet |
Description |
---|---|
Template |
This worksheet contains overview information, a legend, and a definition of the columns used in the worksheets. |
Setup |
This worksheet contains ETL lineage information for all of the setup and staging jobs required for the warehouse. |
Com Dims |
This worksheet contains ETL lineage information for the common dimension jobs required for the warehouse. |
Utils |
This worksheet contains ETL lineage information for the currency conversion jobs required for the warehouse. |
Global Dims |
This worksheet contains ETL lineage information for the global dimension jobs required for the warehouse. |
Local Dims |
This worksheet contains ETL lineage information for the local dimension jobs required for the warehouse. |
<Data Mart> For example: GL & Profitablity, ESA, Campus Community, and so on. |
This worksheet contains ETL lineage information for the jobs required for a specific data mart. Note: Each spreadsheet includes several data mart worksheets. |
Dynamic_Lineage_Generator |
This worksheet provides a macro that enables you to enter the name of an ETL job and automatically generate a list of the complete lineage for that job. |
JobOrder |
This worksheet is an extension of the Dynamic_Lineage_Generator worksheet. It displays the order in which jobs need to be run. |
Column Descriptions
The following table provides descriptions of the columns in the worksheets.
Column |
Description |
---|---|
Sequencer Job |
The name of the job sequencer, which is responsible for invoking and running other ETL server jobs. |
Server Job |
The name of the server job that is called by the job sequencer. |
Server Job Category |
The location of the server job in the IBM WebSphere DataStage project. |
Target Table |
The name of the target table used in the server job. |
Target Update Action |
The target load strategy for the server job. |
Source Table |
The name of the source table used in the server job. |
Source Extraction Type |
The type of extraction from the source table in the server job (for example, incremental date time or cyclical redundancy check). |
Lookup Tables |
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. |
Setup Jobs |
The name of the setup job that populates the source and/or the lookup table. |
Setup Sequencer Job |
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. |
MDW Sequencer |
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. |
OWS Sequencer |
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. |
OWE Sequencer |
The name of the OWE sequence job. |
EPM Foundation |
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. |
Note: The spreadsheet does not contain lineage details for OWE jobs and Tree jobs, except for the GL&Profitability Mart of the FMS warehouse, which does include lineage information for OWE jobs.