Oracle Business Intelligence Metadata Lineage with Oracle Data Integrator

Purpose

This OBE tutorial provides you with a step-by-step method to implement the Oracle Business Intelligence Enterprise Edition (OBIEE) report-to-source data lineage for ETL processes that are developed with Oracle Data Integrator (ODI). This lineage adds existing OBIEE reports links that allow tracking the origin of the report’s information through the OBIEE RPD layers and through the ODI integration processes.

Time to Complete

Approximately 1 hour

Topics

This OBE tutorial covers the following topics:

Overview

Scenario
Examples
Verifying the Prerequisites
Example 1: Setting Up the ODI/OBIEE Lineage
Example 2: Refreshing the Lineage
Example 3: Using Lineage Reports into Dashboards
Summary

Viewing Screenshots

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: Because this action loads all screenshots simultaneously, the response time may be more depending on your Internet connection.)

Note: Alternatively, you can place the cursor over each individual icon in the following steps to load and view only the screenshot associated with that step.

The screenshots will not reflect the specific environment that you are using. They are provided to give you an idea of where to locate specific functionality in Oracle Data Integrator.

Overview

The following schema describes the lineage architecture:

In this architecture, three phases are needed for the lineage:

  1. OBIEE Metadata Export: From OBIEE, the RPD (Repository) and Web catalogs are exported in file format into a temporary directory.
  2. Metadata Consolidation: BIO-EE Export files are consolidated with the ODI metadata (using an ODI package) into lineage tables that are stored in the ODI repository schema. You will use the zip file obiee_odi_10g_lineage.zip that is provided for this purpose.
  3. Lineage Browsing: From OBIEE, a user can browse this consolidated metadata. A predefined RPD and a Web catalog are also provided for this purpose. The lineage report can be inserted in any existing report.

In this process, the ODI work repository:

This guide uses the following references:

  1. OBIEE_EXPORT_TEMP refers to the directory into which the OBIEE files are exported and from where the files are picked up by ODI.
  2. LINEAGE_TEMP refers to the temporary working directory. This directory is used at installation time.

 

Back to Topic List

Scenario

Linda works as a database administrator for Global Enterprise. In Global Enterprise, Linda is responsible for performing database management and integration tasks on various resources within the organization. In particular, Linda is responsible for data loading, transformation, validation, and creating reports using Oracle Business Intelligence Enterprise Edition (OBIEE). In one such OBIEE report, Linda needs to provide links that allow the tracking of the origin of the report’s information through the OBIEE RPD layers and through the ODI integration processes. For this purpose, Linda implements the OBIEE report-to-source data lineage for ETL processes that are developed with Oracle Data Integrator (ODI).

Examples

In this OBE, students learn the scenario through three examples.

Example 1: Setting Up the ODI/OBIEE Lineage
This example walks you through the steps needed to set the configuration of the topology for the ODI/OBIEE Lineage.

Example 2: Refreshing the Lineage
This example walks you through the steps needed to to refresh the Lineage.

Example 3: Using the Lineage Reports into Dashboards
This example walks you through the steps needed to use the Web catalog and RPD installed for the lineage with the most common scenarios.

Back to Topic List

Verifying the Prerequisites

Before you start the tasks, make sure that your system environment meets the following requirements:

Software Requirements

The system should include the following installed products:

The system should include the following configuration:

Back to Topic List

Example 1: Setting Up the ODI/OBIEE Lineage

This section takes you through the configuration of the topology. Two schemas are needed for the metadata consolidation phase:

Installing the OBIEE/ODI Lineage Files
Creating the ODI Topology
Customizing the Model Object
Importing the Metadata Consolidation Process
Initializing the Lineage Tables
Compiling the Load Lineage Package
Importing the Lineage RPD
Importing the Web Catalog Requests
Copying the Dashboard Images

Back to Topic List

 

Installing the OBIEE/ODI Lineage Files

To install the OBIEE/ODI Lineage files, perform the following steps:

1.

Download the OBIEE/ODI lineage zip file (obiee_odi_10g_lineage.zip) from here and extract it to the LINEAGE_TEMP working directory (for example: C:\lineage_temp).

 

