Integrating ODI with OBIEE: How to invoke ODI scenarios & Load Plans from OBIEE

Introduction

In a data warehouse environment where the ETL/ELT tool is Oracle Data Integrator and the BI tool is OBIEE, it is common to find the need for BI users to execute ODI scenarios and ODI load plans within OBIEE via the BI EE Action Framework.  OBIEE administrators and BI users with special privileges may want to launch ad hoc jobs to process data for a specific BI task or business activity.  In most cases, these jobs are already implemented as ODI scenarios and ODI load plans, and they can be accessed and executed via ODI web services.  In OBIEE, you can invoke web services, or configure OBIEE Actions in conjunction with web services.  

This article explains how you can invoke ODI web services from OBIEE to execute your ODI scenarios and ODI load plans.  This article will show you how to execute an ODI scenario from OBIEE using ODI Web Services.  The same steps can be followed to execute ODI load plans.

 

Advantages of invoking ODI web services from OBIEE

 

There are many benefits in exposing ODI web services to OBIEE.  Here are some advantages:

 

  • In a BI environment where the presentation tool is OBIEE, BI users are already familiar with the OBIEE interface.  No training is required for users to learn how to launch ODI scenarios and load plans.
  • ODI administrators can pass the power of executing scenarios and load plans to BI users that prefer to use the OBIEE Actions Framework.
  • ODI administrators can focus on more complex data integration activities, and BI users can execute their ad hoc jobs at any time.
  • For the BI user, the OBIEE Action is transparent; they don’t need to know that web services are being used to execute their ad hoc jobs.
  • OBIEE roles and user groups can be configured to secure the execution of specific OBIEE Actions that call ODI web services.
  • In OBIEE, the Oracle Credential Store can also be used to configure who and when an ODI web service can be executed from the BI Presentation Services.
  • The ODIInvoke and ODIInvokeCallBack web services can be used to manage many types of operations such as start, stop, restart, and check the status of a scenario or load plan.
  • In OBIEE, the parameters of the ODI scenarios and load plans can be customized with friendly labels and names (when creating an OBIEE Action), so BI users can easily understand what they need to specify when calling an ODI web service via an OBIEE Action.
  • BI Administrators can create and configure OBIEE Actions and hide some of the ODI parameters from the BI user, so the user can only execute the action, but she or he can not see the ODI security parameters such as ODI user, and ODI password.

How to Invoke ODI Scenarios & Load Plans from OBIEE?

Configuring ODI Java EE Agent for OBIEE

 

This article assumes that you have already deployed and configured the ODI Agent as a Java EE application.  If you need instructions on how deploy and configure the ODI Agent as a Java EE application, please see Deploying and Configuring the ODI Agent as a Java EE Application before you continue any further.

The first step to integrate your ODI web services with OBIEE is to modify the ODI EAR (Enterprise ARchive) file that comes with the ODI Java EE agent deployment files.  

The name of the EAR file is oraclediagent.ear, and it is typically located by default in the following location:

 

<Oracle Middleware Home>\[ODI Domain Name]\setup\manual\oracledi-agent

 

Open the EAR file with a tool that can read EAR files (i.e. Winzip) as shown in Figure 1.

 

oraclediagent.ear

Figure 1: ODI EAR file (oraclediagent.ear)

 

At the root level of the ear file, locate a WAR (Web application ARchive) file called oraclediagent.war.  Select and open the WAR file as shown in Figure 2.

 

oraclediagent-war

Figure 2: ODI WAR file (oraclediagent.war)

 

At the root level of the WAR file, locate and select the WSIL (Web Services Inspection Language) file called OdiInvoke.wsil (see Figure 3).  Open the WSIL file with a text editor (i.e. Notepad).

 

odiInvoke.wsil

Figure 3: ODI WSIL file (OdiInvoke.wsil)

 

