Export OBIEE RPD Documentation (Data Lineage) From The Command Line

The OBIEE Administration Tool provides a useful utility for exporting the data lineage held within the RPD to a CSV file. The linage is pretty comprehensive and covers everything from Presentation Column to Physical Database Column. You can then interrogate the metadata to find out things like which tables are involved in specific subject areas, what logical expression is used for a given Presentation Column, and hundred and one other things.

I think we can agree that this is usefull stuff then, and having it in a format that can be easily queried (and outside of the RPD) can only help matters.

My biggest issue with this is that the export involves the OBIEE Administration Tool, and it’s a very manual process.

Manually Export RPD Documentation

To export the OBIEE RPD Metadata you’ll need to load the Administration Tool and open your RPD – you can do this online or offline, depending on how you usually connect. Once that’s done, click on Tools and then Utilities.

In the Utilities dialogue box select Repository Documentation and click Execute. You’ll be asked to select a directory and file name to use when saving the CSV output. Select your desired name and path and click Save.

It can take a few seconds, depending on the size of your RPD, then you’ll have your exported RPD documentation.

Automate RPD Documentation Export

Luckily, there is a way to automate the RPD Documentation export to CSV using the command line. I must state at this point that the below admintool switches are not supported by Oracle, or even documented from what I can see.

The below two scripts run in a Windows environment (as does the Admin Tool!) and can be scheduled or executed manually as required.

First, create the Windows bat file that you’ll run to initiate the process. Create a file called rpd_doc.bat and paste in the below script.

@echo off

REM The temp output location of the CSV output. This will be removed on success
set OUTPUT_CSV_TEMP=C:\Temp\temp_repo.csv
REM The final location to copy the CSV output to
set OUTPUT_CSV_FINAL=C:\Users\James\Desktop\temp_repo_final.csv
REM The location of the RPD file to use for the export
set RPD=C:\oracle\fmw\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\repository.rpd
REM The temp output location of the rpd. a copy of your RPD is taken so that the originial is never opened by the AdminTool. This will be removed on success
set RPD_TEMP=c:\Temp\rpd.rpd
REM location of the RPD instruction file
set CMD=c:\Temp\export_metadata.txt
REM Settings taken from your bi_init.bat file. This may be found at C:\Program Files\Oracle Business Intelligence Enterprise Edition Plus Client Tools\oraclebi\orahome\bifoundation\server\bin
set ORACLE_INSTANCE=C:\Program Files\Oracle Business Intelligence Enterprise Edition Plus Client Tools\oraclebi\orainst
set ORACLE_BI_APPLICATION=coreapplication
set COMPONENT_NAME=%ORACLE_BI_APPLICATION%

REM include the below line if you're exporting from a server installation of OBIEE
REM call %ORACLE_INSTANCE%\bifoundation\OracleBIApplication\%ORACLE_BI_APPLICATION%\setup\user.cmd

REM do not edit below this line. 
copy %RPD% %RPD_TEMP%

set PATH=%ORACLE_HOME%\bifoundation\server\bin;%ORACLE_HOME%\bifoundation\web\bin;%ORACLE_HOME%\bin;%JAVA_HOME%\bin;%windir%;%windir%\system32;%PATH%

"C:\Program Files\Oracle Business Intelligence Enterprise Edition Plus Client Tools\oraclebi\orahome\bifoundation\server\bin\admintool" /command c:\Temp\export_metadata.txt

If not exist %OUTPUT_CSV_TEMP% Exit 

REM copy csv to final location
copy %OUTPUT_CSV_TEMP% %OUTPUT_CSV_FINAL%

REM cleanup 
del %OUTPUT_CSV_TEMP%
del %RPD_TEMP%

There are a few things that you’ll need to manually edit:

• OUTPUT_CSV_TEMP – this is just a temporary name and location for the exported CSV. It will be removed once the process completes successfully.
• OUTPUT_CSV_FINAL – is the final export containing all of the RPD documentation. If it already exists, it will be overwritten.
• RPD – is the location of the RPD you would like to analyse.
• RPD_TEMP – is similar to OUTPUT_CSV_TEMP and is only used so that the ‘live’ RPD isn’t used.
• CMD – is the location of the text file containing the Admin Tool commands. You’ll create this is the next step below.
• ORACLE_INSTANCE and ORACLE_BI_APPLICATION are taken from your bi_init.bat script in your Administartion Tool installation.
• COMPONENT_NAME – is the instance name used by your installation. It’s usually the same as the above ORACLE_BI_APPLICATION value.

Next, create another file in the same location called export_metadata.txt. Add the following:

Open c:\temp\rpd.rpd Administrator PASSWORD
  Hide
  DescribeRepository c:\temp\temp_repo.csv UTF-8
  Close
Exit

There are two commands here that you’ll need to specify the path for.

• Open is the location of the temporary RPD that you specified in RPD_TEMP. 
• DescribeRepository is the location of the temporary CSV export that you specified in OUTPUT_CSV_TEMP .
• PASSWORD is the password used to access the RPD.

And that’s all there is to it! Simply run the first file you created (rpd_doc.bat), wait a few moments and you’ll have a CSV export of the RPD in the OUTPUT_CSV_FINAL path you specified.