Calling REST API using Spotfire® and Advanced Data Services

Introduction

In this page, we will go through the procedure to use external REST API (JSON Format) in a Spotfire^® analysis. The API takes as input an API Key that the user, in Spotfire, will have to provide.

Topics covered will be:

1. Add a Data Source in Advanced Data Services (ADS)
2. Define the XSD of the API response / Transform Json output to XML
3. Transform the XML output to tabular data
4. Make this tabular data visible in ADS
5. Publish the data in ADS
6. Import the data in Spotfire
7. Manage parameters in Spotfire

For this purpose, we will use the publicly available API from Transport for New South Wales. (https://opendata.transport.nsw.gov.au/)

The documentation of the API is really good and they are usually easy to use.

Use case

"I want to have a map with the up-to-date list of road cameras in New South Wales, but I want the users who use this map to provide their own API Key."

Based on the same elements, you could also ask for a user to input an address and put a marker on a map using Google Maps API or, even better, Spotfire GeoAnalytics API!

Traffic Camera Data API

For this use case, I will use this API:

https://opendata.transport.nsw.gov.au/node/340/exploreapi

This API needs two parameters:
 

As a result, a JSON Structure is returned with information about cameras like their coordinates, a link to a picture taken by the camera, its direction, etc.

Example (Only 3 cameras here to shorten the text):

{
  "type": "FeatureCollection",
  "rights": {
    "copyright": "Transport for NSW",
    "licence": "http://livetraffic.com/#dev"
  },
  "features": [
    {
      "type": "Feature",
      "id": "d2e386",
      "geometry": {
        "type": "Point",
        "coordinates": [
          151.10533,
          -34.02977
        ]
      },
      "properties": {
        "region": "SYD_SOUTH",
        "title": "5 Ways (Miranda)",
        "view": "5 ways at The Boulevarde looking west towards Sutherland.",
        "direction": "W",
        "href": "http://www.rms.nsw.gov.au/trafficreports/cameras/camera_images/5ways.jpg"
      }
    },
    {
      "type": "Feature",
      "id": "d2e5699",
      "geometry": {
        "type": "Point",
        "coordinates": [
          151.23122,
          -33.90641
        ]
      },
      "properties": {
        "region": "SYD_MET",
        "title": "Alison Road (Randwick)",
        "view": "Alison Road at Darley Road looking north-west towards Anzac Parade.",
        "direction": "N-W",
        "href": "http://www.rms.nsw.gov.au/trafficreports/cameras/camera_images/alisonrd_randwick.jpg"
      }
    },
    {
      "type": "Feature",
      "id": "d2e649",
      "geometry": {
        "type": "Point",
        "coordinates": [
          151.18022,
          -33.86789
        ]
      },
      "properties": {
        "region": "SYD_MET",
        "title": "Anzac Bridge",
        "view": "Intersection of Victoria Road and Anzac Bridge looking east towards the Sydney CBD.",
        "direction": "E",
        "href": "http://www.rms.nsw.gov.au/trafficreports/cameras/camera_images/anzacbr.jpg"
      }
    }
  ]
}

The goal is to have one "row" in Spotfire per "feature" in JSON response.

Advanced Data Services

First, a quick explanation about Advanced Data Services:

Spotfire Advanced Data Services allows Spotfire users to connect and analyze data available through Composite Information Server.

Basically, Spotfire will connect to Composite Information Server which acts as a virtual connector to different external sources (REST, SOAP, SAP, etc.)

We will use Advanced Data Services to connect to the TfNSW REST API.
 

Connect to REST API: Advanced Data Services

Add the Data Source

To keep a neat project, let's create one folder for this new API.

And name it "NSWCameras":

Let's create the Data Source. Right-click on the new folder and select "New Data Source":
 

In the list of adapters, choose "REST":

A new dialog appears. Give a name to your data source and provide the base URL of the API.

The base URL is given on the TfNSW website:

 https://api.transport.nsw.gov.au/v1/live/

The API will return a JSON structure, therefore we need to tell ADS that it needs to parse a JSON string. For this, tick the "JSON Format" check box. We also need to give a name for the root element of the JSON String. This element will encapsulate the result provided by the API. For this, give a name in the field "Wrapper of JSON Bare Response":
 

We now need to add an operation for the API. There is only one operation available for this API:

URL path is "cameras" and the operation is "GET", so let's add this to our Data Source. Click on the + icon and give a name to the operation:

Specify the path of the operation and the name of the method:
 

In the lower part, we now need to add the parameters we want to pass to the API, and what is expected from the API as a result. This can be achieved in the lower part, in the "Header/Body Parameters" part:

Then, tick the "XML<->JSON" checkbox, it will translate the JSON response to XML which will be easier to parse later on.

If you are lucky, you can try to click on the "Design by example" button to trigger the execution of the API call and let ADS try to identify the format of the response (And create the XSD accordingly). Unfortunately, it didn't work for this API.
 

However, the response is correctly parsed and an XML structure is created. Good thing! We will return to the creation of the XML structure in a while, but for the moment, let's just create the Data Source by clicking on the "Create" button.

We now need to introspect the Data Source. Select the operation and click on Next. (In case you later need to add new operations to this Data Source, the introspection will have to be performed again).
 

Click on Finish. The introspection starts

Once finished, click on OK.

And click on "Save and Close" on the main dialog.

Create the XSD of the API response

This step is not necessary in case you've been lucky in the "Design by example". We will create an XML schema defining the format of the response of the API call.

Open the file with the result of the first API call or paste the content into a text editor. Format the XML to make it more readable.
 

Note: As mentioned by ADS, the response provided during the test call may have been truncated, so ensure that the tags are closed correctly.

In this example, there are:

• The root node called cameras (Remember, that is the name we set in field "Wrapper of JSON Bare Response")
• A element "type"
• A node "rights"
• And several nodes "features" that are the actual cameras

My example was truncated in the middle of a node "features". So I simply removed this node and closed the main node "cameras".

Now let's create an XSD file from the XML content. For this, I used an online tool called XMLGrid.
 

And I saved the generated XSD file.

This is a good starting point but it needed some tweaks to make it fit the exact output from our API call. For example:

• Set the maxOccurs of geometry
• Set the minOccurs and maxOccurs of coordinates
  • As in the API response, there will always be two coordinate fields: longitude and latitude.

Final XSD:

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" attributeFormDefault="unqualified">
  <xs:element name="cameras">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="type" type="xs:string"/>
        <xs:element name="rights">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="copyright" type="xs:string"/>
              <xs:element name="licence" type="xs:string"/>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="features" maxOccurs="unbounded">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="type" type="xs:string"/>
              <xs:element name="id" type="xs:string"/>
              <xs:element name="geometry" maxOccurs="1">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element name="type" type="xs:string"/>
                    <xs:element name="coordinates" minOccurs="2" maxOccurs="2" type="xs:double"/>
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
              <xs:element name="properties" maxOccurs="1">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element name="region" type="xs:string"/>
                    <xs:element name="title" type="xs:string"/>
                    <xs:element name="view" type="xs:string"/>
                    <xs:element name="direction" type="xs:string"/>
                    <xs:element name="href" type="xs:string"/>
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Let's save this in our project. Right-click on the NSWCameras folder and select "New Definition Set":
 

Give a name to your file and choose XML as the type. (We want to create an XSD file)

Save the content of the XSD in this file.

Edit the Data Source

Now that the XSD has been generated, we want it to be applied to the response of the API call.

Re-open your Data Source and go to Header/Body parameters. Then, as the data type of the response, choose the XSD structure that we've created in the previous step.

The result should be:

If you now go back to the operation, you can see the full structure of the output:

Great, now the Data Source is done and correctly configured! But still in XML format... We need to show it in a tabular format. That's what we are going to do in the next step.

Transform XML structure to tabular data

Let's make our XML structure into a tab structure. For this, let's create an XSLT Transformation. Right-click on the project folder and choose "Transformation":

Then select "XSLT Transformation":
 

Click Next, give a name to your transformation file, and select the operation of the Data Source you want to apply the transformation to:

Click Finish.

A new tab opens with the transformation mapping screen:
 

Expand the camera node up to the nodes that you want to export.

In our case, one "feature" will become one row.

We now need to map the XML elements to table columns.

Select the element you want to have as a column and click on "Create Link And Target"



This is the easiest way to create output columns.

You can do that for all the elements at once (But you need to unselect the nodes first):
 

Mapping is created:
 

But the column's order is weird. To reorganize that, click on the output field you want to move and move it with the blue arrows:

Result:
 

You can rename columns by right-clicking on the name and selecting "Rename":
 

You can also change the data type from here if needed.

Once done, you can try the mapping:
 

And see the results below:

Good but not done yet.

Two issues:

• Several rows per node
• Coordinates are not split into two columns (long/lat) (One value per node)

Let's review the XSLT code (Click on tab XSLT).
 

The default XSLT does a loop for every complex node. This is why we have multiple rows for the same "features" nodes. This need to be corrected.

Another issue is that the coordinates are stored in one column but on two rows. We should rather have two columns, one for longitude and one for latitude.

Let's create those two columns: go back to the Data Map tab and click on the Add button:

Select the Data type of the new attribute (longitude and latitude are real often stored as double):
 

The two new fields appear in the last positions:



Move them next to the coordinates and delete the coordinates:

Note that those fields are not mapped yet but all the output fields are created.

Let's now get rid of duplicate rows. For this, let's go in the XSLT view.

For example, we know that there will be only one "geometry" node by the camera, so no need to loop on it.



And we know that there will be exactly two "coordinate" nodes (One for longitude, one for latitude), then let's simplify the code.

Note: Once you edit the XSLT file, the Data Map will no longer be available!
 

Removing unnecessary loop:

Let's also map the coordinates:

You can do all the XSL transformations you need. The things to remember are the following:

• At the top of the XSLT file, the variables:

That's for internal use only.

• The columns that need to be exported must be mapped in the XSLT.

Example:

Column "latitude" will be exported and its value will come from variable "_latitude".

But for the column "latitude" to be exported, it must have been defined in the tab "Outputs":
 

All the other variables that are not in this tab, even if mapped in the XSLT, will NOT BE exported.

Ok... now I won't get into all the details of the XSLT... Here is the simplified file:

<xslt:stylesheet xmlns:xslt="http://www.w3.org/1999/XSL/Transform" xmlns:csw-xform="http://www.compositesw.com/2003/xform" xmlns:xs="http://www.w3.org/2001/XMLSchema" version="2.0">
  <xslt:template match="cameras">
    <xslt:element name="results">
      <xslt:for-each select="features">
        <xslt:variable name="_typefeature" select="type"/>
        <xslt:variable name="_id" select="id"/>
        <xslt:variable name="_typegeometry" select="geometry/type"/>
        <xslt:variable name="_longitude" select="geometry/coordinates[1]"/>
        <xslt:variable name="_latitude" select="geometry/coordinates[2]"/>
        <xslt:variable name="_region" select="properties/region"/>
        <xslt:variable name="_title" select="properties/title"/>
        <xslt:variable name="_view" select="properties/view"/>
        <xslt:variable name="_direction" select="properties/direction"/>
        <xslt:variable name="_href" select="properties/href"/>
        <xslt:element name="result">
          <xslt:element name="typefeature">
            <xslt:value-of select="$_typefeature"/>
          </xslt:element>
          <xslt:element name="id">
            <xslt:value-of select="$_id"/>
          </xslt:element>
          <xslt:element name="geometry_type">
            <xslt:value-of select="$_typegeometry"/>
          </xslt:element>
          <xslt:element name="latitude">
            <xslt:value-of select="$_latitude"/>
          </xslt:element>
          <xslt:element name="longitude">
            <xslt:value-of select="$_longitude"/>
          </xslt:element>
          <xslt:element name="region">
            <xslt:value-of select="$_region"/>
          </xslt:element>
          <xslt:element name="title">
            <xslt:value-of select="$_title"/>
          </xslt:element>
          <xslt:element name="view">
            <xslt:value-of select="$_view"/>
          </xslt:element>
          <xslt:element name="direction">
            <xslt:value-of select="$_direction"/>
          </xslt:element>
          <xslt:element name="href">
            <xslt:value-of select="$_href"/>
          </xslt:element>
        </xslt:element>
      </xslt:for-each>
    </xslt:element>
  </xslt:template>
</xslt:stylesheet>

We can now try the transformation:
 

When trying now, the export is correct:
 

Great, now we've got the data as a table! Awesome!

But... you'll have remarked that during this implementation, each time you need to test the execution, you need to provide the full inputs declared in the operation. (The two headers).

We will now create a parameterized query to call the operations with the correct inputs.

Create parameterized query

We are almost done with ADS. We could make the data available right now, but all the input parameters would need to be hardcoded. In this case, it's only an authentication header, so it could be fine, but remember my use case: the users of this report need to provide their own API key. Let's, therefore, add a query in which we expect, as an argument, their API Key.

Right-click on your project and select "New Parameterized Query":
 

Give it a name and click on "OK":
 

A new tab opens. Drag and drop the transformation previously created in the blank area:
 

A dialog with the two input parameters appears:

Remember the input needed by the API:

• One header to set the accepted content type (Accept)
  • Which has only one possible value "Accept: application/json"
    • So this value can be hardcoded as it will never change
• One header to set the API Key (Authorization):
  • Which is composed of "apikey" and the API key itself
    • So this value will be given as a parameter (Note the "Query Parameter" under the name of the field "Authorization"

Click on OK. The SQL is generated for you. Go to the SQL tab to see it:
 

As you can see in the previous screenshot:

• The first parameter is hardcoded: 'application/json'
• Second parameter is replaced by a variable: apikey

That's cool, but here, that means that the user needs to provide the full header value of the API Key, i.e.; "apikey XXXXXXXXXXXX"

It would be better to ask for the API Key only, not the full header, right?

Let's edit the SQL query to add the prefix "apikey " to the second parameter. But, as for the previous mapping, the GUI mode will not be visible anymore once the SQL query is edited:
 

SQL query updated:
 

Note the double pipe for the concatenation of the string "apikey " and the value of the variable apikey.

Now, when testing:
 

Only the API key is requested!
 

And the result shows the lines:

Ok, now, we can have a parameter for our API, through this parameterized query. Awesome! But I feel a little bit of impatience now... Almost done! Next step, is the creation of a view.

Creation of view

Right-click on your project folder and select "New View":
 

Give a name to your view. This name will be visible in Spotfire when querying the data.
 

In the opened tab, drag and drop the parameterized query:

A dialog appears asking for the input:

Change the drop-down to Declaration but do not enter any value in the input field and click on OK.

Open the Grid tab and change the "Alias" (by default Expr1) name to make it more understandable. This is the name of the variable that will be prompted in Spotfire:

When going to the SQL Tab, the SQL Query is using the variable we've just declared:

Publishing the view

I promise this is the last step with ADS! :)

To publish the view, start by creating a new Composite Data Source. Right-click on "Databases" in "Composite Data Services" and select "New Composite Database Service..."
 

Name your database (This name will be visible in Spotfire) and click on OK:
 

Now right-click on your view and publish it:

Select the database previously created:
 

And click on OK. Your view is now created!
 

All done for ADS configuration!

Configure Spotfire

Ok, now we're done with Advanced Data Services. We need now to connect with Spotfire to ADS new Data Source. But for this, you have several choices:

1. Advanced Data Services authentication
   • Take this as a connection to a Data Base: When opening a report, would you like the users to authenticate on the database server (In addition to the Spotfire Server)
     1. Choice 1: The user that connects to the report needs to authenticate on the Advanced Data Services server (Not user-friendly)
     2. Choice 2: The credentials to connect to Advanced Data Services are stored on the Spotfire Server
2. Parameterization of query
   • As we added a parameter in the query, we need to fill in this parameter. For this, you have two choices.
     1. Choice 1: The user will be prompted to enter a parameter. This parameter can then be used every time he refreshes the data or can be set once and for all
     2. Choice 2: The user can input his parameter in an input field. When updating the value of the input field, the data will be refreshed based on the input value.

Advanced Data Services authentication

User authentication

When having a report using ADS, you can ask the user to authenticate on the ADS server. For this, you simply need to add the connection to the Spotfire report without any additional configuration:
 

Fill in the ADS credentials, click Connect, and chose the Database we've just created (NSWCamerasDB)

Then click OK.

The next dialog is to select the tables/view we want to use. But, depending on the parameter input method we chose, the method will be different:

• If you want the user to be prompted for the parameter value (Every time the data is refreshed), click here
• If you want the user to use an input text and have a more dynamic experience, click here

Saved credentials

You also have the possibility to store the ADS credentials to be authenticated once and for all, and, above all, avoid the user needing to input ADS credentials.

For this, you need to store the Data Connection in the Spotfire library and save the credentials: Tools > Manage Data Connections...
 

Add New > Data Source > Cisco Information Server
 

Fill in the ADS credentials, click Connect, and chose the Database we've just created (NSWCamerasDB)

In the new dialog, click on the "Credentials" tab and choose to save the credentials:
 

Note: As written, it may be considered a security risk to store the credentials with the Data Source...

Finish by saving your Data Source in the library by clicking on "Save As...". Select the folder where you want to save the connection and click on save.
 

Now, you can add Data Tables using this Data Source:

And the user will not be prompted anymore for the ADS credentials.
 

Parameterization of query

Remember that our query expects one argument: The API Key that is necessary for the REST API. Spotfire, therefore, needs to provide this API key to ADS. And... The user needs to provide this API Key to Spotfire. :)