Add a new web service called “ODI Web Services”, and specify the name of the service (ODIInvoke) and its WSDL (XML document that describes the service).   ODIInvoke is the ODI web service that can be used by other applications such as OBIEE to invoke ODI scenarios and ODI load plans.

If the WSIL file has never been modified, replace the content of the WSIL file with the following XML code (adjust the service name, URL location, and port number based on your requirements):

 

<?xml version = ’1.0′ encoding = ‘UTF-8′?>
<!–Generated by Oracle BI Services–>
<inspection xmlns=”http://schemas.xmlsoap.org/ws/2001/10/inspection/”>
<service>
<abstract>ODI Web Services</abstract>
<name>ODI Web Services</name>
<description referencedNamespace=”http://schemas.xmlsoap.org/wsdl/” location=”http://localhost:8001/oraclediagent/OdiInvoke?WSDL”/>
</service>
</inspection>

Save your changes in all 3 files: WSIL, WAR, and EAR file.

 

The next step is to redeploy your ODI Java EE agent.  In Weblogic, launch the Weblogic Console and login as an Administrator.  Locate your oraclediagent deployment, and proceed to update it with the new version of your oraclediagent.ear file as shown in Figure 4.

 

updateODIAgent

Figure 4: oraclediagent.ear Re-deployment

 

Select “Finish” in the Update Application Assistant screen.  If you successfully updated the oraclediagent with the new EAR file, you should see a confirmation message as shown in Figure 5.

 

DeployedChangesODIAgent

Figure 5: Weblogic confirmation

 