Back to Topic List

Creating the ODI Topology

 

To create the ODI topology, perform the following steps:

1.

Start the Oracle Data Integrator Topology Manager: Click Start > All Programs > Oracle > Oracle Data Integrator > Topology Manager.

 

2.

The Oracle Data Integrator Login screen appears. From the Login Name drop-down list, select your Master repository—in this example: OracleDI Training Master Repository. The default User and Password is preset as SUPERVISOR and SUNOPSIS. Click OK to login.

 
3.

Click the Physical Architecture button . To create an Oracle data server, expand Technologies, right-click Oracle, and then select Insert Data Server. On this screen, enter values as shown in the following table, and then click the JDBC tab.

Parameter Value
Name ORACLE_XPONE
Instance / dblink (Data Server) Leave blank
User SYSTEM
Password SYSTEM

 

 
4.

Select Oracle JDBC Driver and click OK. In the JDBC URL field, enter jdbc:oracle:thin@xpone:1521:XE as shown below and click Test, and then click Test again.

Notes:

  • Don't copy and paste in the JDBC Url field! This may cause problems with entering a valid URL string.
  • You may need to enter the appropriate driver and URL for your RDBMS.

 
5.

Verify the successful connection, click OK, and then click OK again. The Oracle data server ORACLE_XPONE is now created.

 
6.

Now you have to create a Physical schema that points to your ODI work repository. Right-click the ORACLE_XPONE server and select Insert Physical Schema.

 
7.

You need to point this schema to your Work repository (WORKREP). In this example, in the Schema (Schema) and Schema (Work Schema) fields, you select REP_TRAININGW. Click the Context tab.

 

8.

Now you need to create an Oracle logical schema called ORA_ODI_REPO and map it to this physical schema. Click the button . Name the Logical schema ORA_ODI_REPO. Click OK.

 

9.

Similarly, create a File Physical schema that is based on the FILE_GENERIC data schema. This physical schema must point to the OBIEE_EXPORT_TEMP directory, the directory where the BI lineage text files will be dropped. Expand Technologies > File. Right-click FILE_GENERIC and select Insert Physical Schema. In the Directory (Schema) field, enter the path to your OBIEE_EXPORT_TEMP directory—in this example: C:\lineage_temp as shown below. Click the Context tab.

 

 
10.

Now, you need to create a File logical schema and map it to the physical schema. Click the Context button and enter the Logical schema name FILE_BIEE_EXPORT_FILES. Click OK.

 

Back to Topic List

 

Customizing the Model Object

To match the BI-EE models and the ODI models, you need three new flexfields (customized fields) in the ODI models. These flexfields will correspond to the Physical DB, Catalog, and Schema information stored in the OBIEE RPD files.

To customize the Model object, perform the following steps:

1.

Click the button to connect to ODI Security Manager. In the Objects tree view, double-click the Model object to edit it, and click the FlexFields tab.

 
2.

Click the Add FlexField button to add the three flexfields as shown below. Make sure to use the same Name and Code values for these flexfields. Click OK.

Name Code Technology Type
BI Physical DB BI_PHYSICAL_DB <All> String
BI Physical Catalog BI_PHYSICAL_CATALOG <All> String
BI Physical Schema BI_PHYSICAL_SCHEMA <All> String

 

 

Back to Topic List

 

Importing the Metadata Consolidation Process

The metadata consolidation process is provided in the form of an ODI project with its associated models. They must be imported into the existing ODI repository. To Import the consolidation process, perform the following steps:

1.

Click the Designer button to open ODI Designer. Log in to the OracleDI training Work repository (User: SUPERVISOR; Password: SUNOPSIS).

 

2.

Click the Models tab. In the Models tree view, click the Import Model button.

 

3.

In the File Import directory, navigate to the LINEAGE_TEMP directory and click OK. Select the BIEE Export Files and ODI Repository models. In the Import Type field, select the Synonym Mode INSERT _UPDATE mode. Click OK. Ignore the warning and click OK. The new models now appear in the Models tree view.

 
4.

