Invoking Web Services from OBIEE

This blog explains on how to invoke Web services from OBIEE 11g using Actions.

 

Requirements

OBIEE 11g installed and running

Sample app for OBIEE 11g

SOAP UI

Note: You need internet access for the machine on which you have installed OBIEE 11g.

 

Web Service

A Web service is a Web resource which may be invoked to get some information. There are 2 different implementations of Web services

1. SOAP : Simple Object Access Protocol

2. REST: Representational State Transfer.

Today we are going to see a demo based on SOAP based Web services. SOAP based Web services are described in an xml format know as Web Service Description Language (WSDL). WSDL specifies what operations that web service contain, inputs and outputs for each operation, where the service is located etc.

The Web service that I am going to use for demo is called “zip code web service”. This service has an operation which takes zip code as input parameter and gives the city name, state name, time zone and phone area code etc. as output.

The web service is located at http://www.webservicex.net/uszip.asmx

The WSDL is located at http://www.webservicex.net/uszip.asmx?wsdl

Note: Please note that I am no way related to this Web Service.

Understanding the Web service

Let see how Web services work.

1. Download the SOAP UI Standalone version and unzip it.

2. Go to bin folder and double click on soapui.bat for windows or soapui.sh for Linux

3. It opens a GUI window as shown below



4. Click on the File Menu and select New soapUI Project as show below



5. It should open a New soapUI Project window. Give a name to the project (I named it as Test) and in the Initial WSDL/WADL field enter the “http://www.webservicex.net/uszip.asmx?wsdl”, leave the default settings and Click OK. See the image below



6. SOAP UI creates the necessary stubs and you should see a new project (Test in my case) with list of services and operations as shown below



7. Expand the USZipSoap12, expand GetInfoByZip operation and double click on the Request1. You should see a new split window. The left side represents the input message for the operation and right side represents output for the operation. Notice that on left hand side, you should see that <web:GetInfoByZip> tag which is just below the Body tag, this indicates that you are about to invoke GetInfoByZip operation. You should see that “web:USZip” under operation tag, this is the parameter that needs to be passed before executing the operation. Replace the “?” from the highlighted section (in the image below) with some zip code and click on run or green play button located on the top. You need internet for this step to work



8. You can see that the output side is filled with data. I have entered 60563 and I got back city as Naperville, state as IL, time zone as C, area code as 60563



9. In OBIEE, you get this information using XPath. For e.g.,

a. To get the XPath for City, you could use //CITY (since there is only city). I would suggest you to use //GetInfoByZIPResult//Table/CITY.

b. Similarly, I would use

i. //GetInfoByZIPResult//Table/STATE for State

ii. //GetInfoByZIPResult//Table/TIME_ZONE for Time Zone

iii. //GetInfoByZIPResult//Table/AREA_CODE for Area Code

Note: Please remember these XPath expressions as I am going to use them in the next section.

Create Action:

1. Let us create an action first, Log in to OBIEE 11g and go to New > Action

2. It pops up a dialog, select Invoke a Web Service link



3. You should see “Select Web Service Operation” window, in the WSDL URL field enter the

http://www.webservicex.net/uszip.asmx?wsdl and click Open button



4. Expand the USZip > USZipSoap12 and select the operation GetInfoByZip and click OK



5. It opens New Action window as shown below. Notice that it defines only one parameter as our Web service operation takes only one parameter as input. Click on the “Options…” button located next to Help button



6. In the Invoke Action tab, check the Dialog Title box and enter “Get Geographic Information for Zip” in the field besides it



7. In the Action Results Tab,

a. Enter “The Geographic information is” in the Dialog Title field

b. Enter following in the Dialog Text area

The City is @{1}
The State is @{2}
The Time Zone is @{3}
The Area Code is @{4}

c. In the XPath Results section, add 4 Names as (use green color plus for adding additional parameters)

i. 1 as //GetInfoByZIPResult//Table/CITY

ii. 2 as //GetInfoByZIPResult//Table/STATE

iii. 3 as //GetInfoByZIPResult//Table/TIME_ZONE

iv. 4 as //GetInfoByZIPResult//Table/AREA_CODE



8. Click OK and Save Action button.

9. This finishes the action part, let us to go and create an analysis which uses this action.

Note: I can use an action link to give a demo but I thought it is better to create a demo with analysis.

Create Analysis

I have the sample app for OBIEE 11g installed and I am going to use it for the demo

1. Create an analysis from the Sample Sales subject area with Postal Code (Customer), Product, Revenue and Country(Customer) as columns



2. Since the Zip code Web service is going to work only for United States, add a filter on Country with value equal to United States



3. On the results tab, Let us exclude Country from the display



4. On the Analysis tab, open the column properties of the Postal Code Column



5. Go to Interaction Tab of the column properties and select Primary Interaction for Value as Action Links



6. Add action link by clicking the green plus symbol

a. Enter the link text as Get Geographic Info

b. Select existing action button

c. Navigate to the place where you have saved the action (mentioned in previous section, I saved it as ZipToGeoAction), click on the action and click OK button.

d. You should see a pop up “Edit Parameter Mapping” window. In the value column, click this button to expand it and select “Column Value” option



7. You should see that values are populated as list of columns present in your analysis. Select Postal code as value (see the image below). By doing this, you are saying that pass the value of Postal code as a parameter to the web service.



8. Click OK to close Edit Parameter Mapping window, OK to close Edit Action Link window and OK to close Column Properties.





9. Go to Results tab and click on any value in postal code column, it should pop a menu as Get Geographic Info



10. Click on the Get Geographic Info menu, you should see the following window



11. Click on Execute button and you should see the following window



12. Note: it might take little time as it going through the internet to get this information.

If you have any questions or comments, please drop a note below.