You have two choices for this:

1. Prompt the user so he inputs the API Key when reloading the data
   1. Which, in the case of an API Key would be sufficient
2. Use an input field as the source of the parameter
   1. In case for example you need more dynamic input from the user. (i.e: When the API call needs additional input from the user like, for example, for Google Maps API, the address to locate)

User prompt

This method is to prompt the user to input a parameter (Here, an API key) when refreshing the data. As a pre-requisite, you need the Connection to ADS to have been done (Here or here)

Select the view/table that you want to use and click on "Add >":
 

The columns of this table appear in the right panel. Note that an additional column has been added: "API_KEY":

Click on the "Define Prompting..." button to define what fields should be prompted to the user. In the new dialog, click on "New...".

Select the API_KEY column and chose "Manual input" as the prompt type:
 

Click OK to close this dialog, OK to close the "Define Prompting" dialog, and "OK" to close the "Views in Connection" dialog.

Click "OK" to add the data table.

You will be prompted for the API Key:

Then, if the API key is correct, you should be able to see the data:

Input value

Now another solution: adding an input text that the user will use to enter his API Key. As a pre-requisite, you need the Connection to ADS to have been done (Here or here).

When adding the table and choosing the ADS connection, you will see:

Chose "Custom Query" > "New Custom Query...":