Click the Projects tab. In the Projects tree view, click the Import button.

 
5.

In the Import window, click the button. Navigate to the LINEAGE_TEMP directory and click OK. Select the OBIEE Integration project and the Synonym Mode INSERT_UPDATE import mode. Click OK. Ignore the warning (Click OK). The project now appears in the Projects tree view.

Note: The objects imported in this phase come from a work repository with ID 101. Check that your own work repository ID is different than this value before performing the import task. If your repository ID is 101, you cannot use the lineage as is and need to import it in a different work repository.

 

 

Back to Topic List

 

Initializing the Lineage Tables

The lineage tables contain the consolidated ODI and OBIEE lineage metadata. They are created in the work repository using an ODI procedure. To initialize the lineage tables, perform the following steps:

1.

In the Projects tree view, expand the project: OBIEE Integration > Load ODI Lineage > Procedures. Select the Initialize Lineage Environment procedure.

 

2.

Right-click and select Execute. Select the appropriate context, and then click OK in the Execution dialog box. When the Information dialog box that says Session Started appears,  click OK.

 

 

 

Back to Topic List

 

Compiling the Load Lineage Package

This package consolidates the metadata from three sources: OBIEE Repository, OBIEE Web catalog, and ODI Metadata (models and data flows) into the lineage tables that will be used in the dashboards.

This package assumes that the Oracle technology is configured for supporting Ordered SQL Joins, which is not the default configuration in ODI. In order not to alter the technology when it is used by other ODI packages, it is recommended to change the Oracle technology to support Ordered Joins, generate a scenario for this package, and then restore the Oracle technology to its original state.

To compile the load lineage package, perform the following steps:

1.

Click the button to open Topology Manager. Navigate to Oracle technology. Double-click the Oracle technology node and perform the changes as shown in the following screenshots for the Definition tab and the SQL tab. Click Apply.
2.

Click the button to open ODI Designer. In the Projects view, select the OBIEE Integration > Load ODI Lineage > Packages > Load Lineage package. Right-click the Load Lineage package and select Generate Scenario. Enter the scenario name and version, and then click OK. A new scenario appears under the package.

 
3.

Open Topology Manager. In Topology Manager, you need to restore the Oracle technology to its initial state as shown below. Click the Definition tab and select Not Ordered in the Reference field. Click the SQL tab and verify that you have the settings as shown in the screenshot. Click Apply, and then click OK.

 

 

Back to Topic List

 

Importing the Lineage RPD

The OBIEE/ODI Lineage includes an RPD for accessing the ODI lineage tables as well as the ODI repository. This RPD needs to be imported into your existing OBIEE repository.

Note: The method described below can be used to import the Lineage RPD into the OBIEE instance. It is recommended that you contact your OBIEE administrator to import this RPD according to your practices and methods.

To import the Lineage RPD, perform the following steps:

1.

If started, stop the Oracle BI Server and Oracle BI Presentation Server services. Make a backup copy of the repository into which you want to merge the lineage RPD. This file is usually located in the <OBIEE_HOME>\server\repository directory. In this example, you create a copy of your repository RPD file samplesales.rpd, which is located in C:\OracleBI\server\repository and rename it to samplesales_old.rpd as shown in the following screenshots:

 
2.

Copy your repository RPD file (in this example, the samplesales.rpd file in the C:\OracleBI\server\repository directory) into the LINEAGE_TEMP directory (C:\lineage_temp).

 
3. Open a command window, change the directory to LINEAGE_TEMP (C:\lineage_temp) and run the following command to create a UDML file from the odi_repository_archive.rpd file. 
<OBIEE_HOME> \Server\Bin\nQUDMLGen 

-U <administrator name>

-P <administrator password> 

-R odi_repository_archive.rpd 

-O odi_repository_archive.udml 

-N –Q

This command generates a file called odi_repository_archive.udml containing the UDML definitions for the BI-EE Lineage. In this example, you run the command provided below. Refer to the screenshot. 

C:\OracleBI\server\Bin\nQUDMLGen.exe -U Administrator -P Administrator -R odi_repository_archive.rpd -O odi_repository_archive.udml -N –Q

 

 

