(v24) Connecting to Generic Web Data Sources

Connecting to Generic Web Data Sources

This applies to: Managed Dashboards, Managed Reports

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.

Set Up the Data Connector

Create a new data connector and set the Data Provider drop-down 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 Standard .NET Classes:

• 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.

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.

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:

Property	Type	Description
Authorization	string	Gets or sets the Authorization header value.
Data	IDictionary<string,object>	Gets a dictionary that can be used to store any information which is needed for subsequent requests using this connection.
Secret	string	Gets the value of the Secret setting of the data connector.
Settings	IDictionary<Guid,object>	Gets all of the data connector's setting values (including any without dedicated properties already listed) keyed by data connector property ID.
Url	string	Gets the URL setting of the data connector.
UserName	string	Gets the User Name setting of the data connector.

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.

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()

Returns	Type	Description
Returns:	Request	An object that will send an HTTP request using the connection information.

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.

Request Properties

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

Property	Type	Description
Headers	IDictionary<string,string>	Gets a dictionary defining the HTTP request headers to be sent to the request URI. By default this contains an Authorization header if it was set on the connection object.
Uri	string	Gets or sets the URI (URL) where the request will be sent.
TimeoutSeconds	int	Gets or sets the maximum number of seconds to allow for receiving the response before canceling the request. Default: 100

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

ExecuteGet Method

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

ExecuteGet()

Returns	Type	Description
Returns:	Response	The response to the HTTP GET request that was sent. (Your script will wait for the response before it continues.)

Example:

var response = request.ExecuteGet();

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")

Parameter	Type	Description
body	string	The body of the request that the POST method you are calling is expecting as data.
encoding	string	(optional) The name of a specific text encoding to use in the request body. Default: utf-8
mediaType	string	(optional) The media type of the request body. Default: text/plain
Returns:	Response	The response to the HTTP POST request that was sent. (Your script will wait for the response before it continues.)

Example:

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

Note: The example above is defining JSON using a verbatim string literal so that it can span multiple lines. These string literals use pairs of quotation marks (quote escape sequence) to insert a literal quotation mark into the JSON.

Response Properties

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

Property	Type	Description
Content	string	Gets or sets the response content. Default: the body of the response returned for your request.
Headers	IDictionary<string,string>	Gets a dictionary defining the HTTP response headers returned in response to your request.
IsLastPage	bool	Gets or sets a true/false value indicating whether this is the last page of data. This defaults to true - change to false if the Get Page Script should run again to get more data.
StatusCode	HttpStatusCode	Gets the HTTP status code returned in response to your request. This is commonly HttpStatusCode.OK.

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;

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)

Parameter	Type	Description
name	string	A name to identify this parameter.
dataTypeName	string	The parameter data type, one of: DateTime, bool, string, double, or int.
defaultValue	object	The default value of the parameter if not changed elsewhere. This value must be of the type specified in dataTypeName.

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 Define 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.

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.

Examples

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;

Symphony REST example

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

For this example, set the URL to <symphony-url>/API/Account/Query to call the /Account/Query POST method. Replace <symphony-url> with the base URL you usually use to access Symphony (you can use localhost to refer to the server where the <symphony-url> 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.

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)

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");

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.

CalculateMd5Hash Method

This method calculates an MD5 hash of an input string.

CalculateMd5Hash(string input)

Parameter	Type	Description
input	string	A value to hash using the MD5 algorithm.
Returns:	string	The MD5 hash of the input string.

Example:

var hash = ConversionHelper.CalculateMd5Hash(inputString);

GetJsonValue Method

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

GetJsonValue(string json, string jsonPath)

Parameter	Type	Description
json	string	Text in JSON format.
jsonPath	string	A JSONPath expression.
Returns:	string	A single property value or object extracted from the input json as a string. Depending on the value, the returned string could represent a subset of the original JSON, a string value enclosed in quotation marks, a number, a Boolean value, or null. An error may occur if multiple JSON tokens match your JSONPath expression, e.g., matching multiple items in an array.

Example:

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

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)

Parameter	Type	Description
xml	string	Text in XML format.
xPath	string	An XPath expression.
namespaces	IDictionary<string,string>	(Optional) A dictionary containing namespace URIs keyed by prefixes that may be used in the XPath expression.
Returns:	string	A subset of the input xml corresponding to the xPath.

Example:

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

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)

Parameter	Type	Description
original	string	A string to convert.
Returns:	string	The string representation in base 64.

Example:

string base64 = ConversionHelper.ToBase64String(text);

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 asDefine Parameters.

For more information, see:

• Connect to Data and View it on a Dashboard