5 Researching Data Lineage

This chapter explains how to set up and use data lineage dashboards.

This chapter contains the following sections:

• Section 5.1, "Introduction"

• Section 5.2, "Setting Up Data Lineage"

• Section 5.3, "Tasks for Setting Up Data Lineage Dashboards"

• Section 5.4, "Tasks for Loading and Refreshing Data Lineage Dashboards"

• Section 5.5, "Performing Analysis with Data Lineage Dashboards"

5.1 Introduction

Data lineage dashboards provide reporting on out-of-the-box Business Intelligence Application module metrics and data, allowing data analysis from the transactional source application through the Business Intelligence repository and the underlying ETL mappings. Using data lineage dashboards, business analysts and those customizing ETL and repository objects can gain insight into where the data in their applications' reports and dashboards is being sourced from, how it is modeled in the Oracle BI Applications repository, and how it is loaded during ETL.

Data lineage dashboards are useful in performing business analysis and in understanding source-to-target ETL mappings and data transformation logic when planning or implementing Oracle Business Analytics Warehouse customizations.

5.2 Setting Up Data Lineage

Overview

When you set up data lineage, prebuilt data lineage warehouse tables in the OBAW are populated by an ETL package which loads lineage metadata from five sources:

• Oracle BI Applications Configuration Manager

• Oracle Data Integrator (ODI) Work Repository

• Oracle BI Presentation Catalog

• Oracle BI metadata repository file (RPD)

• Oracle Fusion OLTP tables

One-time preliminary setup steps include preparing metadata from and access to these sources for load of data lineage information into the prebuilt data lineage warehouse tables. Metadata and data refresh can then be performed on an ongoing basis.

List of Steps for Setting Up Data Lineage

To install and set up data lineage dashboards, you must complete the following tasks, in order. Each high-level task breaks down into a list of detailed steps.

1. Configure the environment to support data lineage, as described in Section 5.3.1, "Setup Step: Configure ODI Topology and Load Plan."

2. Configure extraction scripts to generate Presentation Catalog, dashboard, and RPD metadata files, as described in Section 5.3.2, "Setup Step: Configure RPD and Presentation Catalog Extraction Scripts."

3. Create data lineage warehouse tables, as described in Section 5.3.3, "Setup Step: Create the Data Lineage Warehouse Tables."

4. Extract metadata using scripts, as described in Section 5.4.1, "Extracting Oracle Business Intelligence Metadatata Using Scripts."

5. Execute the data lineage load plan in ODI to load the data lineage warehouse tables, as described in Section 5.4.2, "Executing and Monitoring the Data Lineage Load Plan." This step loads the data lineage warehouse tables.

5.3 Tasks for Setting Up Data Lineage Dashboards

This section provides detailed tasks for setting up data lineage dashboards. It includes the following sections:

• Section 5.3.1, "Setup Step: Configure ODI Topology and Load Plan."

• Section 5.3.2, "Setup Step: Configure RPD and Presentation Catalog Extraction Scripts."

• Section 5.3.3, "Setup Step: Create the Data Lineage Warehouse Tables."

Note: You must perform the tasks in this section in the sequence described in Section 5.2, "Setting Up Data Lineage".

5.3.1 Setup Step: Configure ODI Topology and Load Plan

The ODI Work Repository comes preconfigured with required topology and environment settings to support data lineage data extraction from the dashboards' five sources. During initial setup, you configure a prebuilt data lineage home directory variable which provides access to extracted metadata files, configure an adaptor list variable, then configure prebuilt ODI and Configuration Manager sources in the ODI topology so that ODI and Configuration Manager data can be sourced during data lineage ETL.

1. In ODI Studio, open the Data Lineage Exract and Load load plan.

2. Select Steps, then select the DATA_LINEAGE root step.

3. In the Property Inspector, set the DL_HOME variable to $ORACLE_BI_HOME/biapps/DataLineage.

