Data Management,

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

August 31, 2013 | 14 minute read

Text Size 100%:

－＋

Disclaimer

The content of this article pertains to on premise technology and was last validated in January 2013. It is still made available for its educational value.

Introduction

This article demonstrates how to configure Oracle Business Intelligence Enterprise Edition (OBIEE) to invoke Oracle Data Integrator (ODI) web services. In a data warehouse environment where the ETL/ELT tool is ODI and the business intelligence tool is OBIEE, it is common to find the need for BI users to execute ODI scenarios and ODI load plans from OBIEE. 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 in ODI as ODI scenarios or ODI load plans, and they can be accessed and executed via ODI Web Services. In OBIEE, you can invoke the ODI web services via the OBIEE Action Framework.

The first part of this article illustrates how to integrate the ODI Web Services with the OBIEE Action Framework. Then, it illustrates how to create an OBIEE Action to invoke an ODI scenario. Also, it illustrates how to customize the OBIEE Action to add additional ODI variables and ODI parameters to the Action form. The example can be extended to other ODI web services such as invoking ODI load plans.

Advantages of invoking ODI web services from OBIEE

There are many advantages 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 user 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 ODI scenarios and ODI 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 BI users, the OBIEE Action Framework 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 OBIEE Actions.
The Oracle Credential Store can also be used to configure who can execute an ODI web service. Although this article does not cover how to configure the Oracle Credential Store for ODI and OBIEE, it is highly recommended to secure your web services.
ODI web services can be used in OBIEE to manage ODI run-time operations such as start, stop, restart, and check the status of an ODI scenario or ODI 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. ODI security parameters such as ODI user, and ODI password can be hidden from BI users.

Configuring the ODI Java EE Agent for OBIEE

This article assumes that you 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, go to “Installing and Configuring Oracle Data Integrator” before you continue any further.

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

In ODI 12c, the agent EAR file is called oraclediagent-wls.ear, and it is located in the following folder:

<Fusion Middleware Home>\odi\jee\oracledi-agent

In ODI 11g, the agent EAR file is called oraclediagent.ear, and it is located in the following folder:

<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 below:

Figure 1 - Opening the ODI EAR File for Modification

At the root level of the ear file, locate and open a web application archive (WAR) file called oraclediagent.war, as shown on Figure 2 below:

Figure 2 - Opening the ODI WAR File for Modification

At the root level of the WAR file, locate the web services inspection language (WSIL) file called OdiInvoke.wsil, as shown on Figure 3 below:

Figure 3 - Opening the ODI WSIL File for Modification

Select the WSIL file and open it with a text editor. Add a new web service called ODI Web Services, and specify the Web Services Description Language (WSDL) URL of the ODI web services. The WSDL URL is used by web applications such as OBIEE to see what web services are available and how to invoke them. ODIInvoke is the ODI web service that can be used by other web applications to invoke ODI scenarios and ODI load plans. Figure 4 below shows an example of how to modify the ODI WSIL file. Adjust the service name, URL location, and port number.


<?xml version = "1.0" encoding = "UTF-8"?> <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>

Figure 4 - OdiInvoke.wsil Example

Save the three files: the OdiInvoke.wsil, the ODI WAR file, and the ODI EAR file. The next step is to update your ODI Java EE agent. In Weblogic, launch the Weblogic Console and login as the Weblogic administrator. Locate your oraclediagent deployment, and proceed to update it with the new version of the oraclediagent.ear file, as shown in Figure 5 below.

Figure 5 - Redeployment & Update of the Oraclediagent

Once the oraclediagent deployment has been updated, ensure that no restarts are necessary. Test your new WSIL file by launching a web browser, and typing the URL of the WSIL file. As an example, enter the following URL:

http://localhost:8001/oraclediagent/OdiInvoke.wsil

If your WSIL was successfully configured, you should see the following XML code as shown in Figure 6 below:

Figure 6 - URL Test of the ODI WSIL File

