The other day I was playing around with the Direct Database Request (DDR) feature within OBIEE. The reason I did this is I wanted to create a report that utilized columns from the database that were not exposed in OBIEE, yet still utilize the functionality of OBIEE. Why else would you use this and how do you set it up? That is a good question, Kevin McGinley wrote extensively about this topic, which you can find here. However, I am not here to talk about that, what I am going to talk about are two potential problems one could run into setting up the DDR connection pool information.
- What happens if you have multiple connection pools with non-unique names?
- What if you don’t have access to an RPD to find the name of the connection pool?
Typically, like in the article linked above, you would have unique connection pool names that would be entered into this box.
But, what happens when all of your connection pools are called “Connection Pool” for example?
In this instance, OBIEE does not know what database you are trying to connect to so you will receive the error nQSError: 27023 Ambiguous Connection Pool object. You can have multiple connection pools with the same name and OBIEE will work, because it is passing the query through the metadata layer, which has links to the physical database, so it knows what database to connect to. Because the DDR bypasses the metadata layer it does not have this crucial bit of information, so it throws the error.
In this instance, you have to options to resolve the issue of ambiguous connection pools. The first is to rename the connection pools so that they are unique. It can be named anything as long as it makes them unique. The other option if you happen to know the database you are going against and do not have the ability to edit the RPD, would be to explicitly define the database and connection pool with the following syntax: “Database Name”.”Connection Pool”, for example “DB1”.”Connection Pool”.
Now the second issue, what if you don’t have access to an RPD, or know what the connection pool name you are trying to connect to is? For this issue, there is a simple solution. What is that solution? Well I am glad you asked. All you need to do is run a simple query from the subject area that hits the database you want to do a DDR from. Then go Manage Sessions under the Administration tab and view the log of the query you ran.
Then on the last line of the logical SQL generated, right before the start of the physical SQL, it will tell you what the connection pool name is. This can then be copied from here and pasted into the connection pool box of the DDR.
These are two issues I discovered while trying to create DDRs and was unable to find much information on the topic. Again, if you want more detail on how to set up and use DDRs check out Kevin McGinley’s blog.
