JNDI for BI Server – Part 2

This is a follow up for the post I’ve written some time back about the JNDI data source for the Oracle BI Server.

Recently I’ve had a chance to revisit this subject when one of our clients had a performance issue with their BI Publisher reports. The issue was that when they were trying to open a report it takes a long time to get the page with all the parameters. This issue became to be worse when they started testing with multiple users at a same time.

So first, we looked at the report and found it had many parameters which were using SQL queries, some were going against a Oracle database and some were going against BI Server with JDBC. This is a very typical report but can be a cause for the bad report performance.

Every time you run a BI Publisher report it creates a new connection for each query. So if you have 5 SQL base parameters, that means BI Publisher opens 5 connections separately without sharing the connection even when all or some of the queries are going against the same data source.

In order to address this issue we usually recommend to use JNDI data source. You can create a JDBC data source connection pool at the application server level and register it as JNDI, and you can use it from BI Publisher. This way your SQL queries in the reports and parameters can share the same connection through the JDBC connection pool.

Now, what if you need to pass the BI Publisher’s session username to the data source to get authorized or filter the returned data ? You can check the ‘Proxy Authentication’ check box to enable the proxy authentication, which pass the BI Publisher’s session user information to the data source.

However, this, proxy authentication with JNDI for Oracle BI Server, is not supported when the data source is Oracle BI Server with the BI JDBC.

That means, again, when you have 5 parameters, all of which are querying against a same BI Server, BI Publisher needs to open the JDBC connection 5 times! This obviously has significant performance impact especially when more users accessing the same or similar reports.

Well, the good news is, the BI Publisher product development has fixed this issue already by implementing a way that BI Publisher shares the connection for the parameter queries instead of opening and closing for each parameter. This means, you don’t need to use the JNDI connection for the BI Server to improve performance anymore. You can keep using the JDBC connection and set the ‘proxy authentication’ if needed. This fix is available in the latest BI Publisher’s October patchset release, which can be downloaded from our support web site. The patch number is 9017232. 

I would still recommend to use JNDI connection for other reasons as I described in the previous post, but to improve the performance of the parameter values rendering now we don’t need to worry about thanks to this new implementation!