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.

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} <note tip>Without the user parameter, you get a Null connection. Bad URL parsing</note>
  • 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 <note tip>This is the adventure works schema because I was working in a Azure Cloud environment</note>

_

  • 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


Powered by ComboStrap