OBIEE - Automatically Generate Repository Documentation

I have strived to close the loop with automating all of the internal reporting on OBIEE.

So far we have automated the capture of what reports, filters and prompts are on our dashboards.

We have in place an automatic system for recording the performance of the reports on the current dashboards. This we run every night and the results uploaded to the database.

We have the option of running an 'after' script which compares the output from dashboards (logical sql, row counts and timings) when we upload a new repository, this gives us impact analysis.

What we lacked was a way to automate the generation of the repository documentation. This was the manual step that was sometimes skipped.

Having a report on the repository, from physical to presentation and then joining this data to what's on the dashboards gives us a view of what's being used where and where the data is sourced from.

Now I must admit, this seems like a 'hack' and I wouldn't use it on any environment where I couldn't freely break things without causing damage. Oh, and it is windows only - there is no AdminTool for Linux/Solaris - sigh.

The Repository Command Script

So what do we have to do?

We are going to use the /command switch of the AdminTool to run a script of, you guessed it, .... commands.

The command script we've called report_rep.build and put in the following few lines, replacing MWHOMW with your own settings, repository with your own and the repository password with your own.

OpenOffline <MWHOME>\instances\instance1\bifoundation\OracleBIServerComponennt\coreapplication_obis1\repository\repository.rpd <repository password>
DescribeRepository D:\scriptoutput\repository_report.csv UTF-8
Close
Exit

The next part involves creating a script to run the AdminTool and pass in the script file. Just to be safe it may be worth setting the environment using some of the bits from the bi-init.cmd as follows:

@echo off
set ORACLE_HOME=<MWHOME>\Oracle_BI
set ORACLE_INSTANCE=<MWHOME>\instances\instance1
set ORACLE_BI_APPLICATION=coreapplication
set JAVA_HOME=<MWHOME>\Oracle_BI1\jdk
set ESSBASEPATH=%ORACLE_HOME%\clients\epm\Essbase\EssbaseRTC
call %ORACLE_INSTANCE%\bifoundation\OracleBIApplication\%ORACLE_BI_APPLICATION%\setup\user.cmd
set PATH=%ORACLE_HOME%\common\ODBC\Merant\5.3\Drivers;%ORACLE_HOME%\bifoundation\server\bin;%ORACLE_HOME%\bifoundation\web\bin;%ESSBASEPATH%\bin;%ORACLE_HOME%\bin;%PATH%
<MWHOME>\Oracle_BI1\bifoundation\server\bin\AdminTool.exe /command d:\scripts\report_rep.build

Before running the above script we copy the latest repository to one side and call it repository.rpd. Not very imaginative I know but that way we don't muck around with the actual repository and, in theory, if we script it right, this will always be a copy of the latest and most up to date RPD.

You should be left with a CSV report of your repository (all being well).

Upload to your favourite database and analysis tool.

OBIEE - Displaying the Value of System Variables

Following from the previous article here on the subject of displaying your repository variables, I thought it may be time to look at showing some of the system variables

On a dashboard, drag in a text item and edit the contents to include something like

We are running OBIEE version [u][b]-@{system.productVersion} [/b][/u]
This is the @{dashboard.name} dashboard
and we are on the @{dashboard.currentPage} page.

OBIEE - Report Footer

Report Footer

You can create a report footer by adding another Title view and editing the layout.
- set the display text to custom and enter "Confidential" or some sort of disclaimer text.
- set the style to show the text as centered, bold and Italic.
- there you go, a confidential footer for your report.