19 Oracle Business Intelligence Enterprise Edition Data Lineage

19.1.1 Components

The OBIEE Lineage is made up of the following components:

• Lineage Tables: These tables consolidate both the OBIEE and ODI metadata. They are stored in the ODI Work Repository.

• Lineage Artifacts for OBIEE: This pre-packaged OBIEE artifacts are deployed in OBIEE to access the lineage information. These include:

  • Lineage RPD containing the Physical, Logical and Presentation layers to access the Lineage Tables,

  • Lineage Web Catalog Requests to be used in existing dashboard to create report -to-source dashboards,

  • Images used in these dashboards.

• Command Line Tools and a Wizard to automate the lineage tasks:

  • Deployment of the Lineage Artifacts for OBIEE in an OBIEE instance,

  • Extraction of the OBIEE Metadata from a OBIEE Instance,

  • Consolidation of the OBIEE and ODI Metadata in the ODI repository.

19.1.2 Lineage Lifecycle

This section describes the different phases of using OBIEE Lineage and the persons involved in these phases.

19.1.2.3 Using the Lineage

The lineage is used to extend existing dashboards. You can create specific links in these dashboards to browse the data lineage and view the execution statistics of the ODI sessions.

You can also customize your own dashboards using the pre-packaged Lineage Artifacts for OBIEE.

Figure 19-1 describes the Lineage lifecycle after the initial setup.

Figure 19-1 Lineage Lifecycle

Description of "Figure 19-1 Lineage Lifecycle"

The BIEE metadata is extracted (1) and consolidated with the ODI Metadata in the lineage tables (2). The lineage tables are accessed from the end-user's dashboard (3) through the Lineage Artifacts deployed in the BIEE Server.

19.2.1 Installation Overview

Installing Lineage in an OBIEE Server deploys the required OBIEE artifacts in the OBIEE Repository and Web Catalog. The OBIEE Lineage artifacts are the Lineage RPD, the Lineage Web Catalog Requests, and the dashboard images. These artifacts are used to access the lineage content from your reports and dashboards.

The installation is performed using the OBIEE Lineage Wizard. This wizard guides you through the installation, and also through the configuration and refresh of the Oracle Data Integrator (ODI) Lineage for Oracle Business Intelligence Enterprise edition (OBIEE).

After installation and configuration are complete, there are some post-installation tasks you need to perform, depending on your OBIEE version.

The complete installation flow is as follows:

Installation Flow when Using OBIEE 10g

When using OBIEE 10g, the OBIEE Lineage wizard installs only the Lineage RPD. To install the Lineage Web Catalog Requests and the dashboard images, you have to perform some additional tasks. The following installation flow describes the complete installation instructions, including the deployment of the Web Catalog Requests and Images:

1. Review the Requirements.

2. Installing and Starting the OBIEE Lineage Wizard.

   Note that you can also use the install lineage script instead of the OBIEE Lineage wizard. See Section 19.5.2, "Automating Lineage Deployment" for more information.

3. Use the OBIEE Lineage wizard to install Lineage in OBIEE Server and deploy the OBIEE Lineage artifacts. See Section 19.2.3.2, "Deploying the OBIEE Lineage Artifacts using the Wizard".

4. Deploy the Web Catalog requests in the OBIEE 10g Web Catalog. See Section 19.2.4, "Post-Installation Tasks".

5. Deploy the images. See Section 19.2.4, "Post-Installation Tasks".

6. Update the BI Physical Layer Connection to ODI Work Repository. See Section 19.2.4, "Post-Installation Tasks".

Installation Flow when Using OBIEE 11g

When using OBIEE 11g, the OBIEE Lineage wizard installs only the Lineage RPD and the Web catalog Requests. To install the dashboard images, you have to perform some additional tasks. The following installation flow describes the complete installation instructions, including the deployment Images:

1. Review the Requirements.

2. Installing and Starting the OBIEE Lineage Wizard.

   Note that you can also use the install lineage script instead of the OBIEE Lineage wizard. See Section 19.5.2, "Automating Lineage Deployment" for more information.

3. Use the OBIEE Lineage wizard to install Lineage in OBIEE Server and deploy the OBIEE Lineage artifacts. See Section 19.2.3.2, "Deploying the OBIEE Lineage Artifacts using the Wizard".

