Oracle BI EE 11g & ODI 11g - BI Knowledge Modules & SELECT_PHYSICAL
As mentioned in my blog post here, one of the new features of BI EE 11g is the ability to connect directly to the Physical Layer of the Repository using the SELECT_PHYSICAL property. This opens up quite a lot of integration possibilities. Let's look at how this property works and how it has been put to use by the new Oracle BI Knowledge Modules available now with ODI 11g.
There are 2 ways of querying the BI Server Physical layer directly. They are
1. Use the SELECT_PHYSICAL sql keyword instead of the normal SELECT statement. For example, from the Advanced Issue Logical Sql page in Presentation services, we can fire a sql as given below
SELECT_PHYSICAL
FIRST_NAME,
LAST_NAME,
EMAIL,
EMPLOYEE_ID
FROM "ORCL"."HR"."EMPLOYEES"
And my physical layer in the repository is shown below.
So in the above SQL query, we have referenced the EMPLOYEES table which is in the physical layer. This will be a lot of useful in cases where we are doing reporting on real time ADF View Objects (will be available in the next BI EE patch set release). So, any external sql query engine can leverage the capability of doing real time ADF View Objects based reporting by using this BI Server feature.
2. In some cases, we do not want to explicitly specify the SELECT_PHYSICAL command. For example, some external client tools can fire only SELECT statements and there is no way to customize them. So, for such tools to leverage this feature, we can basically enable the SELECT_PHYSICAL property in the BI Server DSN. To demonstrate this, we start off with creating a new BI Server DSN called PhysicalBIODBC as shown below
While creating the DSN just enable the Route Requests to Physical Layer property. This will automatically make the DSN to fire SELECT statements against the physical layer.
I am a big fan of the Movie Inception (Dream within a Dream within a Dream)!!!. So, after watching the movie i have always wanted to know what will happen when we have 3 BI EE DSN's and then importing the metadata of one BI Server ODBC DSN to the other and then to the 3rd. The diagram below basically shows what i am trying to do
The Default DSN is the one that we get as part of the BI EE 11g install. The other 2 DSN's have the Route Requests to Physical Layer property set.
We start off with opening up the repository in online mode using the DSN2. Remember all of these are pointing to the same rpd.
We then use the import metadata to import from the DSN1 (through ODBC).
One of the first things you will notice is, since DSN1 has the Route Requests to Physical Layer enabled, we will be seeing the physical layer tables instead of the Presentation Layer subject areas.
I have imported the EMPLOYEES table through DSN1 into the DSN2. Lets save the repository. Now lets open up the default repository of BI EE in online mode and then import the EMPLOYEES table(imported from DSN1) through DSN2. This is possible now as the Route Requests to Physical Layer is enabled in DSN2 as well.
After the import is successful we will have the EMPLOYEES table now imported through 2 different DSN's.
Let's try to view the data now in online mode.
As expected, this throws an error. I would actually have been surprised if this worked. So far what i have done is purely to satisfy my curiosity. There is absolutely no practical use case of importing the same metadata again and again. But what this demonstrates is the ability to directly get at the Physical Layer without going through the BI Server subject areas.
Now that we have seen how the Physical Layer pass through works, lets look at another use case of connecting to the Physical Layer i.e. Using Oracle Data Integrator 11g to import the physical layer metadata of BI EE.
Oracle Data Integrator(ODI) 11g was released 3 months ago. One of the lesser advertised features of ODI 11g is the ability to reverse engineer and load data from Oracle BI EE 11g. In 10g, one had to go through workarounds like the one i had blogged about here and here to load data from BI EE. Also, in 10g, we could only load data from subject areas through Logical SQL. But in 11g, we can now directly import the BI EE metadata from the physical layer and use SELECT_PHYSICAL constructs to load data directly from the physical layer.
To understand how this works, we start off with defining a new Oracle BI Server in the ODI Topology as shown below.
Use the JDBC driver of BI EE 11g. The JDBC driver of BI EE is available at {Middleware_home}\Oracle_BI1\bifoundation\jdbc\lib directory. Ensure that you copy the bijdbc.jar from this directory to the Userlib directory of ODI (In ODI11g the drivers directory has changed - In windows the directory where the jar file needs to be copied is C:\Documents and Settings\Administrator\Application Data\odi\oracledi\userlib - replace Administrator with the username that you have used for logging in).
Test the connectivity. It should be successful. Since we are using the JDBC driver, we need to explicitly define the SELECTPHYSICAL property in order to make the driver to load the metadata from the Physical layer. To enable this, go to the Properties tab and then add Key-Value pair of NQ_SESSION.SELECTPHYSICAL=Yes as shown below
The above is very important for the connectivity to work. After defining this, we need to define a Catalog-Schema for the server in the topology. As we are connecting to the Physical layer of BI EE, we need to use the Physical Layer Catalog/Schema combination. For example, in my case, i have an EMPLOYEES table in the HR schema that i am trying to import into ODI (shown below).
So, the Catalog-Schema pair that we have to use will be ORCL-HR as shown below.
Lets now create a logical schema and then map it to the above physical schema within ODI.
The next step is to create a Project in the designer and then import the following Knowledge Modules.
1. RKM Oracle BI (jython)
2. LKM Oracle BI to Oracle (DBLINK)
3. LKM Oracle BI to SQL
3. IKM Oracle BI to SQL Append
As you see we now have pre-defined knowledge modules to extract data from Oracle BI and then load it into multiple targets. Lets now define a model and then reverse the metadata using the RKM Oracle BI (jython)
Ensure that you set a log file for RKM as shown below
The reverse process should go through successfully. We should now have 2 tables from our HR schema defined within the BI EE Physical layer as shown below. We can even look at the data directly from ODI.
From here on, its a matter of using ODI & the LKM/IKM knowledge modules to load the data from source to the target. A sample interface with an IKM is given below
The sample code used for actual execution of the interface is shown below. As you see, the knowledge modules automatically push SELECT_PHYSICAL sql queries in order to bypass the BI EE subject areas.
While using LKM, you can switch between different connection pools by adding a new flex field property OBIEE_CONNECTION_POOL in the Oracle BI Server (within the topology - Thanks to David Allan for this hint).