Understanding the Data Lineage Dashboard

Tracking the flow of data from your source system to its various EPM targets is critical to maintaining the integrity of your data and managing it across EPM and reporting. PeopleSoft provides data lineage dashboards and reports that enable you to visualize the end-to-end flow of your data, from the source to target tables, including transformation logic, load strategy, and dependencies.

The data lineage dashboards and reports act like a reverse-engineering tool or family tree, enabling you to view the ancestry of source, target, lookup tables, ETL jobs, and dashboards. For example, the data lineage dashboards can help you answer:

What is the processing logic used to derive a specific metric column in a specific fact table and ETL job?
What jobs do I need to run (and in what order) to load a specific EPM fact table?
Which tables does a specific ETL job load?
What are the fields in an EPM table and how they are derived in an ETL job?
What are the source fields used to derive a field in a specific EPM table?
How is a specific surrogate key generated in a specific dimension?
Which dashboards and reports use a specific column?
Which dashboards and reports use a specific EPM record?
What are the presentation tables and column used in a specific OBIEE report?
Which EPM fact and dimension tables are used in a specific OBIEE dashboard and report?
What is the logical expression/derivation behind an OBIEE report column?

The new data lineage feature consists of the following dashboards and are discussed in more detail in this documentation:

ETL field lineage
ETL job utility
OBIEE lineage

Note: This feature is currently available only for the Campus Solutions, HCM, FMS, and SCM warehouses.

Note: This feature does not include audit jobs, language-related jobs, and MLOG related information. For MLOG related jobs please refer to the lineage information for the related staging table or staging ETL job in the base code line.

Data Lineage Architecture

The Data Lineage feature provides dashboards that report on lineage data taken from EPM tables, IBM WebSphere DataStage metadata, and OBIEE metadata. To capture this array of data lineage information that is in a raw format and transform it into reportable data, the Data Lineage architecture consists of two layers, the Data Lineage Staging Layer and the Data Lineage Reporting Layer.

Image: Data lineage architecture

Data lineage architecture showing the flow of data from the PeopleSoft Campus Solutions source system to the Data Lineage Reporting Layer. PeopleSoft Campus Solutions is used as one example, but the source system could also be PeopleSoft Asset Management, PeopleSoft Order Capture, and so forth.

Data Lineage Staging Layer

The Data Lineage Staging Layer contains all your DataStage metadata, including all ETL job metadata and design information. The DataStage metadata is stored in a set of lineage staging tables designed to store formatted metadata and grouped according to specific criteria, such as stage properties, stage level column derivation and stage design flow in ETL job. Data stored in the lineage staging tables is passed to the lineage reporting tables in the Data Lineage Reporting Layer.

The following table describes the lineage staging tables included in the Data Lineage Staging Layer.

Lineage Staging Layer Table	Description
PS_STG_PROP_TBL	Contains stage property information, such as table or file name, SQL, target action on file, or table, for the following passive stages: Hash File stages used as source, target, or reference. DRS stages used as source, target, or reference.
PS_ETL_DSGN_TBL	Contains job design information for all ETL jobs, such as stage names and related link names used in an ETL job. The order of the stages in the job design is used to get the data flow in the ETL job.
PS_STG_FLD_MAP	Contains transformation and derivation information for the following stages: Transformer DRS Transformer Reference Lookup derivation This table is used to trace lineage for MDW tables using the derivations used across the stages in the ETL job design.

Lineage Staging Layer Table

Description

PS_STG_PROP_TBL

Contains stage property information, such as table or file name, SQL, target action on file, or table, for the following passive stages:

Hash File stages used as source, target, or reference.
DRS stages used as source, target, or reference.

PS_ETL_DSGN_TBL

Contains job design information for all ETL jobs, such as stage names and related link names used in an ETL job.

The order of the stages in the job design is used to get the data flow in the ETL job.

PS_STG_FLD_MAP

Contains transformation and derivation information for the following stages:

Transformer
DRS
Transformer Reference Lookup derivation

This table is used to trace lineage for MDW tables using the derivations used across the stages in the ETL job design.

Data Lineage Reporting Layer

The Data Lineage Reporting Layer contains all of your DataStage metadata from the lineage staging tables, plus all of your OBIEE metadata, such as reports, dashboards, presentation layer, and so forth. The combined metadata is stored in a set of lineage reporting tables that are used directly by the OBIEE reporting interface.

The following table describes the lineage reporting tables included in the Data Lineage Reporting Layer.

Lineage Reporting Layer Table	Description
PS_JOB_CTRL_TBL	Contains information about each server job in the DataStage project.
PS_JOB_DEPN_TBL	Contains dependent jobs and the run order sequence for loading a fact or dimension table in the EPM layer.
PS_JOB_LKP_TBL	Contains the information related to each lookup table used in the ETL job.
PS_JOB_SRC_SQL	Contains the job name and source query used in each ETL job.
PS_FLD_LVL_LINEAGE	Contains a consolidated list of source tables and source fields used to derive a particular field in an EPM record. There is one row for each field in a record.
PS_FLD_SRC_MAP	Contains one row for each source/lookup field used to derive a particular field in an EPM record.
PS_FLD_LVL_FLOW	Contains the field lineage flow for each field in an EPM record, based on the ETL job.
PS_FIELD_LVL_LKP	Contains the lookup criteria used in the lookup derivation for an MDW table field.
PS_LINEAGE_RPD_HDR	Contains OBIEE repository presentation layer information.
PS_LINEAGE_RPD_DTL	Contains the entire OBIEE repository information from presentation layer through physical layer.
PS_LINEAGE_RPT_DTL	Contains information related to reports created in OBIEE and its corresponding presentation layer information.
PS_LINEAGE_DASHBRD	Contains information related to OBIEE dashboards and their corresponding report information.
PS_LINEAGE_DAS_RPT	Contains information related to OBIEE dashboards and their corresponding report information, plus the related OBIEE presentation table and column for the subject area.
PS_R_FLD_LVL_LKP	Relate table containing target field information and the corresponding lookup fields/lookup derived fields information.
PS_JOB_TGT_FLD	Contains the list of fields in an EPM Record, based on the ETL Job.