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):
substr(numtodsinterval(duration, 'SECOND'), 12, 8) as duration,
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'
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 220.127.116.11.0.
Oracle Data Integrator 11g and 12c Repository Description (Oracle Support Doc ID 1903225.1).
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 18.104.22.168, 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 oracle.bi.security.provider.wls.authentication.dbms.SQLGroupProviderDelegateException: 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
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
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
Oracle SQL Developer
As documented in Jeff Smith‘s blog post Oracle SQL Developer Around the World, add the following line in the file