Web Services Communication Using Informatica

Introduction

In today’s fast paced world, Organizations have started using different types of software systems to compete and to be up to the speed of the world. This eventually increases the need for communication between different software systems and the same is growing by leaps and bounds. And with the Current data warehousing and data analytics world being more dependent on the ETL tool, a connection between ETL and the software systems, is the key and it is more paramount to fulfill the business requirements and their needs.

So, as a part of this blog, we would like to demonstrate the importance of web services and its method of communication that allows two software systems to exchange data over the internet using Informatica (ETL) tool.

What are web services

Web service is a way of communication that allows inter-operability between different applications on different platforms, for example, a java based application on Windows can communicate with a .Net based one on Linux. The communication can be done through a set of XML messages over HTTP protocol.

Web services are browsers and operating system independent service, which means it can run on any browser without the need of making any changes. Web Services take Web-applications to the Next Level.

The World Wide Web Consortium (W3C) has defined the web services. According to W3C, “Web Services are the message-based design frequently found on the Web and in enterprise software. The Web of Services is based on technologies such as HTTP, XML, SOAP, WSDL, SPARQL, and others.”
Let’s say, you are a java developer and you can publish your functions on internet or LAN through java web service so any other developer (let’s say .Net developer) can access your function.

Specifications of Web services

Web services can be accessed using different methods/styles. The definitions for some of the important methods/styles that acts as an interface with web services are mentioned below.

SOAP

SOAP was originally part of the specification that included the Web Services Description Language (WSDL) and Universal Description, Discovery, and Integration (UDDI). It is used now without WSDL and UDDI. Instead of the discovery process described in the History of the Web Services Specification section below, SOAP messages are hard-coded or generated without the use of a repository. The interaction is illustrated in the figure below.

 

Representation State Transfer (REST)

Representation State Transfer (REST) is an architectural style for communicating with web services. REST can use SOAP web services because it is a concept and can use any protocol like HTTP, SOAP. It also less verbose so that less volume is sent when communicating. The interaction is illustrated in the figure below.

 

 

JavaScript Object Notation (JSON)

While both SOAP and REST use XML for interchange, JavaScript Object Notation (JSON) uses a subset of JavaScript. This is illustrated in the figure below.

For illustration purpose, we will be using a SOAP based web service.

Web Service Call in Informatica

Now let’s find more on implementing the web service call in informatica in detail. The sample web service taken up for the purpose of demonstrating web service call is Medicare supplier web service. It provides names, addresses, and contact information for suppliers that provide services or products under the Medicare program. A supplier is an individual or entity that has agreed to provide health care equipment, items, or services under Medicare. A supplier may provide items such as: Durable Medical Equipment (wheelchairs, walkers, oxygen), Prosthetic (artificial limb replacements or dentures) or Orthodontic (mechanical devices used to assist in mobility or supplement the joints and limbs). In addition, a type of supplier can also be Pharmacy/Drug Stores and Optometry/Opticians. This Supplier Directory lists Medicare enrolled pharmacies and suppliers.

The first step in web service call implementation is validating whether the Web service is up and running. Now let’s find more about it.

Validation of Web service

Pre-requisites

• WSDL file for the web service
• SOAP request and response.
• SOAP UI 5.2.1

Step by Step Process

Step 1: Create an WSDL file for the respective web service. In this case, for the Medicare supplier. The WSDL can be fetched from the web service URL.

Once the WSDL code is copied, save it with the suffix wsdl.

Step 2: The Next step is to use SOAP UI for validating the web service. Open the SOAP UI.

Since we are performing an SOAP based web service call, a new SOAP project is created.

While creating a new SOAP project, a pop-up asks for the WSDL file. Now browse for the WSDL file and select it.

Once the WSDL file is selected, Click OK in the pop-up.

Step 3: Once the SOAP project is created in SOAP UI, a separate folder will be created under projects with the title Medi_Care_Supplier.

Under this folder, three options are available. These options depict the operations performed in the web service. Medicare supplier information can be fetched based

• on city name
• on supplier type description
• on zip code

Now open the operationGetSupplierByCity. Under this operation, SOAP Request 1 will be available.

