Connecting to Generic Web data sources

1. Overview

This article describes how you can use the Generic Web data provider to connect to various online data sources such as web applications and sites offering REST API services.

Main article: Connect to data and view it on a dashboard

2. Set up the data connector

Create a new data connector and set the Data Provider dropdown to Generic Web.

Fill in the URL setting to the address of the online data source, or the URL to an API method you want to access.

The User Name and Secret fields are optional. You can set these and access their values in script if needed rather than writing them directly in the script.

Enter at least a Get Page Script. Depending on your data source, different script written in DundasScript may be required to authenticate and access the data, and you can find several samples in the examples section below. Your script has access to the following objects:

• connection — defines connection information in the Connection Script and creates a request in the Get Page Script.
• parameters — defines optional parameters you can expose to users of the data connector and refer to in your script.
• session — the current logon session of the user when the script runs.

And these classes in addition to the ones you can access in any DundasScript:

• ConversionHelper — helper methods for converting and manipulating request and response data.
• HttpUtility — provides methods for encoding and decoding URLs when processing Web requests.
• Request — executes HTTP requests to an online service.
• Response — the response returned by a request.

2.1. Connection script

You can enter a Connection Script if connections to your data source require an authorization header, or if you want to set up any initial values to use later in the Get Page Script. This script is otherwise optional.

2.1.1. Connection properties

Your script can access the connection object, which has the following properties. The first two can optionally be customized, and all properties can be read later from your Get Page Script:

2.2. Get page script

The Get Page Script is required but can be very simple if it returns all of its data at once, or more complicated when needed to retrieve a large amount of data in separate requests as 'pages' containing portions of the complete result.

This script must return a Response object you received to a Request made to your data source or service.

2.2.1. CreateRequest method

First, call the connection.CreateRequest method on the connection object to get a Request populated with whatever information you provided so far.

CreateRequest()

Example:

var request = connection.CreateRequest();
// or
var request = new Request(); // blank request - needs properties set

Alternatively, you can create a new blank Request as shown in the last line above, and then populate its properties yourself.

2.2.2. Request properties

The Request type object has the following properties, which can be customized as needed:

Call one of the following two methods to get a Response depending on which type of HTTP request to send.

2.2.3. ExecuteGet method

The Request.ExecuteGet method performs an HTTP GET method request (the most common type of web request).

ExecuteGet()

Example:

var response = request.ExecuteGet();

2.2.4. ExecutePost method

The Request.ExecutePost method performs an HTTP POST method request, if the API method you are calling is a POST method.

ExecutePost(string body, string encoding = "utf-8", string mediaType = "text/plain")

Example:

var body = @"[
  {
    ""objectId"":""de1a699b-793a-45ee-bdf2-fbab59edfe96"",
    ""clientData"":""" + Guid.NewGuid() + @"""
  }
]";
var response = request.ExecutePost(body, "utf-8", "application/json");

2.2.5. Response properties

Once you have the Response type object, you can optionally read or customize its properties as follows before returning it:

If IsLastPage is changed to false, the script will run again to get more data. You should keep track of which data to request next using connection.Data.

The last line of script should return the response. Example:

return response;

2.3. Define parameters script

If you want your data connector to expose parameters so users can customize values provided to your script, expand the Advanced section and set the Define Parameters Script.

You can call the following method on the parameters object:

Add(string name, string dataTypeName, object defaultValue)

Example:

parameters.Add("From", "int", 0);

With a parameter added, you can access parameters in your other scripts as a dictionary (IDictionary<>). For example:

int from = parameters["From"];
parameters["From"] += 10;

Users with access to this data connector can make use of these parameters by dragging the data under it in the Explore window onto a data cube while editing. This adds the data as a Tabular Select, and you can then configure these parameters to customize their values, connect them to bridge parameters, or make them public to connect filters directly to them on a dashboard or other view.

2.4. Cleanup script

In the Advanced section, you can optionally add a Cleanup Script that will run after the Get Page Script is finished running or if data retrieval otherwise stops. You can make another request to the service you're using, for example, if you need to let it know you're finished.

2.5. Using a proxy

