Manohar Paleti

My photo
Hyderabad, AP, India
Working as a BI Consultant, Served for various organizations as an OBIEE Developer by building the BI Solutions for Business Decision Making..

Wednesday, May 25, 2011

ODI and OBIEE 11g Integration



Copied from Oracle Site


Here we will see some of the connectivity options to OBIEE 11g using the JDBC driver. You’ll see based upon some connection properties how the physical or presentation layers can be utilized. In the integrators guide for OBIEE 11g you will find a brief statement indicating that there actually is a JDBC driver for OBIEE.


In OBIEE 11g its now possible to connect directly to the physical layer. In ODI 11g the Oracle BI technology is shipped with the product along with KMs for reverse engineering, and using OBIEE models for a data source.

When you install OBIEE in 11g a light weight demonstration application is preinstalled in the server, when you open this in the BI Administration tool we see the regular 3 panel view within the administration tool.

To interrogate this system via JDBC (just like ODI does using the KMs) need a couple of things; the JDBC driver from OBIEE 11g, a java client program and the credentials. In my java client program I want to connect to the OBIEE system, when I connect I can interrogate what the JDBC driver presents for the metadata. The metadata projected via the JDBC connection’s DatabaseMetadata  changes depending on whether the property NQ_SESSION.SELECTPHYSICAL is set when the java client connects.

Let’s use the sample app to illustrate. I have a java client program here that will print out the tables in the DatabaseMetadata, it will also output the catalog and schema. For example if I execute without any special JDBC properties as follows

•java -classpath .;%BIHOMEDIR%\clients\bijdbc.jar meta_jdbc oracle.bi.jdbc.AnaJdbcDriver jdbc:oraclebi://localhost:9703/ weblogic mypass


Then I get the following returned representing the presentation layer, the sample I used is XML, and has no schema;

Catalog                          Schema    Table


Sample Sales Lite              null        Base Facts

Sample Sales Lite               null       Calculated Facts

…        ...                    ...                  .....
Sample Targets Lite            null         Base Facts

Now if I execute with the only difference being the JDBC property NQ_SESSION.SELECTPHYSICAL with the value Yes, then I see a different set of values representing the physical layer in OBIEE;

•java -classpath .;%BIHOMEDIR%\clients\bijdbc.jar meta_jdbc oracle.bi.jdbc.AnaJdbcDriver jdbc:oraclebi://localhost:9703/ weblogic mypass NQ_SESSION.SELECTPHYSICAL=Yes

The following is returned;


Catalog                        Schema                 Table

Sample App Lite Data   null                    D01 Time Day Grain

Sample App Lite Data    null                   F10 Revenue Facts (Order grain)



System DB (Update me)




If this was a database system such as Oracle, the catalog value would be the OBIEE database name and the schema would be the Oracle database schema. Other systems which have real catalog structure such as SQLServer would use its catalog value.

Its this ‘Catalog’ and ‘Schema’ value that is important when integration OBIEE with ODI.

For the demonstration application in OBIEE 11g, the following illustration shows how the information from OBIEE is related via the JDBC driver through to ODI.



In the XML example above, within ODI’s physical schema definition on the right, we leave the schema blank since the XML data source has no schema. When I did this at first, I left the default value that ODI places in the Schema field since which was ‘’ (like image below) but this string is actually used in the RKM so ended up not finding any tables in this schema! Entering an empty string resolved this.


                                 

Below we see a regular Oracle database example that has the database, schema, physical table structure, and how this is defined in ODI.

Remember back to the physical versus presentation layer usage when we passed the special property, well to do this in ODI, the data server has a panel for properties where you can define key/value pairs. So if you want to select physical objects from the OBIEE server, then you must set this property.
An additional changed in ODI 11g is the OBIEE connection pool support, this has been implemented via a ‘Connection Pool’ flex field for the Oracle BI data server. So here you set the connection pool name from the OBIEE system that you specifically want to use and this is used by the Oracle BI to Oracle (DBLINK) LKM, so if you are using this you must set this flex field.

Thank You,


2 comments:

  1. I suggest you take this down, as you copied it directly from Oracle's blog site and posted it as your own. https://blogs.oracle.com/warehousebuilder/entry/odi_and_obiee_11g_integration

    ReplyDelete
  2. Dear Michael,

    Thanks for the suggestion , I have just saved all the stuff that I gathered into a place called " this Blog" .. what ever I think useful I just copied here .. I least bother for this.

    Thanks,
    MP

    ReplyDelete