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.

• incrementValues — when using incremental updates in version 25.4 or higher, passes to your script the maximum values from incremental elements already stored from previous data cube storage job runs.

• sourceEntityId — passes to your script the ID of the data cube executing it in version 25.4 and higher, in case this information is needed.

• sourceEntityName — passes to your script the name of the data cube executing it in version 25.4 and higher, in case this information is needed.

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:

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

Example:

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:

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

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

Example:

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:

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)

Example:

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

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

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.

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.

Incremental Data Updates

In the Advanced section, select the Enable Incremental Warehouse Support option in version 25.4 and higher when you want the data connector to support data cubes using incremental builds for its warehouse or in-memory storage. This way, each time the data cube builds its storage, it can download only new data not previously downloaded and append it to the previously stored data, rather than downloading all of the data.

After enabling this option, use the the incrementValues object in your script to filter the data accordingly to download only new data. This object has type IDictionary<string, object> where the keys are the names of the data cube's Incremental Elements and the values are the maximum values previously stored for each of those elements.

For example, append to your request URL an appropriate parameter specifying the latest date or sequential ID already downloaded previously as the exclusive minimum value to download next:

if (incrementValues["date"] != null) { request.Uri += "&date=" + incrementValues["date"]; }

You can optionally include the particular data cube's name and ID if needed using other objects available to your script.

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.

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:

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:

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:

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

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.

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:

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)

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)

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)

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)

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.

Note: If you need to serialize to or deserialize from JSON in script instead, use the JsonConvert class in Symphony v25.3 and later.

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