If you need to use a proxy, you can configure one in version 23.3 or higher. Go to the configuration settings in Administration or have an administrator do this for you: settings for the proxy server name or address, port, username, and password using basic credentials are located in the Data Provider category.

With a proxy configured, expand the Advanced section of your generic web data connector and select the Use Proxy checkbox option.

3. Examples

3.1. Basic example

Some online data sources or APIs may be available without any special authentication, headers, or posted content, and can simply be accessed directly via URL.

The following Get Page Script accesses a public web data source from the address set in the URL setting of the data connector.

var request = connection.CreateRequest();
var response = request.ExecuteGet();
return response;

3.2. Twitter example

You can access the Twitter API v2 using settings and script like the following. Twitter provides a tutorial you can follow for the same API endpoint used as an example here to get tweets composed by a single user.

Set the URL to the API's endpoint URL you want to use, such as https://api.twitter.com/2/users/2244994945/tweets

This example accesses public Twitter data, so you can set Secret to the OAuth 2.0 Bearer Token you've generated using your developer account.

The Connection Script can access this bearer token value and include it in an Authorization header:

connection.Authorization = "Bearer " + connection.Secret; 

This Get Page Script sets Twitter's URL parameters to get only up to 5 results and exclude retweets and replies:

var request = connection.CreateRequest();
request.Uri = connection.Url + "?max_results=5&exclude=retweets,replies";
var response = request.ExecuteGet();
return response;

3.3. Dundas REST example

Dundas BI has its own REST API, which you can access like other REST API services.

For this example, set the URL to {Dundas BI URL}/API/Account/Query to call the /Account/Query POST method. Replace {Dundas BI URL} with the base URL you usually use to access Dundas BI (you can use localhost to refer to the server where the Dundas BI instance you're using is running, e.g., http://localhost:8000/).

This Connection Script accesses session to set the request's Authorization header to the current session ID of whichever user is accessing data. It also sets up an initial page number value stored in the Data property:

connection.Data["pageNumber"] = 1;
connection.Authorization = "Bearer " + session.Id.ToString();

This Get Page Script gets pages of up to 50 accounts at a time, keeping track of progress by updating the pageNumber data value set above:

var request = connection.CreateRequest();
int pageNumber = connection.Data["pageNumber"];
string postData = @"{{
  ""queryAccountsOptions"": {{
    ""pageNumber"": {0},
    ""pageSize"":50
  }}
}}"; // "{{" & "}}" are literal braces for string.Format
postData = string.Format(postData, pageNumber);

var response = request.ExecutePost(postData, "utf-8", "application/json");
response.IsLastPage = response.Content.EndsWith("[]");
connection.Data["pageNumber"] = pageNumber + 1;

return response;

The IsLastPage property is set to true when no more accounts are returned.

3.4. OData example

This example connects to an OData data source as an example (OData is a standard that you can connect to using the dedicated OData data provider).

The URL is set to https://services.odata.org/V4/OData/OData.svc/Products for this example.

The Connection Script sets up an initial value for the $skip query string parameter to include in the request:

connection.Data["$skip"] = 0;

The Get Page Script gets two items at a time, filtered by the range of IDs defined by two parameters p0 and p1.

It also uses HttpUtility to encode text to be compatible in a URL's query string, and returns only the contents of the root element's value property using the GetJsonValue ConversionHelper method:

var request = new Request();
request.Uri = connection.Url + "?$top=2&$skip=" + connection.Data["$skip"];
string filter = string.Format("ID ge {0} and ID le {1}", parameters["p0"], parameters["p1"]);
request.Uri += "&$filter=" + HttpUtility.UrlEncode(filter);

var response = request.ExecuteGet();
response.IsLastPage = ConversionHelper.GetJsonValue(response.Content, "$.value[1]") == null;
response.Content = ConversionHelper.GetJsonValue(response.Content, "$.value");
connection.Data["$skip"] += 2;

return response;

The Define Parameters Script adds the two parameters referred to above, which also allows them to be used in a data cube:

parameters.Add("p0", "int", 1);
parameters.Add("p1", "int", 10)

3.5. Advanced example