Give a name to this custom query and enter the SQL statement. 

• The name of the view/table is the name of the view you have created in ADS.
• For the columns to select, you can set a "SELECT *" for the moment as, when clicking on "Verify", you will be able to see the retrieved columns and remove the unnecessary ones.
• To create a parameter, you need to prefix it with a question mark in the SQL query and create it in the "Parameters" tab.
• Once the SQL query is filled in and parameters are created, click on Verify to check everything is good. You should see a green mark as on the screenshot above.

Click on OK to save the query and go back to the main view selection:
 

Click OK to close this dialog and return to the "Add Data Tables":

Select "Load on demand" if not already selected and click on "Settings..."

In the opened dialog, select the API_KEY column and click on "Define Input...":
 

(Here the input is set as "Prompt in connection")

In the Define Input dialog, change the following:

• Input for the selected parameter:
  • Values (fixed/properties/expression)
• Input from:
  • Property (And click on "Select..." to select the property that will store the API Key)

In the "Select Property" dialog, create a new property:

Then click OK to close these dialogs and to return the "On-Demand Settings":

Note here that the input comes from a Document property.

If, when creating the property, you set the value of the API Key, then you should see the data.

From now, you can add an input field to a text area and link it to the property that stores your API Key:

Then, if the user leaves the API key blank or enters an incorrect key:

Final result

Well, I will not go into all the details on how to set up markers on a map and so on. But I guess that you would like to see the end result, right?

Here it is:
 

Troubleshooting

The CustomQuery: 'QueryWithUserInput' was not allowed to execute because it is not trusted.

• Ensure the user has the correct rights and is in the "Custom Query Author" group.

If the query is still not trusted:

• In Spotfire, chose "Edit" > "Data Connection" properties
• Select the connection and click on the "Settings" button.
• Click the "Edit" button to edit the query
• Select the custom query on the left-hand side and click on "Edit custom query"
• Click the "Verify" button and check that the query is still valid
• Click on OK on all opened windows to close them
• Save the report in the Spotfire library

The query should now work

See Also: Accessing a Web Service via IronPython