In general every OBIEE Application user/developer needs to know the origin of the data displayed on reports. The general questions that everyone looks for answers to understand their application are:
- How the report columns are defined back in metadata?
- What are the OBIEE objects (Analysis, BIP Reports, Prompts and filters etc.) that are being referenced by a column(s)?
- How columns are mapped through metadata?
- Who are the owners of reports?
- And more …
And also if you want to modify/update your metadata definition, the developer/BI Analyst should be aware of the impact of that change in already developed content.
To answer all these questions, we need to build a column lineage, from dashboard to subject area, to physical column, and then to column in source system. We have so many tools to build this Data lineage, but within OBIEE itself, we have some great information that can be used to build this Data Lineage to answer many of questions but not all.
To build OBIEE Data Lineage, We need to extract the objects information from
- OBIEE Web Catalog and
- OBIEE Repository (RPD).
OBIEE Web Catalog Data:
The All Web Catalog objects information can be found using Catalog Manager Report. Generate a catalog report using Catalog manager and then export it to a tab delimited CSV file.
To export the OBIEE Web Catalog report to a CSV file:
- Open OBIEE Catalog Manager and connect to the catalog that contains the metadata that you want to include in the lineage. For example: E:\OBIEE\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalog\SampleAppLite
- Select Tools > Create Report.
- In the Create Catalog Report dialog, select the following columns to include in the report: Owner, Folder, Name, Subject Area, Formula, Table, and Column. Of course, this depends on what kind of analysis you are trying to do.
- Click OK, to generate the report.
- Save the report in a temporary file, for example c:\temp\catalog_data.csv.
- Click OK.
- The file will be saved as tab delimited CSV file with all the information of the columns that we pulled.
OBIEE Repository Data:
All repository objects information can be generated with Repository documentation utility available within OBIEE Administrator tool. Generate the repository documentation and export to a text file.
To export the OBIEE Repository documentation to a text file:
- Open the Oracle BI Administration tool and connect to the OBIEE Repository containing the metadata that you want to include in the lineage. For example, SampleAppLite.rpd
- In the OBIEE Administration tool, select Tools > Utilities.
- In the Utilities dialog, select the Repository Documentation utility and click Execute.
- Save the repository documentation in a temporary file, for example c:\temp\RPD_Data.csv.
- Click Save.
Lets see how to make use of this RPD and WebCatalog data to build column Lineage in my next blog post.