4. Deploy the images. See Section 19.2.4, "Post-Installation Tasks".

5. Update the BI Physical Layer Connection to ODI Work Repository. See Section 19.2.4, "Post-Installation Tasks".

19.2.2 Requirements

Before installing OBIEE Lineage, you should review the following requirements:

• The OBIEE Lineage Wizard requires a Java Runtime Environment 1.6 (JRE). Before starting the wizard, make sure that your JAVA_HOME is pointing to a valid JRE.

• The work repository has to bes stored in an Oracle database.

• Before installing the artifacts, stop the BI Server and BI Presentation services component.

• Make a backup copy of the OBIEE RPD and Webcat.

• Make sure the RPD file used by the server is NOT open in the BI Admin tool.

• Install and Execute OBIEE Lineage Wizard or Command Line tools on the machine where the BI Admin tool is installed.

• The database user used to connect the Work Repository schema must have sufficient privileges to create views in the schema hosting the Work Repository.

19.2.3 Installation Instructions

This section provides the installation instructions and contains the following topics:

• Installing and Starting the OBIEE Lineage Wizard

• Deploying the OBIEE Lineage Artifacts using the Wizard

19.2.4 Post-Installation Tasks

This section describes the post-installation tasks. Theses tasks depend on your OBIEE Server version.

For OBIEE 10g, you need to perform the following post-installation tasks:

• Deploy the Web Catalog Requests in the OBIEE 10g Web Catalog

• Deploy the Dashboard Images

• Update the BI Physical Layer Connection to the ODI Work Repository

For OBIEE 11g, you need to perform the following post-installation tasks:

• Deploy the Dashboard Images

• Update the BI Physical Layer Connection to the ODI Work Repository

Deploy the Web Catalog Requests in the OBIEE 10g Web Catalog

The OBIEE/ODI Lineage comes with a Web Catalog for building your reports on top of the Lineage and ODI Repository tables.To import the Web Catalog requests, perform the following steps:

1. Connect to your Web Catalog.

   To connect to your Web Catalog:

   1. Select Start > All Programs > Oracle Business Intelligence > Catalog Manager.

   2. Click File > Open Catalog.

   3. Provide the path to the web catalog used by the BI Server.

   4. Click OK.

2. (Optional Step) Make a backup copy of the catalog into which you want to install the lineage artifacts.

   To make a backup copy:

   1. Select the catalog.

   2. Select File > Archive.

   3. Provide a name for the archive file, for example webcatalog_backup.cat.

   4. Click OK.

3. Expand the catalog and select the shared folder into which the ODI catalog items will be imported.

4. Select File > Unarchive.

5. In the Unarchive catalog window, enter in the Archive File Path field the location of the ODI catalog archive file. Note that this file is located in the /artifacts/10g sub-folder of the Lineage installation folder.

   • For OBIEE 10.1.3.3, enter artifacts/10godi_catalog_archive_10g.cat

   • For OBIEE 10.1.3.4, enter artifacts/10g/odi_catalog_archive_10_1_3_4.cat

6. Click OK.

A new folder called ODI appears in the catalog folder.

Deploy the Dashboard Images

The prepackaged requests use images that should be deployed into the application server that hosts the analytic application. Theses tasks depend on your OBIEE Server version:

• For OBIEE 10g, copy the dashboard images (hie.gif and lin.gif, located in the in the /artifacts/images sub-folder of the Lineage installation folder) to the res folder under the deployment directory of the BI analytics application.

  For example:

  <OC4J_HOME>\j2ee\home\applications\analytics\analytics\res

• For OBIEE 11g, copy the dashboard images (hie.gif and lin.gif, located in the in the /artifacts/images sub-folder of the Lineage installation folder) to the res folder under the deployment directory of the BI analytics application.

  For example:

  <DOMAIN_HOME>\servers\<SERVER_NAME>\tmp\_WL_user\analytics_11.1.1\7dezjl\war\res

Update the BI Physical Layer Connection to the ODI Work Repository

1. Start the Oracle BI Administration tool. For example, select All Programs > Oracle Business Intelligence > Administration.

2. Open the RPD file (.rpd) used by the BI Server.

3. 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:

   1. Update the Data source name, Username and Password fields.

   2. Click OK.

   3. Right-click the Physical schema and rename it to match the schema of the ODI Work Repository.

   4. Click OK to save your changes.

4. Expand the renamed schema and test this updated connection as follows:

   1. Right-click one of the tables of this physical schema and updating the row count.

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

19.4.1 Exporting the OBIEE Repository Documentation to a Text File

This section explains how to manually export the OBIEE Repository metadata for consolidating it in the OBIEE Lineage.

To export the OBIEE Repository documentation to a text file:

1. Open the Oracle BI Administration tool and connect to the OBIEE Repository containing the metadata that you want to include in the lineage.

2. In the OBIEE Administration tool, select Tools > Utilities.

3. In the Utilities dialog, select the Repository Documentation utility and click Execute.

4. Save the repository documentation in a temporary file, for example c:\temp\repo_doc.txt.

   Make sure to save this repository documentation as Tab-separated values (*.txt) file type

5. Click Save.

19.4.2 Exporting the OBIEE Web Catalog Report to a Text File

This section explains how to manually export the OBIEE Web Catalog metadata for consolidating it in the OBIEE Lineage.

To export the OBIEE Web Catalog report to a text file:

1. Open OBIEE Catalog Manager and connect to the catalog that contains the metadata that you want to include in the lineage.

2. Select the catalog folder containing the reports that you want to include in the lineage, for example /shared/Paint Demo or /shared/ODI.

3. Select Tools > Create Report.

4. In the Create Catalog Report dialog, select the following columns to include in the report: Owner, Request Folder, Request Name, Request Subject Area, Request Criteria Formula, Request Criteria Table, Request Criteria Column.

   M ake sure to include these columns in this precise order.

5. Save the report in a temporary file, for example c:\temp\webcat_doc.txt.

6. Click OK.

7. Check the Report Preview and click OK.

19.4.3 Refreshing the OBIEE Lineage From Existing Exports

This section describes how to refresh the OBIEE Lineage from existing OBIEE Repository and Web Catalog exports created manually.

To refresh the OBIEE Lineage:

1. Start the OBIEE Lineage wizard as described in Section 19.2.3.1, "Installing and Starting the OBIEE Lineage Wizard".

   Note:

   You can also use the refresh lineage script instead of the OBIEE Lineage wizard. See Section 19.5.3, "Automating Lineage Refresh" for more information.

   The wizard displays a sequence of screens, in the order listed in Table 19-3.

2. Follow the instructions in Table 19-3.

If you need additional help with any of the installation screens, click Help to access the online help.

19.5.1 Configuring the Scripts

Before starting any of the scripts, you need to provide the configuration information in a property file. This property file contains the values provided via the wizard user interface.

Figure 19-4 lists the properties defined in the property file.

Example 19-1 shows a sample property file:

# Version of BIEE Server. Values: 10g / 11g
OBIEE_VERSION=10g

# The location of the repository documentation (.rpd) file of the BI Server
OBIEE_RPD=C:/obiee11g/instances/instance2/bifoundation/ OracleBIServerComponent/coreapplication_obis1/repository/TechDemo_11g.rpd

# The location of the Web Catalog folder used by the BI Server. 
# Required only for OBIEE 11g.
OBIEE_WEBCAT=C:/obiee11g/instances/instance2/bifoundation/ OracleBIPresentationServicesComponent/coreapplication_obips1/catalog/TechDemo

# The OBIEE Repository user. Required only for OBIEE 10g. 
OBIEE_RPD_USER=Administrator
# The password of the OBIEE Repository user
OBIEE_RPD_PASS=<obiee password>

# The location of the exported Repository Documentation file
OBIEE_RPD_EXPORT_FILE=c:/odi/lineage/run/repo_doc.txt
# The location of the exported Web catalog file
OBIEE_WEBCAT_EXPORT_FILE=c:/odi/lineage/run/webcat_doc.txt

# The BI Server Oracle Home directory
OBIEE_ORACLE_HOME=C:/obiee11g/Oracle_BI1
 # The BI Server Instance Home directory. Required only for OBIEE 11g.
OBIEE_INSTANCE_HOME=C:/obiee11g/instances/instance2

# The JDBC URL to connect to the ODI Master Repository
ODI_MASTER_URL=jdbc:oracle:thin:@localhost:1521:orcl
# The JDBC Driver to connect to the ODI Master Repository
ODI_MASTER_DRIVER=oracle.jdbc.OracleDriver
# The Database user for the schema that contains the ODI master repository. 
ODI_MASTER_USER=MASTER_REPO
# This user's password 
ODI_MASTER_PASS=<master_password>

# The ODI user with SUPERVISOR privileges
ODI_SUPERVISOR_USER=SUPERVISOR
# The ODI Password of the ODI User with SUPERVISOR privileges
ODI_SUPERVISOR_PASS=<supervisor password>

# Work Repository containing the lineage
ODI_SECU_WORK_REP=WORK_REP1

# The Web Catalog folder to export in the report. Eg: /shared/ODI
OBIEE_WEBCAT_FOLDER_TO_EXPORT=/shared/ODI

# Option to deploy ODI Artifacts on the BI Server. 
INSTALL_ODI_LINEAGE=no
# Option to export BI Metadata as flat files
EXPORT_OBIEE_METADATA=yes

Encoding Passwords

To avoid storing the passwords in plain text, use the encode.[sh|bat] <password> command to encode and store the passwords in the property file. If the password are encoded, the property names will change to ODI_MASTER_REPO_ENCODED_PASS, ODI_SUPERVISOR_ENCODED_PASS, and OBIEE_RPD_ENCODED_PASS.

19.5.2 Automating Lineage Deployment

The install lineage script deploys the following ODI Artifacts in the OBIEE Server:

• Lineage RPD

• Lineage Web Catalog (11g OBIEE only)

The script uses the OBIEE tools to merge the Lineage RPD and Lineage Web Catalog with the BIEE Server components.

Syntax

The script syntax is as follows:

installlineage.bat [-propertyFile=property_file] [-prop_name=prop_value [...]] [-usage]

where:

• propertyfile represents the Property File that contains all the required properties to install the lineage artifacts. See Section 19.5.1, "Configuring the Scripts" for more information. If no value is specified, the User Wizard will be launched to gather the required information from the User. All the properties in the property file can be overridden by specifying the property value in the command line option -propName=propValue.

• prop_name represents the property that can be specified. The value specified in prop_value will override the value specified in the property file (if any).

• prop_value represents the value for the prop_name property. It will override the value specified in the property file (if any).

• usage prints the detailed usage information

19.5.3 Automating Lineage Refresh

The refresh lineage script performs one of the following operations, depending on the value set in the EXPORT_OBIEE_METADATA option defined in the property file:

• Export and refresh metadata, if the EXPORT_OBIEE_METADATA option is set to Yes

• Refresh lineage metadata, if the EXPORT_OBIEE_METADATA option is set to No

Note that in order to use refreshlineage.sh you need to manually copy the repo_doc.text and the webcat_doc.txt files to the target Linux machine.

Syntax

The script syntax is as follows:

refreshlineage  [-propertyFile=property_file] [-mappingFile=mapping_file] [-prop_name=prop_value [...]] [-usage]

where:

• propertyfile represents the Property File that contains all the required properties to export and consolidate lineage metadata. See Section 19.5.1, "Configuring the Scripts" for more information. If no value is specified, the User Wizard will be launched to gather the required information from the User. All the properties in the property file can be overridden by specifying the property value in the command line option -prop_name=prop_value.

• mappingfile represents the mapping of the Model code to BI_PHYSICAL_DB, BI_PHYSICAL_SCHEMA and BI_PHYSICAL_CATALOG. This mapping must be provided in the form of a comma separated values (.csv) file.

Example 19-2 shows a sample mapping file.

# (c) Copyright Oracle.  All rights reserved.
# Sample Mapping File for ODI-OBIEE Metadata Lineage
# Format: BI Physical DB, BI Physical Schema, BI Physical Catalog, ODI Model ID
# Note: Lines starting with # are considered as comments.
DB-1,Schema-1,Catalog-1,model1
DB-2,Schema-2,Catalog-2,model2

19.6.1 Viewing Execution Statistics

In this scenario, we want to display the execution statistics of ODI within a OBI-EE dashboard.

To add ODI statistics, insert the RuntimeStats request from the Lineage Web Catalog into your dashboard. The statistics appear as shown in Figure 19-2.

Figure 19-2 Runtime Statistics

Description of "Figure 19-2 Runtime Statistics"

19.6.2 Viewing and Filtering Lineage Data

In this scenario, you want to view the lineage data and filter the results.