4. Set the ADAPTOR_LIST variable by populating up to eight adaptors. If more than eight are required, you can use the ADAPTOR_LIST1 variable to add up to eight more.

   Based on your source system, one or more value can be selected from the following list: 'SDE_ORA11510_Adaptor','SDE_ORAR1212_Adaptor','SDE_PSFT_90_Adaptor','SDE_OP_V1_Adaptor','SDE_ORAR1211_Adaptor','SDE_PSFT_91_Adaptor','SDE_SBL_822_Adaptor','SDE_SBL_811_Adaptor','SDE_ORAR12_Adaptor','SDE_ORAR1213_Adaptor','SDE_JDEE1_91_Adaptor','SDE_JDEE1_90_Adaptor','SDE_Universal_Adaptor''SDE_FUSION_V1_Adaptor'.

5. In the Physical Architecture view of the Topology Navigator's tree view in ODI Studio, expand the technology that hosts your ODI work repository and locate the prebuilt BIAPPS_ODIREP Data Server.

6. Double-click the data server and, in the editor, verify or enter the correct connection details for the server, including the instance name and a correctly privileged ODI user and password.

7. In the JDBC tab of the editor, verify the JDBC Driver and verify or specify a valid JDBC URL for the connection.

8. Click Test Connection to verify that the connection details you have entered are correct.

9. Repeat the above steps to configure each of the following:

   • FILE_BIAPPS_DL_DEFAULT: Connects to the file location where input files are placed.

   • BIAPPS_DW: Connects to the data warehouse.

   • BIAPPS_BIACOMP: Connects to the Configuration Manager Schema.

10. Copy Fusion EAR files from $ORACLE_BI_HOME/biapps/DataLineage to $DL_HOME.

5.3.2 Setup Step: Configure RPD and Presentation Catalog Extraction Scripts

In this step, you set ODI memory heap size to accommodate extraction scripts, then customize extraction scripts which generate RPD and Presentation Catalog metadata files sourced during load of the data lineage warehouse tables.