The next step is to validate and test the WSDL document. Launch a web browser, and type the URL of the WSDL document. As an example, enter the following URL:

http://localhost:8001/oraclediagent/OdiInvoke?WSDL

If your WSDL was successfully configured, you should see XML code, as shown in Figure 7 below:

Figure 7 - URL Test of the ODI WSDL Document

This concludes the configuration that is required in ODI. The next steps demonstrate how to 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 users to invoke web services from other application servers. These web 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, go to “Using Actions to Integrate Oracle BI EE with External Systems”.

In this section, the discussion will be limited to what configuration is needed in order to execute ODI web services from OBIEE. Additional configuration is required in order to secure the OBIEE Actions. For more information on how to secure OBIEE Actions, go to “Overview of Action Security”.

To register the ODI Web Services with OBIEE, a configuration file called ActionFrameworkConfig.xml must be manually edited. This configuration file is located in the following OBIEE folder:

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

Make a backup of the ActionFrameworkConfig.xml file before making any modifications. Locate and open the ActionFrameworkConfig.xml file to add an entry for the ODI WSIL. OBIEE will use the ODI WSIL to locate the ODI WSDL, and retrieve the available ODI web services based on the WSDL document. For more information on how to configure the ActionFrameworkConfig.xml file, go to “Configuring the OBIEE Action Framework”.

Modify the ActionFrameworkConfig.xml file by adding a new <registry> node for the ODI Web Services as illustrated on Figure 8 below. The new <registry> node must be added under the <registries> node. Change the name of the WSIL registry, and the path of the WSIL based on your requirements. For an example of how to modify the ActionFrameworkConfig.xml file, go to “Sample Action Framework Configuration File.”


<registries> <registry> <id>odiws</id> <name>ETL Web Services</name> <provider-class>oracle.bi.action.registry.wsil.WSILRegistry</provider-class> <content-type> <typename>webservices</typename> <displayname>ODI Web Services</displayname> <actionType>WebServiceActionType</actionType> </content-type> <description/> <location> <path>http://localhost:8001/oraclediagent/OdiInvoke.wsil</path> </location> </registry> </registries>

Figure 8 - Sample Action Framework Registry Entry for ODI Web Services

Save your changes, and restart the OBIEE server. Verify that the ActionFrameworkConfig.xml file has been loaded successfully in the OBIEE server as shown on Figure 9 below.

Figure 9 - Reloading the OBIEE Action Framework Configuration File

Invoking ODI Web Services in OBIEE

Once your ODI Web Services has been configured with OBIEE Action Framework, BI users can login into the 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 10 below:

Figure 10 - Creating a new OBIEE Action

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

Figure 11 - Selecting the ODI Web Service Operations

Select an ODI web service operation such as InvokeStartScen, and customize the web service screen as shown on Figure 12 below.

Figure 12 - Defining & Customizing the OBIEE Action

Figure 12 above shows an example of how to configure an Action to invoke an ODI scenario called ODI_PAYROLL. In this example, this ODI scenario is an ODI package that BI users can invoke to process payroll information. For this Action, the Session Name has been set to ODI Monthly Payroll Job. The prompt for the ODI variable has been changed to Variable for Payroll Month, and the actual ODI variable name has been set to OBIEE_WS_DEMO.V_MONTH. The prompt for the ODI variable value has been changed to Enter Payroll Month, and the value has been set to YYYY-MM.

Most of the parameters have been set to fixed or hidden, since BI users do not need to know the ODI Repository Name, Scenario Version, Context, and other ODI parameters. Notice the prompt for the ODI variable value – it is not hidden; thus, the prompt, Enter Payroll Month, will be displayed when the user executes the Action.

Select Options to customize the Dialog Title, Action Help, and the Execute Button Text as shown in Figure 13 below.

Figure 13 - Customizing the OBIEE Action Options

Select OK, and save the new Action. Execute the new OBIEE Action and enter the necessary parameters as illustrated on Figure 14 below. The new Action is called ODI Monthly Payroll Job.

Figure 14 - Executing the OBIEE Action

