WeBlog - How to query and get data from OBIEE with a database client (Jdbc)

About

This article will show you how you can query data in OBIEE through a database client application.

This is actually really handy when you want to:

create automatic test to control the repository design (such as join, aggregate, …)
connect a third analytics application. Business Intelligence Publisher - BIP uses it if I remember wel.

Below is a step by step made with the database IDE Dbeaver. You will:

create a driver. (A driver for Dbeaver is the definition of default connection parameters for a database)
make a connection
check the data of a table
and ultimately execute a OBIEE select query.

On a detailed technology side, the connection is made to the OBIS (Oracle BI Server) via JDBC. You can see this service (ie OBIS) as a database that you can only query (SELECT). You will query the presentation layer with the SQL of OBIEE (logical SQL) (ie of OBIS).

This blog is a complement to the JDBC page of OBIS: OBIS - JDBC

Step by Step

Install Dbeaver

Download the installer for your computer and run it.

https://dbeaver.io/download/

Create a driver

Go to Menu > Database > Driver Manager and click on the new button.
Fill the form with the below content:

where:

JDBC - Driver is oracle.bi.jdbc.AnaJdbcDriver
URL template is jdbc:oraclebi://{host}[:{port}]/user={user} Without the user parameter, you get a Null connection. Bad URL parsing
Default port is for 12c 9514 (9703 for an older version)
Add the Jdbc Driver that you can find after a OBIEE client installation at this path OBIEE_CLIENT_HOME\bi\clients\bijdbc.jar

Create a new connection

Create a new connection. Menu > Database > New Connection

Due to the structure of the Logical SQL, you need to add a default schema in order to see its data in the IDE when you select a table.

Check that it works This is the adventure works schema because I was working in a Azure Cloud environment

To write a query, you just gives the columns and the filtering but you don't defined the aggregation and the join as they are already defined in your repository. This is really powerfull.

Example:

select
	Address.City,
	Orders.OrderQty
from
	"Adventure - SalesOrderDetail".Address as Address,
	"Adventure - SalesOrderDetail".SalesOrderDetail as Orders

Conclusion

That's it. I hope you enjoyed it and that you got as much fun as I got when I discovered this feature.

Have a nice day

Nico