In my recent post, I have shown how to gather the RPD and Webcatalog data required for data lineage. And now we will build an OBIEE rpd with these OBIEE Web Catalog and Repository CSV files by creating ODBC connections or we can import the CSV files data into a database table using any import tools. For example, I am using SQL developer to import data from CSV files into a database table. I used SQL developer excellent feature to import data from CSV files. This is very simple, just expand the database and right click on the tables. Then select Import Data.
Select the CSV file to import.
Define the CSV file properties, like format type = delimited and delimiter = tab.
Proceed to next wizards. This will create a table in specified Oracle DB and imports the data into it. Repeat the same steps for RPD_Data.csv files to load into other table.
Now import these tables into OBIEE RPD and build a repository with all 3 layers.
I have used the following business logic to join the Catalog and RPD data. (The table and column names are dependent on what you have customized)
Catalog. Subject Area = RPD. Subject Area
RPD.Presentation Table = Catalog. Table
Catalog.Presentation Column = RPD.Column
Deploy this RPD and configure an empty webcatalog (or you can use existed one) through Oracle Fusion Middleware EM and restart all the OBIEE services.
Now we are ready with the data lineage information. Just login to OBI Presentation Services and create the necessary reports to know about reports statistics and their columns definition all the way from physical sources.
For example,
Question 1: What are the physical columns used by a report?
Ok well, I’ll create a report with the required columns to answer this question (See the report below).
From above report I can answer, what are the columns the report is made up with, what is the definition of a metric and what is the source of that particular column. And the above report also tells me what happens if I delete a particular column from RPD like what are the reports that are being impacted?
Question 2: How many reports have been developed and who?
Create a report and answer there are 39 total reports and ownership details are below.
Like this we can create the reports to answer some crazy questions about column lineage.
Hope this helps you all..
Thanks,
Shiva