Reporting on ODI Load Plans Executions with OBIEE

In this post I will describe a quick-and-dirty way of building reports on the ODI Load Plans Executions using Oracle Business Intelligence (OBIEE).

Although it’s probably not considered best practice, OBIEE allows you to quickly build a report upon a physical SQL statement by navigating to New > Analysis > Create Direct Database Query.

Enter the name of your Connection Pool and then paste this code into the SQL Statement textarea (replace ODI_REPO_SCHEMA with the schema name of your ODI Repository):

select start_date,
       substr(numtodsinterval(duration, 'SECOND'), 12, 8) as duration,
       case status
          when 'W' then '<span style="color:red">Waiting</span>'
          when 'R' then '<span style="color:red">Running</span>'
          when 'E' then '<span style="color:red">Error</span>'
          when 'D' then '<span style="color:green">Done</span>'
          when 'M' then '<span style="color:red">Warning</span>'
          when 'A' then 'Already done in a previous run'
       end status_msg
  from ODI_REPO_SCHEMA.snp_lpi_run
 where start_date >= trunc(sysdate)
 order by start_date

Once you click on the Validate SQL and Retrieve Columns button, the Result Columns will appear at the bottom of the page:


Now you need to tell OBIEE to treat the STATUS_MSG column as HTML code in order to display the message using red or green color:


Once you click on the Results tab you should see a report with this layout:


Now you can easily schedule the delivery of this report via email through an Agent (iBot).

Note: I have tested this on ODI version 12.1.3 and OBIEE version


Oracle Data Integrator 11g and 12c Repository Description (Oracle Support Doc ID 1903225.1).

BISQLGroupProvider: Connection pool not usable

It’s possible to configure Oracle Business Intelligence to perform user authentication using an LDAP Identity Store, such as Microsoft Active Directory, while storing group information in database tables.

The setup of this configuration for OBIEE, which is mostly performed inside Oracle WebLogic Server Administration Console, is explained in the official documentation in the section Configuring LDAP as the Authentication Provider and Storing Groups in a Database.

Since the process is quite complicated, however, it’s easy to forget something and end up with a broken configuration.

In my case, after performing the configuration and restarting the whole OBIEE stack, I was unable to list groups.

Looking at the AdminServer.log file, I found the following Java Exception being logged over and over:

<BEA-240003> <Console encountered the following error Error listing groups:
Caused by: java.sql.SQLException: Connection pool not usable.

After double-checking the JDBC Data Source configuration and the SQL statements of the SQLGroupProvider, I finally managed to identify the problem: I had deployed the BIDatabaseGroupDS data source to the bi_server1 server, but not to the AdminServer:


Using English locale in Oracle tools

As a developer, whenever I have a choice I install my OS with English localization, and even go as far as using a U.S. keyboard since typing curly brackets and back-ticks with the Italian keyboard layout is not fun.

Sometimes, however, you have no such luxury, for example with a company-issued notebook.

An irritating consequence of using a non-English locale is that some programs try to be clever and automatically display their user interface in your language. Although I can see the benefit for non-advanced users, this is something I personally dislike, since it makes it more difficult to search for documentation or google error messages.

In this post I will try to document in a single place how to force the user interface to appear in English in some Oracle tools I use on a daily basis.

Oracle BI Administration Tool (OBIEE 11.1)

As documented in Modifying the Language of the User Interface for the Administration Tool, create the following environment variable:


Oracle Data Integrator (ODI 12.1)

As documented in MOS Note How To Set Up The GUI Runtime Language In ODI Studio (Doc ID 1271743.1), add the following lines in the file odi/studio/bin/odi.conf:

AddVMOption -Duser.language=en
AddVMOption -Duser.region=US

Oracle Warehouse Builder (OWB 11.2)

As documented in MOS Note How To Change the GUI Language in OWB 11.2 (Doc ID 1269876.1), add the following line in the file OWB_HOME/ide/bin/ide.conf:

AddVMOption -Duser.language=en

Oracle SQL Developer

As documented in Jeff Smith‘s blog post Oracle SQL Developer Around the World, add the following line in the file sqldeveloper/bin/sqldeveloper.conf:

AddVMOption -Duser.language=en