Service Now connectivity using SOAP query through HANA SDA
In this article, the method of connecting Service Now with HANA SDA connection is explained along with the set up for the delta connection is described. The scenario described here will explain the connectivity with Service Now and the data extraction techniques.
Connectivity with Service Now
Connectivity with Service Now from HANA is possible through SOAP adapter. Once the adapter is set up, a connection needs to be created in HANA IDE. This is possible through the SDA option. Please note that the data is always accessed virtually and there is no need of replicating the data for the connection.
Points to note while creating the connection –
- SOAP adapter should be installed
- In HANA SDA, the connection to Service Now is based on one table/view. This means that for connecting to multiple tables, multiple connections would be needed. In each connection, a WSDL would be required for the connectivity.
- Table can’t accessed directly. The only possibility is to use Virtual Function.
In the first step the connection should be created using function add connection. Select one technical name of the connection, add the WSDL link and insert the user name & password
Please note that the WSDL link normally contains the name of the server and the table/view name. This is why there is 1:1 relation between the connection name and the table/view name.
Once the connection is created then expand the connection in the left side under Provisioning -> Remote source. At the end of the hierarchy, the methods should be visible. The method of our interest is getRecords
Right click on the method and create virtual function.
Create flow graph
HANA has introduced an option to consume the Service now virtual function using Flow Graph. This feature can be accessed through IDE. Flow Graph is like any other ETL tool which extracts and load the data into HANA table. In our case, we will use the ADSO active table. In order to create the flow graph, the first pre-requisite is to create an ADSO through BW/4 HANA modelling tool. The ADSO should be of type ‘Direct Update’ as there is no need to generate requests during the administration.
Create the flow graph using the editor.
Once the flow graph is created then click on data source and add the virtual function. This should now act as a source of data.
As soon as the virtual function is created, we need to mention the SOAP query to be executed in the parameter section:
SOAP response will have 3 components –
- Body: This will have the XML response
- Header: Header of the response
- Fault: This will store any error returned during the execution of the flow graph
We need to ensure that the SOAP_BODY is mapped in the next transformation.
SOAP_BODY will always have the output in the unstructured format i.e., it is not possible to map the fields. In order to flatten the structure, it is advised to use the ‘Hierarchy’ transformation in the next stage:
Inside the hierarchy structure we need to map the fields from the hierarchy result:
We can simply drag and drop the elements from the left side into the result (right side bottom). Make sure that you have selected the SOAP_BODY as XML attributes.
There are 4 fields which will be added by default. They are :
You should not remove them else you will have duplicate error. The reason is that, these fields would act as primary key in the target structure for uniqueness.
Once the Mapping is completed, then add a target table. In our case I have used a template table (which will be created automatically based on the mapping specified here:
Here, we need to map the source fields (output of the hierarchy) to target fields (from the table). We have 3 options for Writer type: Insert, Upsert and Update. We use the Upsert to ensure that existing data is modified and new data is written after each run. There is also an option to truncate the table each time we run a load.
Save and activate the Flow Graph.
In this blog, we understood the connectivity option with Service now and accessing data using Flow Graph. In the upcoming blog, I will provide more information on how to integrate flow graph using BW/4 HANA process chain and enable delta update.