1. Set the heap size appropriately. To do this in Windows:

   To do this in Windows, open the <DOMAIN_HOME>/bin/setDomainEnv.sh file for editing. Locate the if NOT "%USER_MEM_ARGS%"=="" ( line, and add the following code before it:

   if "%SERVER_NAME%"=="odi_server1" (
       set USER_MEM_ARGS=-Xms512m -Xmx3072m -XX:PermSize=256m
   -XX:MaxPermSize=512m
   )
   

   To do this in Linux, open the <DOMAIN_HOME>/bin/setDomainEnv.cmd file for editing. Locate the if [ "${USER_MEM_ARGS}" != "" ] ; then line, and add the following code before it:

   if [ "${SERVER_NAME}" = "odi_server1" ] ; then
         USER_MEM_ARGS="-Xms512m -Xmx3072m -XX:PermSize=256m -XX:MaxPermSize=512m"
         export USER_MEM_ARG
   fi
   

2. In $DL_HOME, open the webCatExtract script.

3. Set OBIEE_INSTANCE_HOME to your Oracle BI EE instance home location.

4. Set OBIEE_WEBCAT to the location of your Oracle BI Application Presentation Catalog location.

5. Set TARGET_DIR to the location where the extracted metadata files, webcat_text.txt and webcat_dashboard_test.txt, are to be created.

6. Save and close the file.

7. Open the webCatExtract_dashboard script and set the OBIEE_INSTANCE_HOME, OBIEE_WEBCAT, and TARGET_DIR variables as above.

8. Save and close the file.

9. Open the rpdExract script. and set OBIEE_ORACLE_HOME to the Oracle BI EE instance home location, and Presentation Catalog and repository locations.

10. Set OBIEE_ORACLE_HOME to the Oracle BI EE instance home location.

11. Set OBIEE_RPD to the Oracle BI EE repository name and location.

12. Set TARGET_DIR to the location where the extracted metadata file, rpd_text.txt, is to be created.

13. Save and close the file.

5.3.3 Setup Step: Create the Data Lineage Warehouse Tables

In this step, you use the Generate DataLineage DDL package to create the data lineage tables in the warehouse. These tables are created by default during data warehouse creation, but can be created at any time using this package.

1. In the ODI Studio Designer Navigator, expand BI Apps Project > Components > Generate DW DDL > Packages.

2. Execute the Generate DataLineage DDL package and monitor its execution using the Operator.

5.4 Tasks for Loading and Refreshing Data Lineage Dashboards

This section provides detailed tasks for initial and ongoing loading and refreshing of data lineage dashboards and their required metadata. It includes the following sections:

• Section 5.4.1, "Extracting Oracle Business Intelligence Metadatata Using Scripts."

• Section 5.4.2, "Executing and Monitoring the Data Lineage Load Plan."

Note: You must perform the tasks in this section in the sequence described in Section 5.2, "Setting Up Data Lineage".

5.4.1 Extracting Oracle Business Intelligence Metadatata Using Scripts

In this step, you copy and execute the metadata extraction scripts, then copy output files to the data lineage home directory. You can run the metadata extraction scripts to refresh your metadata.

1. Copy and execute the scripts on the Windows machine where Oracle Business Intelligence Enterprise Edition is installed.

2. Verify that the following files are generated in the location specified in the TARGET_DIR variable set in the scripts:

   • WebCat_text

   • Webcat_dashboard_text

   • rpd_text

   These files contain extracted Presentation Catalog and repository metadata, and are sourced during load of the data lineage warehouse tables.

3. Copy the output files from the script execution from the TARGET_DIR location to $DL_HOME.

4. Copy Fusion EAR files from $ORACLE_BI_HOME/biapps/DataLineage to $DL_HOME

5.4.2 Executing and Monitoring the Data Lineage Load Plan

In this step, you execute the Data Lineage Extract and Load load plan in ODI. This load plan uses the sources and extracted metadata files you have configured and generated as sources to load and refresh the prebuilt data lineage warehouse tables.

1. In the ODI Studio Designer Navigator, expand Load Plans and Scenarios > Predefined Load Plans > Data Lineage.

2. Execute the DataLineage Extract and Load load plan and monitor its execution using the Operator.

5.5 Performing Analysis with Data Lineage Dashboards

This section provides an overview of the BI Applications Data Lineage Dashboard and a case study in its use. in Oracle Business Intelligence Applications, and contains the following topics:

• Section 5.5.1, "Overview of the Oracle BI Applications Data Lineage Dashboard"

• Section 5.5.2, "Analyzing Data Lineage for Oracle Business Intelligence Applications"

5.5.1 Overview of the Oracle BI Applications Data Lineage Dashboard

Data lineage is analyzed across a variety of Oracle BI Applications entities, using dashboard prompts for the following in the DataLineage Summary page of the OBIA DataLineage dashboard:

• Dashboard Name

• Report Name

• Presentation Table Name

• Presentation Column Name

• Logical Table Name

• Logical Column Name

• Warehouse Datastore Resource Name

• Warehouse Column Name

• Source Application

• Source Table Name

• Source Column Name

You can select dashboard prompt values at any level, and subsequent selections are constrained by your other selections, so that, for example, if you have made a selection in the Report Name prompt, any selection from the Logical Table Name prompt will be limited to those included in the selected report.

The OBIA Data Lineage dashboard has five pages, including a summary whose tabular results can be drilled into for detailed lineage reports in detail pages. These pages include:

• DataLineage Summary: Provides an end-to-end data lineage summary report for physical and logical relationships. To navigate to detailed reports and dashboard pages for an entity in the Summary report, click its link.

• Dashboard Implementation: For a selected dashboard, provides reports detailing: dashboard pages, reports, and Presentation columns; Presentation catalog, tables, and columns; RPD implementation, detailing how a Presentation column is derived from a physical column in the OBAW.

• Report Implementation: For a selected report, provides details on the derivation on the Presentation column from its underlying physical column. Also includes reports describing the warehouse tables, and listing the ODI interfaces and adaptors that load both from the OLTP source into the staging tables and from the staging tables into the warehouse dimension and fact tables.

• Presentation Layer Implementation: For a selected Presentation table and column, provides details on the logical and physical column derivation in the RPD. Also includes reports describing the warehouse tables, and listing the ODI interfaces and adaptors that load both from the OLTP source into the staging tables and from the staging tables into the warehouse dimension and fact tables.

• Warehouse Implementation: For a selected warehouse table name and column name, provides a summary of the warehouse data store and its OLTP source tables and columns. Also includes reports listing the ODI interfaces and adaptors that load both from the OLTP source into the staging tables and from the staging tables into the warehouse dimension and fact tables.

5.5.2 Analyzing Data Lineage for Oracle Business Intelligence Applications

This section presents one usage scenario for the OBIA Data Lineage dashboard to illustrate its reports and usage.

To analyze data lineage for Oracle Business Applications:

1. Navigate to the OBIA Data Lineage dashboard.

2. In the Data Lineage Summary page, make a selection from one or more of the available prompts. You can make multiple selections in prompts, and available prompt selections are constrained by earlier selections in other prompts.

   
   

3. Click Apply to display the OBIA - LineageSummary report, which provides lineage details from the presentation dashboards and reports through BI Applications repository metadata, and finally to the warehouse table and column.

4. To drill to the detailed reports in the Dashboard Implementation page of the dashboard, click the Dashboard Name value in the summary report. This opens the Dashboard Implementation page with the Dashboard Name dashboard prompt pre-selected with the dashboard name. You could also click other entities in the report to navigate to other pages. For example, clicking a Warehouse Column Name and selecting Implementation would navigate to the Warehouse Implementation page, which focuses on ETL implementation.Examine the reports in the Dashboard Implementation page.

   Examine the reports in the Dashboard Implementation page:

   • The first report in the page, DashboardImplementation- OBIA -LineageSummary, provides a similar lineage to the default LineageSummary report, tracking lineage from the dashboard through the Presentation catalog to the warehouse table and column.

   • The second report, DashboardImplementation-DashboardDetails, focuses on the dashboard alone, detailing dashboard pages, reports, the Presentation Catalog, and the associated Presentation table and column names.

   • The third report, DashboardImplementation-OBIA-RPDImplementation, focuses on the lineage of the data in the BI repository metadata, covering all three layers of the repository, starting from Presentation Catalog names through business model and logical table and column names, and down to the physical catalog, table, and column names. The logical layer details include the expression, which often is just the logical expression of the table and column resources.

   • The fourth report, DashboardImplementation-ODIImplementation-SourceToStaging, focuses on the ETL interfaces used to load the warehouse staging tables, providing the warehouse table and associated adaptor and interface details.

   • The fifth report, DashboardImplementation-ODIImplementation-StagingToWarehouse, focuses on the ETL interfaces used to load the warehouse target fact and dimension tables, providing the warehouse table and associated adaptor and interface details.

5. Navigate back to the DataLineageSummary page, click a Report name value, and select Report Implementation to open that report as the dashboard prompt value in the Report Implementation page. Scroll through the reports on this page and notice that they closely mirror those provided for dashboards.

6. Navigate to the Presentation Layer Implementation page, which includes reports detailing the logical and physical column derivation in the RPD for Presentation columns. There are also reports describing the warehouse tables, and listing the ODI interfaces and adaptors that load both from the OLTP source into the staging tables and from the staging tables into the warehouse dimension and fact tables.

7. Navigate to the Warehouse Implementation page, in which the WarehouseImplementation-OBIA-LineageSummary report summarizes the relationship between warehouse tables and columns and associated models and source transactional tables and columns.