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