Business Intelligence – Oracle

Oracle Data Integrator 10.1.3.5 – Using OBI EE as a Data Source – Using BI EE JDBC Driver

Posted by Venkatakrishnan J on June 7, 2009

In a prior blog entry here, i had shown how to use BI Server as a data source for loading data from Essbase into custom tables. That approach basically used the Sun ODBC-JDBC bridge which in most cases has bad performance. Today we shall see how to use the native BI Server JDBC driver to connect to BI Server and in-turn use that for loading Essbase Data into a custom table.

One of the major advantages of ODI is the fact that it can be used to connect to any data source that has a valid jdbc driver. In order to make ODI to use the BI EE JDBC driver, we need to make the JDBC driver (jar file) of the BI Server to be accessible by ODI. The jar file(bijdbc14.jar) of BI Server can be obtained from the BI Publisher web server installation folder {OracleBI}\oc4j_bi\j2ee\home\applications\xmlpserver\xmlpserver\WEB-INF\lib. The main reason why we are going to the BIP folder structure is because BIP uses the JDBC driver of BI Server to do reporting on BI EE metadata.

image

The above screenshot basically provides the database driver class and the connection string of the JDBC driver of BI EE. Now copy this driver jar file to {OracleDI}\oracledi\drivers directory.

image

Now open up Topology Manager and create a new Universal Data Server. Use the above driver class and connection in the JDBC URLs.

image

Ensure that you are able to test the connection using Administrator username and password.

image

Now, in the physical schema choose any subject area in the Presentation Layer.

image

Now once this is done, go to the Designer and create a Project. And import the necessary Knowledge Modules that i had shown in the last blog entry. Since we are using JDBC, we can do a selective reverse of all the necessary tables.

image

But be aware that, BI EE supports multiple presentation tables with the same name across subject areas. But ODI currently does not support multiple table import with the same name (as there is no BI EE specific RKM. This can be resolved if you build a separate RKM. Also, though you might be able to reverse objects with special characters, they would not work within an Interface. So, its better to create aliases with proper ODI supported naming conventions. ). Lets choose just 4 such tables, Basic, Market, Scenario and Year.

image

image

image

Now lets build a very simple interface wherein we would be inserting Gen2, Market and the Measure value into a dummy table. Remember in BI EE, whenever you are bringing in multiple tables from a single presentation subject area, no joins are needed i.e. a cross join would have to be configured across multiple tables.

image

If you execute this interface you would notice that BI Server would generate the MDX accordingly and would then insert the output of the MDX to the database table.

image 

This should give you an idea of how to go about using multiple BI EE subject area tables in an ODI interface and then using that interface to load data into a database table. Also, this method should typically be as fast as your BI Server is since it uses the JDBC connectivity as opposed to the JDBC-ODBC bridge.

3 Responses to “Oracle Data Integrator 10.1.3.5 – Using OBI EE as a Data Source – Using BI EE JDBC Driver”

  1. Sid said

    Great post, thanks Venkat. Here is one thing that a reader may like to know: there is another directory from which JDBC driver can be obtained – C:\OracleBI\jdbc or {OracleBI}\jdbc.

  2. gabrielsan98 said

    por favor necesito ayuda, necesito generar variaciones en oracle bi es decir sacar que tanto vario el ingreso de un mes a otro, el problema es que los meses los saco de una dimension tiempo que estoy utilizando y no se como hacer

  3. Chinnu said

    Hi,
    I got the result for first time i tried it . But now am getting error like :”java.sql.SQLException: No suitable driver” . I tried a lot to rectify it but am dint get the connection successfull.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: