This article acts as a guide to illustrate how to pull the data from a REST API into TDV and then transform the data into a tabular form. 

This article is a guide to introspect a REST API in TDV and transforming the response to a relational table

Note: For the sake of demonstration, we are using  https://data.cms.gov/resource/xbte-dn4t.json as the sample REST service. If the above public REST service is not working at the time of reviewing this article, please feel free to use any REST endpoint available to you. 

CREATING A REST DATASOURCE:
(1) If your endpoint returns a huge amount of data, then check the below-mentioned configuration and set an appropriate value accordingly. 

(i) Open TDV Studio
(ii) Navigate to Administration > Configuration > Studio > Data and set "XML Text Size" to an appropriate value as per your usecase. 

Note: For the sake of demonstration, we have set the value of this configuration to 1 million characters.

(2) Create a new data source in TDV Studio with REST as the adapter. 

(3) Provide all of the required details on the data source:

(i) Name – Provide a name for data source -> Ex: Training
(ii) Base URL = https://data.cms.gov/resource (Everything after the resource is the endpoint)
(iii) Check the box “JSON Format”
(iv) Set an appropriate package name (Ex: pkg -> you can provide your own package name)
(v) Set an appropriate name for "Wrapper of JSON Bare response" (You can set any name here – Ex: wrap)

Screenshot for reference:


(4) After step(3), scroll down on the same page to configure the REST endpoints. Click on the green “+” icon to create a new operation. A new pop-up window will be populated on the screen. Set an appropriate name for the operation. For the sake of this article, let us name the operation as "OP1". Once the operation is named, you will see a new "Details for OP1" tab on the right-hand side of the screen. 

(5) Now set the following values for the operations:

(i) Set the "Operation URL" (Ex: xbte-dn4t.json)
Note: A combination of Base URL + Operation URL should be a valid REST endpoint.
(ii) [Optional] Click on the "parse" button next to the Operation URL.
(iii) Scroll down and continue configuration. Click on the “+” sign in the "Header/Body parameters" section.
(iv) Click on the "Param Name" column and set a name for the parameter. (Ex: output in this scenario)
(v) Click on the "In/Out" column and change this value from IN to OUT. (This changes the type of the parameter from input to output)
(vi) Click on create.

Screenshot for reference:

Note: Repeat the same process for each and every endpoint. 

(6) Now click on create. A new introspection window pops up. Select all of the endpoints/resources you want to introspect into TDV and follow the screen prompts to proceed. 
Screenshot for reference:


(7) After the introspection is complete, you will be back to the "Physical Datasource" configuration screen. In the "Header/Body Parameter" section, select the white "DesignByExample" button which is located next to the “-“ sign which generates a popup. In this popup window, scroll down to OP1 definition and select "wrap"(as shown above) and click "OK". Now verify the "XML-JSON" check box got selected automatically. This step actually takes the JSON output and converts it to XML for XSLT transformation.
Screenshot for reference:


(8) Once you confirm everything till step(7), go ahead and click on the "Save & Close" button.

(9) Expand the "govHealthCareRest" data source which we created above and select OP1 and choose open. Execute the operation "OP1" and confirm the output. You can close this window after you see the output.

CREATING A TRANSFORMATION:

(10) Right-click on any folder of your choice and select "New" > "New Transformation" and choose "XSLT Transformation" and click on "Next"
Note: There are a total of 5 different transformations that TDV offers. Select the appropriate one depending on your use case. Explanation of each of the transformations is outside the scope of this article. If you need more information on each of them, refer to the User's guide. 
Screenshot for reference:


(11) Provide a name for transformation and select the resource on which the transformation needs to be applied on. For demonstration, let us can call this "trainingtransformation" and click "Finish".
Screenshot for reference:


(12) Now you can expand "wrap" to see all of the objects from the REST service. Choose the objects you need and click on the link icon with an arrow to add to the output. This step is used to map the JSON objects to a relation table's columns. 
Screenshot for reference:


(13) Now save the changes and execute the transformation. Now you can see the data being returned from the REST web service in a tabular format.
Screenshot for reference: