Setting up OBIEE Metadata Lineage with ODI

Oracle Business Intelligence Metadata Lineage with Oracle Data Integrator in 10g

Oracle Business Intelligence Metadata Lineage with Oracle Data Integrator in 11g

Oracle Business Intelligence Metadata Lineage with Oracle Data Integrator in 10g

Overview

This Oracle 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. 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:

• Contains the consolidation process and is used to run it
• Contains all the Extract-Transform-Load (ETL) processes that load the data warehouse
• Hosts the lineage tables

This guide is based on an oracle tutorial which 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.

Software Requirements
The system should include the following installed products:

• OBIEE 10g 10.1.3.0 and above
• Oracle Database 10g XE
• Oracle Data Integrator 10g (10.1.3.4)
• Oracle Data Integrator 10.1.3.4.2 patch from Metalink. To obtain this patch, perform the following steps:
  1. Go to http://metalink.oracle.com.
  2. Log in with your username and password.
  3. Click the Patches and Updates tab, and then click Simple Search.
  4. From the Search By drop-down menu, select Product or Family and enter odi.
  5. Click Go to download.
• The lineage is implemented for an ODI repository that is hosted in Oracle Database version 10g and
  later.

The system should include the following configuration:

• The ODI Master and Work repositories must be created. You can refer to the steps from here.
• The ODI Work repository must have an ID that is different from 101. To check the repository version, open its
  properties from the Topology Manager > Repositories tree view.
• If not done before, start the services and components for Database 10g XE, Oracle Data Integrator 10g (10.1.3.4), and the following services for OBIEE 10g 10.1.3.0.
  • BI Server
  • BI Presentation Server
  • BI Java Host
  • OC4J

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:

• The ORA_ODI_REPO Oracle logical schema. This schema must point to the Oracle physical schema that contains the tables of the ODI work repository. This schema also contains the lineage tables. The name of the logical schema must be ORA_ODI_REPO. This schema is used both as source and target in the metadata consolidation phase. OBIEE will also use these tables for data lineage reporting.
• The FILE_BIEE_EXPORT_FILES file logical schema. This schema must point to a file physical schema that represents the OBIEE_EXPORT_TEMP directory. The name of the logical schema must be FILE_BIEE_EXPORT_FILES. This schema is used both as source and target in the metadata consolidation phase.

- 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).


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


- 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


- 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, 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.



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




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



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











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

















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








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


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.








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








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











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








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


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.








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

 

Oracle Business Intelligence Metadata Lineage with Oracle Data Integrator in 11g


To install the OBIEE Data Lineage feature, you need to make sure you’re on ODI 11.1.1.5 and then download the ODI Companion CD along with ODI itself. Once you’ve done this, you run a Data Lineage Wizard that ships as part of the Companion CD, and you’re given three options to work with.





You can set this up for both OBIEE 10g or 11g, but the install is a bit simpler and automated with 11g. To set things up, select Install Lineage in OBIEE Server, make sure the BI Server and Presentation Server are both stopped, and pick an RPD for the wizard to merge the physical, logical and subject area contents in to.





The wizard installs objects in your repository (RPD), together with analyses in your presentation catalog. Once its run, you can take a look at what’s been created.





There’s a couple of steps to change the supplied connection pool details to point to your combined ODI and master repositories (this works best if you’ve used the RCU to create your ODI repositories), but once it’s all done and you’ve checked the connections, you’ve got a set of tables that hold combined ODI and OBIEE lineage data that then needs to be initially loaded using the wizard.





Selecting the Export Metadata from OBIEE and Refresh Lineage option copies repository information from your RPD file, and analyses and dashboard information from your presentation catalog, into a set of new tables in your ODI repository. You need to run this step every time your OBIEE or ODI repository information changes, and there are supplied scripts that you can use to automate the process.


Once you’ve supplied the connection details to your OBIEE repository and catalog, you then match up the ODI models in your ODI repository with the physical databases in your OBIEE repository, so that the lineage links can be created.





Once it’s all run and setup, you can then start querying your data lineage using the supplied analyses, or create your own. In the example below, I’ve selected a subject area table from the OBIEE repository, and the analysis initially shows me the columns that it contains.





Pressing the Lineage button shows me some more information about a selected subject area column, including where it came from in the source database.





A “Runtime Stats” analysis shows me the history of agent executions within my repository, allowing me to analyze past runs, identify long-running process and spot where errors and warnings are getting raised.





You can also add contextual links to analyses in your catalog, to allow users to display details on where the data items came from in their report. For example, in the dashboard below, I’ve added a link as a text item under the analysis, which the user can then click on to display lineage data.





Clicking on the link, which is actually some embedded HTML in a text item added to the dashboard, calls another report and passes across the name of the request we’re interested in, giving us a contextual lineage report.





Please refer to the ODI 11.1.1.5 New Features Oracle document for more information and new features supported.