ODI 11g New Mapping and Interface Features - Part 3

In the first two parts of this min-series, I looked at the new mapping and interface features with ODI 11.1.1.3, and yesterday I took a look at Load Plans, a new feature that's come with the 11.1.1.5 release of Oracle Data Integrator. Today, to finish-off the series, I'll be taking a look at something that intersects both ODI and OBIEE; "Oracle Business Intelligence Data Lineage", a new feature that's also just shipped with ODI 11.1.1.5

Something that's always been a weakness with Oracle's BI&DW tools (and indeed, with most vendors' tools) is the multiple, fragmented repositories that they use. When you create data objects and data mappings in ODI, there's no in-built link between those and the repository objects you create with the OBIEE Administration tool, and it's also tricky to trace data elements from analyses and dashboards through to the OBIEE repository, let alone the ETL tool and the underlying data source. The Oracle Business Intelligence Data Lineage feature with ODI 11.1.1.5 looks to address this by linking the ODI and OBIEE repositories and allowing you to query lineage and impact across both sources.

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.

From that point on, I can drill-down further through the business model and mapping columns, physical layer columns, through to the ODI repository objects that were used to populate the request data.

So there we are, the last of the new ODI 11.1.1.5 features that I wanted to cover. Take a look through the ODI 11.1.1.5 New Features PDF if you're interested in hearing what else is new with this release.