4.

Run the following command from the same command window to merge this UDML file into your existing repository:



<OBIEE_HOME> \Server\Bin\nQUDMLExec 

-U <administrator name>

-P <administrator password> 

-I odi_repository_archive.udml 

-B <your repository RPD file>.rpd 

-O <your repository RPD file>_updated.rpd

In this example, you run the command provided below.

Note: For more help on the nQUDMLGen and nQUDMLExec commands, enter these commands with no parameters in the command line. After importing the RPD, you must configure it to point to the Oracle schema that contains the work repository tables.

C:\OracleBI\server\Bin\nQUDMLExec.exe -U Administrator -P Administrator -I odi_repository_archive.udml -B samplesales.rpd -O samplesales_updated.rpd

 

 
5.

Now you need to restore the repository file <your repository RPD file>_updated.rpd (in this example, samplesales_updated.rpd) in the BI-EE Instance. Copy the new samplesales_updated.rpd file from the C:\lineage_temp directory to the original directory C:\OracleBI\server\repository. Rename it to samplesales.rpd replacing the existing samplesales.rpd file.

Note: If the Oracle BI Server and Oracle BI Presentation Server services were started in your machine, they need to be stopped before this file is altered, because it will be locked by either Oracle BI Server or Oracle BI Presentation Server.

 
6.

Start the Oracle BI Administration tool: All Programs > Oracle Business Intelligence > Administration. Open the samplesales.rpd file in the Repository folder. Expand the ORACLE_ODI_REPOSITORY database in the OBIEE Physical Layer, double-click the Connection Pool node, and edit the Connection Pool to match your ODI work repository configuration. The following configuration is given for the database whose alias is XE. The schema containing the ODI work repository is REP_TRAININGW (password is REP_TRAININGW) as shown in the following screenshot. Click OK.

 
7.

Right-click the Physical schema and rename it to match the schema of the ODI Work Repository (REP_TRAININGW) as shown in the screenshot. Click OK to save your changes.

 
8.

Expand the REP_TRAININGW schema and test this updated connection by right-clicking one of the tables of this physical schema and refreshing the row count, as shown below.

Note: Make sure that in the connection pool, the password for user REP_TRAININGW is set to REP_TRAININGW.

 
9.

Right-click the same table again and select View data to view data with the updated row count.

 

 

Back to Topic List

Importing the Web Catalog Requests

The OBIEE/ODI Lineage comes with a Web catalog for building your reports on top of the lineage and ODI repository data.

To import the Web catalog requests, perform the following steps:

1.

Start Oracle BI Server and Oracle BI Presentation services in your machine. To connect to your Catalog, use the OBI Catalog Manager. Start the OBI Catalog Manager: Start > All Programs > Oracle Business Intelligence > Catalog Manager. Click File > Open Catalog. Enter the password for the user Administrator. In this example, the password is Administrator.

Note: Make sure that the Oracle BI Server and Oracle Presentation Server are up and running before opening the Catalog.

 
.2.

You need to make a backup copy of the catalog into which you want to add the Web catalog lineage. Select the catalog, select File > Archive. Name the archive file odi_archive.cat and provide the path to the LINEAGE_TEMP (C:\lineage_temp) directory as shown in the following screenshots. Click OK.

 

3.

Expand the catalog and select the folder into which you want to import the ODI catalog items (Shared folder). Select File > Unarchive. In the Unarchive catalog window, enter the Archive File Path, which is the path to the odi_catalog_archive.cat file from the LINEAGE_TEMP directory (C:\lineage_temp\odi_catalog_archive). Click OK. A new folder called ODI appears in the catalog folder.

 

 

Back to Topic List

Copying the Dashboard Images

The prepackaged requests use images that should be copied into the application server that hosts the analytic application.

To copy the dashboard images, perform the following steps:

1.

Copy the files called hie.gif and lin.gif from the LINEAGE_TEMP directory (C:\lineage_temp) into the /res subfolder of the analytic application deployment directory. In your example, the path to the analytic application deployment directory is C:\OracleBI\oc4j_bi\j2ee\home\applications\analytics\analytics.

 

