Viewing Lineage Information

This section discusses how to use the spreadsheet to:

Find lineage information for a server job.
Identify the list of Jobs to be run for a data mart.

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.
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 Server Job column (column B).
4. Close the Find dialog box.
Review the lineage information in the adjacent columns.

The Sequencer Job column (column A) lists the sequencer which calls this job. The Server Job Category column (column C) lists the category this job is associated with. The Target Table, Target Update Action, Source Table, and Source Extraction Type for this server job are listed in columns D, E, F, and G respectively. The Lookup Tables 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 data mart (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 level of the last staging job. To get the complete lineage for a fact or dimension 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 fact or dimension.

Example

This example, from the ETL FMS Lineage spreadsheet, takes you through the tasks you would complete to review the information for the fact job J_Fact_PS_F_APAR_NETTING_E, which is used for the AR Data Mart.

Navigate to the AR worksheet page.
Type Ctrl-F and type J_Fact_PS_F_APAR_NETTING_E into the Find and Replace dialog box.

Image: Find and Replace Dialog Box

This example illustrates the fields and controls on the Find and Replace Dialog Box. You can find definitions for the fields and controls later on this page.
Type Ctrl-F and type J_Fact_PS_F_APAR_NETTING_E into the Find and Replace dialog box.
Click Find Next until you access the cell in the Server Job column that contains the J_Fact_PS_F_APAR_NETTING_E job.
Close the Find and Replace dialog box. You should see the following information:

Image: J_Fact_PS_F_APAR_NETTING_E job displayed in spreadsheet

This example illustrates the fields and controls on the J_Fact_PS_F_APAR_NETTING_E job displayed in spreadsheet. You can find definitions for the fields and controls later on this page.
Scroll to the right to review the columns shown here:

Image: Reviewing data associated with the J_Fact_PS_F_APAR_NETTING_E job

This example illustrates the fields and controls on the Reviewing data associated with the J_Fact_PS_F_APAR_NETTING_E job. You can find definitions for the fields and controls later on this page.

The Target Table, Target Update Action, Source Table, and Source Extraction Type for the J_Fact_PS_F_APAR_NETTING_E 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_Fact_PS_F_APAR_NETTING_E.

Image: Lookup Tables Column

This example illustrates the fields and controls on the Lookup Tables Column. You can find definitions for the fields and controls later on this page.

In this example there are three source tables: PS_D_CUST_ORG, PS_D_SUPPLIER, PS_F_AR_AGING. The lookup table is PS_F_AP_AGING. 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 out the dependent jobs required to populate these source and lookup tables. In this example, the source table PS_D_CUST_ORG has an entry in the MDW column, which means that is is populated from the MDW dimension J_Dim_PS_D_CUST_ORG_SCM, which is placed in the category Global_Dimensions_E\OWS_To_MDW\Base\Load_Tables\Server.

As shown in the following screenshot, the source table PS_D_SUPPLIER is an SCM warehouse dimension. The cross-warehouse dependency is identified by the different color (the color legend is located on the first worksheet page).

Image: Cross-warehouse dependencies for PS_D_SUPPLIER

This example illustrates the fields and controls on the Cross-warehouse dependencies for PS_D_SUPPLIER. You can find definitions for the fields and controls later on this page.

Similarly, the lookup table PS_F_AP_AGING is populated from the fact job J_Fact_PS_F_AP_AGING placed in the category FMS_E\Payables_Mart\AP\OWS_To_MDW\Facts\Base\Load_Tables\Server. This fact job belongs to a different mart as indicated by the different color.

Image: Cross-warehouse dependencies for PS_F_AP_AGING

This example illustrates the fields and controls on the Cross-warehouse dependencies for PS_F_AP_AGING. You can find definitions for the fields and controls later on this page.

Identifying the List of Jobs to be Run for a Data Mart

You can use the information in the spreadsheet to identify the list of jobs that need to be run for a specific data mart. These include common jobs that are required for every data mart, which we refer to as prerequisite jobs, as well as jobs specific to the particular data mart.

If you prefer, you can create your own master sequencers based on the information provided in this section.

Alternatively, you can generate the list of jobs by using the Dynamic Lineage Generator tool. For more information, see "Generating Lineage Information for a Job".

Note: All the server jobs relating to Hash files that are present within the Load_Hash_Files category need to be run first before running other Sequence jobs within the Load_Tables category since these hash files are being used in other server jobs.

Prerequisite Jobs

The prerequisite jobs include setup jobs, staging jobs, and dimension jobs.

The following sets of jobs need to be run for every mart, in the order that they are listed in the worksheets:

Run these setup jobs in the Setup worksheet:
1. All jobs within the Setup_E\OWS\<Warehouse> category.
  
  (For example all jobs within the Setup_E\OWS\FSCM category for the FMS warehouse and all jobs within the Setup_E\OWS\CS category for the CS warehouse).
2. All jobs within the Setup_E\Dimension mapper category.
  
  Note: Please ensure that you run the Business Unit Wizard before proceeding with the following steps.
  
  See Understanding Warehouse Business Units, TableSet Sharing, and SetID Mapping
3. All jobs within the Shared_Lookups\DimensionMapper_Lookups category.
4. All jobs within the Shared_Lookups\Control_Tables category.
5. All jobs within the Shared_Lookups\System_Lookups category.
6. All jobs within the Shared_Lookups\Language_Lookups category.
7. All jobs within the Setup_E\OWE category (this step does not apply to the Campus Solutions warehouse).
8. If you are implementing currency conversion, then run the jobs listed in the Utils worksheet.
Run the staging jobs listed in the OWS Sequencer column (column N) in the following worksheets:
1. Com Dims.
2. Global Dims.
3. Local Dims.
4. <Data Mart>, where <Data Mart> is the name of the data mart, for example AP, AR, Campus Community, Student Financials .
Run the Common Dimension Jobs listed in the Com Dims worksheet.
Run the Global Dimensions jobs listed in the Global Dims worksheet. (These jobs are required for running the FMS warehouse jobs.)
Run the Local Dimension Jobs placed in the Local Dims worksheet.

Data Mart Specific Jobs

Run all the Server jobs listed in column B of the worksheet for the specific data mart, to populate the corresponding Dimension and Fact tables for that mart.

Note: Do not run the jobs that are listed within the Reusable Jobs category. These jobs are not used to load target tables. They are automatically triggered by various Sequence jobs.