This example demonstrates a number of different operations an API might require you to perform in a more advanced case.

The URL setting can be set like the following to include placeholders, which you can replace in script by calling string.Format as shown below:

https://{0}.example.com/API/v3/data/{1}/export/

The following Get Page Script requests a potentially lengthy export process to start, polls its progress via additional requests, and when complete downloads the content to return in a final request. An X-API-KEY header is set in the requests as an example for authentication used by some APIs. The HTTP status codes and other response information is checked at different points before proceeding, and custom exceptions thrown in case of failure to be displayed to users or logged.

string baseUri = string.Format(connection.Url, parameters["DataCenter"], parameters["ID"]);
Request request = new Request();
request.Headers["X-API-KEY"] = connection.Secret;
request.Uri = baseUri;
string exportBody = @"{
  ""format"": ""json""
}";

// Check that the initial export request is OK, otherwise display an error
Response exportResponse = request.ExecutePost(exportBody, "utf-8", "application/json");
if (exportResponse.StatusCode != HttpStatusCode.OK) {
  throw new Exception("Export status: " + exportResponse.StatusCode.ToString());
}

string status = ConversionHelper.GetJsonValue(exportResponse.Content, "$.result.status");
string progressId = ConversionHelper.GetJsonValue(exportResponse.Content, "$.result.progressId");
status = status.Replace("\"", "");
progressId = progressId.Replace("\"", "");

// Poll for export progress while still in progress
while (status == "inProgress") {
  request.Uri = baseUri + progressId;
  Response progressResponse = request.ExecuteGet();
  status = ConversionHelper.GetJsonValue(progressResponse.Content, "$.result.status");
  status = status.Replace("\"", "");
}

// Check that export succeeded with a file ID
string fileId = ConversionHelper.GetJsonValue(progressResponse.Content, "$.result.fileId");
if (status == null || status != "complete" || fileId == null) {
  throw new Exception("Progress status: " + status);
}

// Return file contents
fileId = fileId.Replace("\"", "");
request.Uri = baseUri + fileId + "/file";
return request.ExecuteGet();

The Define Parameters Script adds the two parameters that are used to replace the placeholders in the set URL in the first line of the Get Page Script:

parameters.Add("DataCenter", "string", "us1");
parameters.Add("ID", "string", "a12BXu");

4. ConversionHelper

The ConversionHelper class provides some helper methods that you can access if needed from your script for converting values for your request or from the response.

4.1. CalculateMd5Hash method

This method calculates an MD5 hash of an input string.

CalculateMd5Hash(string input)

Example:

var hash = ConversionHelper.CalculateMd5Hash(inputString);

4.2. GetJsonValue method

This method extracts a single value from JSON content according to a JSONPath expression.

GetJsonValue(string json, string jsonPath)

Example:

string progressId = ConversionHelper.GetJsonValue(response.Content, "$.result.progressId");
progressId = progressId.Replace("\"", ""); // remove quotation marks from the JSON string value

4.3. GetXmlValue method

This method extracts a portion of XML content according to an XPath expression.

GetXmlValue(string xml, string xPath, IDictionary<string, string> namespaces = null)

Example:

ConversionHelper.GetXmlValue(response.Content, "book");

4.4. ToBase64String method

This method encodes a string of Unicode characters to bytes and converts these to a string of base-64 digits.

ToBase64String(string original)

Example:

string base64 = ConversionHelper.ToBase64String(text);

5. Transform the result

The response to most web requests is a block of text in JSON or XML format. You can transform this into regular columns of data using a data cube to make its individual values ready to analyze and visualize.

After creating or opening an existing data cube, expand your new data connector in the Explore window and drag the WebData structure onto the canvas to add it as a Tabular Select transform.

You can insert a Flatten JSON or Flatten XML transform depending on which format the API returns, or you can use any of the other available transforms.

If the Define Parameters script in the data connector exposes parameters, you can configure these in the Tabular Select transform in a similar way as for a SQL Select.

6. See also

• Connect to data and view it on a dashboard
• Writing data scripts with DundasScript
• Using a data cube to join tables
• Transform: Flatten JSON
• Transform: Flatten XML
• List of transforms