How to Invoke ODI Load Plan from OBIEE / OBIA 11g

Hello Guys.

I am going to breifly explain how to Invoke ODI Loan Plan from OBIEE / OBIA 11g with SCREENSHOTS.

Configuration:

Firstly you need to configure ODI Java EE Agent for OBIEE.

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

Figure1.

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.

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).

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.

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.

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:

In my case , localhost name is "biappseup.inboxbiz.com" and port is "15001"

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:

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.

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.

Click on "Invoke a Web Service" in Figure 10.

Continue in Figure 11.

Continue in Figure 12.

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

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 14.

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 15.

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 16 illustrates my final dashboard.

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

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, visit “Oracle Tuition”.