Back to Topic List

 

Example 2: Refreshing the Lineage

This example walks you through the steps to refresh the Lineage.

Mapping the ODI Models to the OBIEE Physical Schemas
Exporting the OBIEE Repository Documentation to a Text File
Exporting the OBIEE Web Catalog Report to a Text File
Executing the ODI Load Lineage Scenario

 

Mapping the ODI Models to the OBIEE Physical Schemas


The lineage links the ODI models metadata and the OBIEE physical schema metadata using three flexfields: BI Physical Schema, BI Physical DB, and BI Physical Catalog (Microsoft SQL Server only).

To map the ODI models to the OBIEE physical schemas, perform the following steps:

1.

Open ODI Designer. In the Models view, for each model for which you want to build the lineage, update the flexfield values according to the BI-EE configuration. In this example, these values are set on the Oracle_Sales_Warehouse model, which uses the logical schema CUST_DW_DEV. Double-click the Oracle_Sales_Warehouse model, verify that the settings are the same as shown in the following screenshot, and then click the FlexFields tab.

 

2.

Set the value of the BI Physical DB flexfield to the name of the database that contains the physical schema in the BI-EE Physical layer. In this example, this value is XE. Set the value of the BI Physical Schema flexfield to the name of the physical schema that contains the model’s tables in the BI-EE Physical layer. In this example, this is CUST_DW_DEV. Refer to the following screenshot. Click OK to save the configuration.

Note: For Microsoft SQL Server databases, in addition, you need to set the value of the BI Physical Catalog flexfield to the name of the Catalog that contains the physical schema in the BI-EE Physical layer.

 

Back to Topic List

Exporting the OBIEE Repository Documentation to a Text File

To consolidate the OBIEE metadata in the lineage tables, you need to export it from the OBIEE repository into the OBIEE_EXPORT_TEMP folder.

To export the OBIEE repository documentation to a text file, perform the following steps:

1.

Open the Oracle BI Administration tool and, if necessary, connect to the repository that you want to include in the lineage. In this example, you should already be connected to your ORACLE_ODI_REPOSITORY.

 

2.

Select Utilities from Tools. Select the Repository Documentation utility, and then click the Execute button.

 
3.

Save the repository documentation as Repo_doc.txt (with this exact case) in the folder that you have chosen as the OBIEE_EXPORT_TEMP folder (c:\lineage_temp). Make sure to select Tab-separated values as the file type.

 

Back to Topic List

 

Exporting the OBIEE Web Catalog Report to a Text File

BI-EE metadata is not only contained in the repository, but also in the Web catalog. The following operation exports this metadata in a report. To export the OBIEE Web catalog report to a text file, perform the following steps:

1.

Open Oracle BI Catalog Manager and connect to the catalog that contains the reports that you want to include in the lineage. In this example, this catalog should already be connected. Select the catalog folder containing the reports that you want to include in the lineage (CUST_DW_DEV). Select Create Report from Tools.

 

2.

Select the columns that you need to include in the report. In this report, you include only the columns shown in the following screenshot. Save the report as Webcat_Doc.txt (name is case-sensitive) in the OBIEE_EXPORT_TEMP folder (C:\lineage_temp). Click OK. Preview the report and click OK.

Note: The order of columns included in the report should be exactly the same as shown in the screenshot.

Back to Topic List

 

Executing the ODI Load Lineage Scenario

This package consolidates the metadata from three sources: OBIEE Repository, OBIEE Web catalog, and ODI Metadata (models and data flows) into the lineage tables that will be used in the dashboards.

To execute the ODI load lineage scenario, perform the following steps:

1.

Open Oracle Data Integrator Designer. Click the Projects view tab.

 

 

2.

In the Projects view, run the scenario that you generated earlier. Expand: OBIEE Integration > Load ODI Lineage > Load Lineage > Scenarios. Right-click the LOAD_LINEAGE scenario and select Execute. Click OK. Click OK again.

 
3.

Click the ODI Operator button . Review the package execution in Operator and verify that the scenario executed successfully.

 

 

Back to Topic List

 

 