To create such a dashboard, add the Prompt Lineage dashboard prompt and the LineageRequestColumns request on a dashboard. Both objects are in the lineage web catalog as shown in Figure 19-3.

Figure 19-3 Lineage Web Catalog

Description of "Figure 19-3 Lineage Web Catalog"

Figure 19-4 shows the resulting dashboard.

Figure 19-4 Resulting Dashboard

Description of "Figure 19-4 Resulting Dashboard"

19.6.3 Using the Dashboard

In this dashboard, you can filter using:

• The Origin of the column (ODI Column or OBI-EE Logical, Physical, Presentation or Request Column)

• The OBI-EE Folder/Catalog or ODI Project containing the table and the column

• The Request or table containing the column

Click Go to display the filtered list of columns.

19.6.4 Using Lineage and Hierarchy

From this request, you can display the Lineage and Hierarchy for each column by clicking one of the following buttons:

	Lineage
	Hierarchy

Using the Lineage

The Lineage icon allows you to drill down into a column lineage. The lineage goes down the following path:

> The OBIEE Presentation Column(s) used in a request's column

> The OBIEE Logical Column(s) used in a Presentation Column

> The OBIEE Physical Column(s) used in a Presentation Column

> The ODI Column(s) corresponding to OBIEE Physical Column(s)

> The ODI source columns used to load a given ODI target column via an ODI interface. This path can recurse if the source columns are targets for other ODI interfaces.

For each level of the lineage, the dashboard displays:

• The Type, Catalog, Table Name, and Column Name for the (target) column

• The Type, Catalog, Table Name, and Column Name for the (source) column(s)

• The transformation Expression between the source column(s) and the target column

• If the expression is an ODI mapping, you can drill down the ODI run-time statistics (Exec. Stats) for this transformation.

• You can drill down at any point of the lineage by clicking Lineage in the view.

Figure 19-5 shows one lineage level displayed in a dashboard.

Figure 19-5 Lineage Level

Description of "Figure 19-5 Lineage Level"

Using the Hierarchy

The Hierarchy displays the entire lineage of a given request column in a hierarchical view. Figure 19-6 shows the hierarchical column lineage.

Figure 19-6 Hierarchical Column Lineage

Description of "Figure 19-6 Hierarchical Column Lineage"

19.6.5 Using Contextual Lineage

You can create contextual lineage link using the LineageRequestColumns on any dashboard. This contextual lineage link will open a dashboard showing the lineage for a given request.

To create contextual lineage:

1. Edit a Dashboard.

2. Insert a Text object with the following code:

   <p><font class=Nav onclick="JavaScript:GoNavEx(event, '<lineage_requests_folder>/LineageRequestColumns','','Target Column','Catalog','<your_request_folder>','Target Column','Table Name','<your_request_name>');"><img src="res/lin.gif" alt="Navigate Metadata Lineage">&nbsp;Metadata Lineage</font>
   

   In this code, you must set the following items according to your configuration:

   • <lineage_requests_folder> is the folder containing the LineageRequestColumns request. This folder is the folder into which the OBIEE Lineage Requests have been deployed.

   • <your_request_folder> is the folder containing the request for which you want to display the lineage.

   • <your_request_name> is the name of the request for which you want to display the lineage.

   For example, if the lineage requests are installed in the /shared/ODI folder, and you want to view lineage for the /shared/ODI Customer Demo/Customer Per Countries Chart request, the code will be:

   <p><font class=Nav onclick="JavaScript:GoNavEx(event, '/shared/ODI/LineageRequestColumns','','Target Column','Catalog','/shared/ODI  Customer Demo','Target Column','Table Name','Customer Per Countries Chart');"><img src="res/lin.gif" alt="Navigate Metadata Lineage">&nbsp;Metadata Lineage</font>
   

3. Before saving your code, make sure that Contains HTML Markup is selected in the Text Properties editor as shown in Figure 19-7.

   Figure 19-7 Text Properties Editor

   
   Description of "Figure 19-7 Text Properties Editor"
   
   

   This text will create a link on the dashboard that opens the column lineage for the given request.

4. Click OK.

The Metadata Lineage object is added to the dashboard as shown in Figure 19-8.

Figure 19-8 Text Object on Dashboard

Clicking Metadata Lineage displays the dashboard shown in Figure 19-9.

Figure 19-9 What is displayed when clicking on "Metadata Lineage"