The next step is to test your new WSIL.  Launch a browser, and type the URL of your WSIL (http://localhost:8001/oraclediagent/OdiInvoke.wsil).  If your WSIL was successfully configured, you should see the following XML code as shown in Figure 6:

 

ODIInvoke.wsil-test

 Figure 6: URL Test of ODI WSIL

 

The next step is to validate and test your WSDL document.  Launch a browser, and type the URL of your WSDL (http://localhost:8001/oraclediagent/OdiInvoke?WSDL).   If your WSDL was successfully configured, you should see the following XML code as shown in Figure 7:

 

ODIInvoke-wsdl

Figure 7: URL Test of OdiInvoke WSDL

 

At this point, no additional configuration is needed in ODI.  The next steps are going to demonstrate how you configure OBIEE to call the ODI web services.

 

Registering ODI Web Services in OBIEE

 

The Action Framework of OBIEE 11g is a component of the Oracle BI EE architecture that allows us to invoke web services that are deployed in other application servers.  These services can be configured in OBIEE as Action Web Services, and they can be used within the Oracle BI Presentation Services.  For more information about OBIEE Actions Framework, please see Using Actions to Integrate Oracle BI EE with External Systems.

Our discussion will be limited to what configuration is needed to enable and execute ODI web services from OBIEE.  However, we strongly recommend that you secure your OBIEE actions.  For more information, please see Overview of Action Security.

The first step is to register the ODI Web Services in the OBIEE Action Framework configuration file called ActionFrameworkConfig.xml.  This configuration file is typically located by default in the following location:

 

<Oracle Middleware Home>\user_projects\domains\bifoundation_domain\config\fmwconfig\biinstances\coreapplication

 

In this file, we are going to add a new WSIL entry to specify the name and location of the ODI WSIL.  OBIEE will use the WSIL to locate the WSDL, and retrieve available services based on the WSDL document.  For more information on how to configure this file, please see Configuring the OBIEE Action Framework.  Make a backup of this file before making any modifications.

Modify the ActionFrameworkConfig.xml file by adding a new WSIL registry (under the <registries> section) for ODI Web Services as follow (change the name of the WSIL registry, and the path of the WSIL based on your requirements):

 

<registry>
<id>ETLWS</id>
<name>ETL Web Services</name>
<content-type>webservices</content-type>
<provider-class>oracle.bi.action.registry.wsil.WSILRegistry</provider-class>
<description></description>
<location>
<path>http://localhost:8001/oraclediagent/OdiInvoke.wsil</path>
</location>
</registry>

Save your changes.

Restart your OBIEE server, and verify that the ActionFrameworkConfig.xml file has been loaded successfully in the OBIEE server as shown in Figure 8.

 

obiee-registry

Figure 8: OBIEE ActionFrameworkConfig.xml successfully loaded.

 

Invoking ODI Web Services in OBIEE

Once your ODI Web Services has been configured with OBIEE Action Framework, BI users can login into OBIEE Presentation Services and access the ODI web services by creating a new OBIEE Action of type “Invoke A Web Service” as shown in Figure 9.

 

obiee-new-action

Figure 9: Creating an OBIEE Action

 

Create a new OBIEE Action of Type “Invoke A Web Service”.  The new ODI web services will appear in the “Select Web Service Operation” screen of OBIEE as shown in Figure 10.

 

obiee-odi-webservice 

Figure 10: ODI Web Services in OBIEE

 

Select an ODI operation such as InvokeStartScen, and customize the web service screen as shown in Figure 11.

 

ActionConfiguration

Figure 11: Defining and customizing parameters for ODI InvokeStartScen

 

In my example above, I created an Action to run an ODI scenario called “PAYROLL”.  This scenario is an ODI package that BI users like to invoke once a month.  This Action will help us automate this task, so BI users can invoke this scenario when they are ready to process their monthly payroll.

In the “Edit Action” screen above, I modified the following parameters:

  • ODI Prompts:  I replaced “ODI User” with “Payroll User”, “Scenario Name” with “Payroll Job Name”, and “Value” with “Enter Payroll Month”
  • ODI Values:  I left User and Password blank, so the user will enter these values when he or she runs the Action.  Also, I provided a syntax for parameter “Enter Payroll Month”:  “YYYY-MM”.
  • Other attributes:  I defined some of the values as Fixed or Hidden because  BI users should not worry about ODI Repository Names, Scenario Versions, Context, etc.
  • Options:  I selected “Options”  to customize “Dialog Title”, “Action Help”, and the “Execute Button Text” as shown in Figure 12.

ActionOptions

Figure 12: Customizing Options for an OBIEE Action

 

Save your new OBIEE Action.  

 

Now that my OBIEE Action has been fully configured, I decided to create a dashboard in OBIEE that allows users to execute the Action (the ODI scenario called “PAYROLL”), and check the status of the scenario, all in one screen.  To do this, I had to bring two ODI tables, SNP_SESSION and SNP_USER, into OBIEE.  I modified the OBIEE RPD to model these two tables as shown in Figure 13.

 

OBIEE-RPD-ODI-SNP 

Figure 13: OBIEE RPD with ODI tables

 

For more information on how to integrate OBIEE with ODI metadata to build report-to-source data lineage, please see Oracle Business Intelligence Enterprise Edition Data Lineage For ODI 11g.

 

Finally, I created an OBIEE dashboard that includes 4 main areas:  (1) an option to execute the Action, (2) when the action is invoked, it will prompt the user for necessary parameters, (3) an option to filter the ODI user(s), and (4) a table that shows the status of the ODI executions.  Figure 14 illustrates my final dashboard.

 

OBIEE-ODI-Dashboard

Figure 14: OBIEE Dashboard that invokes ODI scenarios

 

In ODI, I can see the executions of the OBIEE Actions in the ODI Operator as shown in Figure 15:

 

ODI-Logs

Figure 14: ODI Operator

 

Conclusion

ODI Web Services is a great mechanism to execute ODI scenarios and load plans from other enterprise applications such as OBIEE.  Now you can configure ODI and OBIEE to invoke the most complex ODI scenario with the click of a button!

For more ODI best practices, tips, tricks, and guidance that the A-Team members gain from real-world experiences working with customers and partners, visit Oracle A-Team Chronicles for ODI.

 

Add Your Comment