BI EE 11.1.1.5 - Calling BI EE 11g Agents through Oracle Data Integrator 11g

I was asked recently to come out with a way of calling BI EE 11g agents directly from Oracle Data Integrator 11g. There are many ways of accomplishing this. I thought i will write a quick blog post about the various possible solutions and more importantly get back to blogging again!!!

Alerting end-users about a completed ETL task is a pretty common requirement. ODI 11g (or for that matter any ETL tool) has native routines to send emails directly from within the tool. But in the case of the specific customer case, they wanted to use BI EE 11g Agents for 3 main reasons

Use the features of BI EE - like along with the ETL task completion status they wanted a report or a set of reports to be distributed to the end users
They wanted dashboard based alerting for Administration users. Whenever an Admin user logs into the BI EE dashboard, they wanted the ability to pop-up a message denoting the completion status.
They have a bunch of BI EE reports built already on top of the ODI metadata which will capture information on number of failed records loaded etc (directly from the E$ and C$ tables).

So, it was pretty clear that using BI EE 11g scheduler is the way to go. But how do we call BI EE agents from ODI. There is no native integration between the two (perhaps a candidate for an enhancement request as i think this will be a pretty common requirement once BI Apps using ODI becomes available for all the modules). So the only option is to build our own integration. There are basically 2 ways of achieving this

Use SASCHINVOKE utility available for calling Agents from command line and then call the SASCHINVOKE utility from within ODI - This requires an ODI Agent to available on the same box as BI EE. A quick overview of the solution is given below

2. Use BI EE Web Services and call ExecuteIbotNow method in the iBotService - This method will be called directly from ODI 11g. The high level architecture diagram is given below

Lets see each of the above methods in detail now. We will start off with the first method i.e. using SAWSCHED utility.

SASCHINVOKE & Native ODI Agents:

This method assumes that there is a physical remote ODI agent running on the same box as BI EE 11g. For this to work, we will have to call the SASCHINVOKE utility from within a ODI package. For example, lets say we have an iBot in BI EE that is supposed to deliver a report/dashboard after the ETL job gets completed. First we start off with creating a package in ODI and then use the ODIOSCommand to call the SASCHINVOKE command line utility as shown below

Very Important: For this to work you need to have SSL enabled. According to note 1331931.1 in MOS, the ability to pass passwords in command line is now disabled(for security reasons). So, we cannot use this option if we do not have SSL enabled.

As you see in the above screenshot, SASCHINVOKE will always prompt for a password. To bypass this, just add an ECHO statement and pipe the output to the SASCHINVOKE command as shown below.

BI EE Web Services & ODI Packages:

In many cases, the first method above will not work because we might not have SSL enabled or we might not prefer passing the username & password in command line. One more drawback with the first one is, we have to host a separate ODI agent on the BI EE Machine. Of course we can work around it by just installing BI EE client on the ODI Agent machine (which will have SASCHINVOKE in the binary - and it can do remove invocations). But the problem with that is in pure Unix environments, we do not have the option of installing BI EE Client on the ODI Agent machine(only option is to setup a SSH public-private key and enable remote invocation). In such cases, we will have to rely on the Web Service API calls of BI Scheduler. Lets try to quickly understand how that works.

ODI supports native Web Service calls. It can either extract data (Through a Data Service) or can execute a remote service method. But the problem with BI EE web services is the fact that they are multiple-end point in nature. That is, we will have to first make a call to the SAWSession web service which will create a SessionID. This sessionid then needs to be passed on to other services like iBotService. To do that we need to do 3 main things within the ODI framework

Using ODIInvokeWebService, make a call to the SAWSession service. This will give a XML response with the SessionID embedded in the XML.
Use a java class (or using ODIXMLSplit & ODIXMLConcat) to extract the SessionID from the response XML and then create a Request XML with the SessionID and the path of the iBot embedded.
Using ODIInvokeWebService, make a call to the executeiBotNow method of the iBotService endpoint using the request XML generated above.

So, we start off with creating an ODI Package. We then call the ODIInvokeWebService component to make a call to the SAWSession service.

On execution of this service, the output response file will get stored in a file with the SessionID.

The next step is to extract the SessionID from the SOAP response and then generate a Request XML as shown below

To do this, i have attached a zip file here that contains the code for extracting the sessionID and converting it into a SOAP Request. This code basically uses the open source DOM4J API. I prefer Java Code instead of using ODIXMLCONCAT & ODIXMLSPLIT as both of them seem to have some known bugs. The snippet of the main code i have written for this conversion is given below

The code accepts 3 parameters(basically the runodiagent.bat file). Input SOAP Response file from the first Web Service Call. BI EE iBot full path that we intend to call. And the output file that will be generated, which will in turn act as a Request XML to the next Web Service Call. All the parameters will be called through the ODIOSCommand component as shown below

Once this file is generated, then this file is passed as an input to the next ODIInvokeWebService call as shown below

The final package will look something like this

On execution we should start getting the alerts right within the Dashboard (or through emails - whichever is setup in the iBot) after the ETL-ODI execution