Double click the SOAP Request 1. Once done, the corresponding SOAP Request code will be displayed in the right hand side window.

Now paste the SOAP Request, that is available in the Web service URL, in the right hand side window.

Now the same process is repeated for the operations GetSupplierBySupplyType and GetSupplierbyZipCode.

Step 4: Now run the SOAP request for all the three operations of this web service. The highlighted arrow symbol depicts the run button.

The corresponding SOAP response will be displayed in the window next to the SOAP Request. If we receive the SOAP response for the requests, then the web service is up and running.

Implementation in Informatica

In order to demonstrate the web service call in informatica, let’s take up a healthcare related web service for fetching the Medicare supplier information. As mentioned earlier, in this web service, three operations can be performed. Medicare supplier information can be fetched based

• on city name
• on supplier type description
• on zip code

Let’s see how the three operations can be performed via Informatica in a step by step process.

STEP BY STEP IMPLEMENTATION

Step 1: Create the necessary source and target tables in the database end. For our illustration, I have created source and target tables with the below metadata.

Source table – MEDICARE_SUPPLIER

• SUPPLIER_CITY_NAME varchar (50) NULL,
• SUPPLIER_TYPE varchar (50) NULL,
• SUPPLIER_ZIP_CD varchar (20) NULL

Target table – MEDICARE_SUPPLIER_DETAILS

• SupplierNumber varchar (20) NULL,
• CompanyName varchar (50) NULL,
• Address1 varchar (50) NULL,
• Address2 varchar (20) NULL,
• City varchar (20) NULL,
• Zip varchar (15) NULL,
• ZipPlus4 varchar (10) NULL,
• Telephone varchar (20) NULL,
• Tele_Description varchar (30) NULL,
• IsSupplierParticipating varchar (5) NULL

Step 2: Once the source and target tables are ready, import them as source and target definitions in Informatica Designer.

Step 3: For our example, the mapping created is m_Medi_Care_Supplier. Drag the source and target definitions as given below.

Next create the expression transformation. Here, we will provide the SOAP request as one of the input port. For each input port, a corresponding SOAP request port (output) is also created.

For each output port, the SOAP Request code needs to be typed in the expression editor pop-up. For the output port oSUPPLIER_CITY_NAME, the SOAP Request for the operation GetSupplierByCity is typed in. In the same way, for the rest of the output ports, their corresponding SOAP Requests are typed in.

Step 4: For performing every operation, a separate HTTP transformation needs to be created. As a result, in total, 3 HTTP transformations are created.

Rename each transformation, for the type of operation they perform.

Step 5: Open the first HTTP transformation for the operation GetSupplierByCity. All the details under the HTTP tab has to be filled in. The first step is to create the below ports.

• SOAPRequest – Input group
• ContentType – Header group
• SOAPAction – Header group

The HTTP method has to be selected to SIMPLE POST.

Once done, the base URL has to be provided for the HTTP transformation.

The base URL for our Medicare Supplier example is given below.

http://www.webservicex.net/medicareSupplier.asmx

Step 6: The Next step is to fill in the Default value as well as the HTTP Name for the Header ports. Select the port ContentType and fill the details as such.

Default Value: ‘text/xml’

HTTP Name: Content-Type

The same procedure is repeated for the port SOAPAction as well.

Default Value: ‘http://www.webservicex.net/GetSupplierByCity’

HTTP Name: SOAPAction

Step 7: The Step 5 and 6 are repeated for the other two HTTP transformation. The only difference being, the default value changes depending on the operation for SOAPAction Header port.

Operation GetSupplierBySupplyType

Operation GetSupplierByZipCode

Step 8: After the HTTP transformations are complete, the next step is to process the HTTPOUT that will be in xml format. We can either send it as xml file else process the xml code and populate the data into a relational table. In our example, we are processing the xml output using xml parser transformation. Create a xml parser transformation and then browse in the xml file for fetching the metadata structure. The required output ports are then connected to the target definition.

Step 9: The last step is to create the session and the workflow for the mapping we created. Once that is done, our Informatica job for calling a web service is ready. The Medicare supplier data loaded into the target table is given below.

This concludes the working mechanism of web services in Informatica.