How to Add ODI Variables in the OBIEE Action Forms for ODI Web Service Calls

NOTE: The following section was added on August 22, 2016 after many questions on how to modify the OBIEE Action form to include additional ODI variables. This section answers these questions.

When OBIEE reads the WSDL document from the ODI Web Services, the OBIEE Action form gets populated with only one ODI variable. In most cases, ODI scenarios are designed to use more than one ODI variable; thus, the Action form must be customized to add additional ODI variables.

Use the OBIEE Catalog Manager to modify the XML file of the Action form and add additional ODI variables. The Catalog Manager is an OBIEE client application that allows administrators to manage the OBIEE catalog files.

Login to the Catalog Manager as an administrator, and locate the Action file. Select the Action file and open the file properties. Figure 15 below shows how to locate the file properties of an Action form called ODI Payroll Monthly Job. By default, this Action form was created with one single ODI variable. In this example, two additional ODI variables will be added to the form. To add these two new ODI variables, the XML object of the form will be modified. Once the XML object is modified, users can open the Action form and configure a total of 3 ODI variables.

Figure 15 - Using the Catalog Manager to Modify the OBIEE Action

Select Edit XML to edit the XML document as shown on Figure 16 below. Select Edit again to enable changes on the XML object.

Figure 16 - Modifying the XML Document of the OBIEE Action

Select the entire XML document, and copy its content into a text editor. To add a new ODI variable in the Action form, two entries are required – the variable name and the variable value – on each of the following four sections of the XML document:

Action Form Parameters – This section defines the parameters of the Action form.
Action Form Assignments – This section assigns the actual values of the parameters in the Action form.
SOAP Envelope Stylesheet Parameters – This section defines the parameters of the SOAP envelope.
SOAP Envelope Variables –This section maps the Action form parameters with the SOAP envelope parameters.

Figure 17 below shows how to add two new ODI variables in the parameters section of the XML document. In this example, four new parameters have been added: two parameters for the variable names, and two parameters for the variable values.

Figure 17 - Modifying the XML Document of the OBIEE Action - Parameters

In the Action form, each new parameter must be displayed in the correct order; thus, a new order has been assigned to each new parameter as shown on Figure 17 above. Other parameters such as LogLevel, Debug, SuspendOnFirstStep, DebugDescendants, and BreakOnError – have been reordered as well, so they show after the new Action form parameters (the new ODI variables).

Figure 18 below shows the assignments section of the XML document – four new assignments have been added: two assignments for the variable names, and two assignments for the variable values. In this section, the actual variable name and the variable value are defined as well.

Figure 18 - Modifying the XML Document of the OBIEE Action - Assignments

Figure 19 below shows the stylesheet section of the XML document. In this example, four stylesheet parameters have been added for the two new ODI variables.

Figure 19 - Modifying the XML Document of the OBIEE Action – Stylesheet

Figure 20 below shows the Variables section of the SOAP envelope for the XML document. Two new variables have been added. Each variable contains a name-value pair entry.

Figure 20 - Modifying the XML Document of the OBIEE Action – Variables

Save the new version of the XML document in a XML file. Open the XML file in a web browser and review the changes before updating the XML object in the Catalog Manager. Return to the Catalog Manager, and replace the old XML object with the new XML object, and save the changes.

Return to OBIEE, and open the Action form. The new parameters, the ODI variables, should be displayed on the form, as shown on Figure 21 below. In this example, a total of three ODI variables are displayed on the Action form.

Figure 21 - Example of Three ODI Variables in the Action Form

When the new Action form is executed, users can enter values for these three variables, as shown on Figure 22 below:

Figure 22 - Example of Three ODI Variables in the Action Form

Use the ODI Operator to see the executions of the OBIEE Actions as shown in Figure 23 below:

Figure 23 - Viewing the OBIEE Actions in the ODI Operator

Conclusion

ODI Web Services is a great mechanism to execute ODI scenarios and load plans from other enterprise applications such as OBIEE. 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”.