Example 3: Using Lineage Reports into Dashboards

The Web catalog and RPD installed for the lineage allow for many scenarios. The most common ones are listed below:

Viewing Execution Statistics
Viewing and Filtering Lineage Data

 

Viewing Execution Statistics

In this scenario, you display the execution statistics of ODI within an OBIEE dashboard. To view execution statistics, perform the following steps:

1.

Open the Oracle BI Welcome page: Start > All Programs > Oracle Business Intelligence > Welcome to Oracle BIEE. Select Oracle BI Interactive Dashboards. Log in using Administrator as User ID and Administrator as Password. The statistics appear as shown below:

 

 
2.

On the screen that follows, select click the "here" link to add content. In Saved Content, navigate to Shared Folders > ODI > RuntimeStats. Select RuntimeStats and drag it from the Saved Content section to the gray area under Section 1 as shown in the screenshot. Click Save.

 
3.

View the runtime statistics, and then click Return.

Back to Topic List

 

Viewing and Filtering Lineage Data

In this scenario, you want to view the lineage data and filter the results. To create such a dashboard, you need to add the Prompt Lineage dashboard prompt and the LineageRequestColumns request on a dashboard. Both these objects are in the Lineage Web catalog. To view and filter the lineage data, perform the following steps:

1.

Select Page options > Edit Dashboard. Click the Add Dashboard Page button . Name the new page Lineage and click OK.

 
2.

In Saved Content, navigate to Shared Folders > ODI > PromptLineage. Select PromptLineage and drag it from Saved Content into the gray area under Section 1 as shown in the following screenshots. Select LineageRequestColumns and drag it from Saved Content to the gray area under PromptLineage. Click Save.

3.

In this dashboard, you can filter using the following:

  • Origin of the column (ODI Column or OBIEE Logical, Physical, Presentation, or Request Column)
  • OBIEE Folder/Catalog or ODI Project containing the table and the column
  • Request or table containing the column
On the Origin menu, select BI Request Column and click the Go button to display the filtered list of columns.

 

 
4. From this request, you can display the Lineage and Hierarchy for each column. The Lineage icon allows you to drill down into a column lineage. The lineage drills down into the following:
  • OBIEE Presentation Columns used in a request’s column
  • OBIEE Logical Columns used in a Presentation Column
  • OBIEE Physical Columns used in a Presentation Column
  • The ODI Columns mapping the OBIEE Physical Columns. The ODI source columns are used to load a given ODI target column. This step can recurse if the source columns are targets for other ODI interfaces.

For each level of the lineage, the dashboard displays the following:

  • Type, Catalog, Table Name, and Column Name for the (target) column
  • Type, Catalog, Table Name, and Column Name for the (source) columns
  • Transformation Expression between the source columns and the target column

For the Amount column, Click the Hierarchy icon and view the Hierarchical Column Lineage. Click Return.

 

5.

You can continue drilling down into the lineage by clicking the Lineage icon in the view. For the same column (Amount), click the Lineage icon and view the information about columns and expressions used to populate a column. Repeat this step several times until you see the last screen shown below for the Server Sales catalog.

 
6.

If the expression is an ODI mapping, you can drill down the ODI run-time statistics for this transformation. From the current screen, click the Exec Stats icon and view the run-time statistics.

 

Back to Topic List

Summary

In this lesson, you learned how to:

Verify the prerequisites
Set up the ODI/OBIEE Lineage:
 
Install the OBIEE/ODI Lineage Files.
Create the ODI topology.
Customize the model object.
Import the metadata consolidation process.
Initialize the lineage tables.
Compile the load lineage package.
Import the lineage RPD.
Import the Web catalog requests.
Copy the dashboard Images.
Refresh the Lineage:
 
Map the ODI models to OBIEE physical schemas.
Export the OBIEE repository documentation to a text file.
Export the OBIEE Web Catalog report to a text file.
Execute the ODI load lineage scenario.
Use Lineage reports into dashboards:
 
View the execution statistics.
View and filter the lineage data.
Use the dashboard.

 

Place the cursor over this icon to hide all screenshots.