Use a custom transform to call a web service


1. Overview

You can augment your data in Dundas BI by using a custom transform to call a web service as part of your data preparation process. The sample transform shown here can be added to a data cube to calculate the travel distance and time (e.g., driving distance) between two locations using the Google Maps Distance Matrix API web service.

This article focuses on making a web service call from a custom transform. For more information about the options available when creating custom transforms and about Dundas BI extensions, see Create a custom transform.

2. Getting started

In addition to the prerequisites found in Create a custom transform, the transform in this sample extension requires a Google Maps Distance Matrix API key. You can obtain one here:

The sample will take the origin and destination addresses from your data and add two new columns in its output, which give the driving distance in kilometers and the driving time in current traffic between the two locations in each row.

2.1. Download the sample solution

To download the custom transform sample solution, click here.

(A sample solution is also available for Dundas BI version 6 and Dundas BI versions prior to 6.)

2.2. Open the solution

Extract to a folder and open the solution in Microsoft Visual Studio to build the extension.

To use the option to publish directly to your Dundas BI instance, run Visual Studio as an administrator before opening the solution. For example, you can right-click Visual Studio in the start menu and find the Run as administrator option.

The solution file is located at:
[Extracted folder]\DistanceTransform\DistanceTransform.sln

3. The project

The project is a class library with the following classes defined:

  • distance_transform_icon.png - This is the icon used in the UI by the custom transform.
  • DistanceMatrix.cs - This creates the web request to Google’s API by passing the parameters required by the service.
  • DistanceTransformPackage.cs - This class contains the extension package information.
  • DistanceTransform.cs - This defines the custom transform.
  • PublishExtensionTemplate.props - Used for auto publishing the extension after the build succeeds, and defines extension properties, and files.

3.1. Web service call

The DistanceMatrix class takes the data from the origin and destination columns and passes it to Google’s Distance Matrix API by creating a web request. The response from the web service includes the calculated distance and travel time.

The following method in this class makes the web service call. It also prevents duplicate requests going out for the same origin-destination pair within a set amount of time, to potential additional expenses from using the service.

/// <summary>
/// General purpose method for calling the Google Maps Distance Matrix API
/// </summary>
/// <param name="apiKey">The Google Maps Distance Matrix API key to use to authorize the call.</param>
/// <param name="units">The measurement system to use.</param>
/// <param name="origin">The starting location to measure from.</param>
/// <param name="destination">The ending location to measure to.</param>
/// <returns>
/// The response to the API call.
/// </returns>
private static DistanceMatrixResponse DistanceMatrixApiCall(string apiKey, string units, string origin, string destination)
    // if any arguments are null, return an empty result
    if (apiKey == null || units == null || origin == null || destination == null)
        return new DistanceMatrixResponse();

    // create an origin,destination pair and check if the cache has a maching result that hasn't expired
    Tuple<string, string> cacheKey = Tuple.Create<string, string>(origin, destination);
    if (DistanceQueryCache.ContainsKey(cacheKey) 
        && (DateTime.Now - DistanceQueryCache[cacheKey].TimeRetrieved).TotalMinutes < CacheDuration)
        // return the cached result
        return DistanceQueryCache[cacheKey].Result;

    // Escape all the arguments for the API call
    units = Uri.EscapeUriString(units);
    origin = Uri.EscapeUriString(origin);
    destination = Uri.EscapeUriString(destination);
    apiKey = Uri.EscapeUriString(apiKey);

    // Create the web request
    WebRequest request = WebRequest.Create(
        "" +
        "units=" + units + "&" +
        "origins=" + origin + "&" +
        "destinations=" + destination + "&" +
        "departure_time=now&" +
        "key=" + apiKey

    // Send the web request and store the results in a string
    WebResponse response = request.GetResponse();
    StreamReader responseReader = new StreamReader(response.GetResponseStream());
    string responseContent = responseReader.ReadToEnd();

    // Parse the response
    DistanceMatrixResponse result = JsonConvert.DeserializeObject<DistanceMatrixResponse>(responseContent);

    // add the result of the call to the cache
    CachedDistanceMatrixResult cacheEntry = new CachedDistanceMatrixResult();
    cacheEntry.TimeRetrieved = DateTime.Now;
    cacheEntry.Result = result;
    DistanceQueryCache[cacheKey] = cacheEntry;

    // return the result
    return result;

3.2. DistanceTransform class

This class defines the custom transform by extending the Transform class. The transform requires two inputs: an Origin and a Destination, and outputs two new columns Distance and Travel Time along with the input columns.

3.3. DistanceTransformPackage class

This class extends the ExtensionPackageInfo2 class, and provides information about the extension package, allowing you to introduce the custom transform.

3.4. Publish extension template

This sample has a mechanism to automatically publish the extension when building, which is the Dundas.BI.PublishExtension NuGet package. When this package is added to the project, it creates a PublishExtensionTemplate.props file containing MSBuild property and item groups, which define how to create and publish the extension.

When the DtFilePath property is set to the file path of the dt tool of a Dundas BI instance, it will then publish the extension directly to that instance when you build the solution. It will also touch the web.config file to force the web application to reset.

If the DtFilePath property is not set, it will create a .zip file you can add to your Dundas BI instance using the Extensions screen in the administration UI. After building the solution with the solution configuration set to Release, this .zip file can be found in the bin\Release\netcoreapp[x] subfolder of your solution. It targets both .NET Framework and .NET Core.

For more details on using this package to automate publishing extensions, see Using the Dundas.BI.PublishExtension NuGet package.

4. Using the transform

Since the transform uses the origin and destination locations to calculate the distance and travel time, you need a dataset that contains this data in two separate columns.

Data that contains the origin and destination locations
Data that contains the origin and destination locations

Create a new data cube and drag your dataset onto the canvas.

After the extension is added to Dundas BI, you should see the Travel Distance transform added to the list of transforms under Insert Other.

Distance transform in the toolbar
Distance transform in the toolbar

Add the Travel Distance transform and connect it to your input data.

Add the Travel Distance transform to the data cube
Add the Travel Distance transform to the data cube

Configure the transform by adding your Google Maps Distance Matrix API key, and setting the Origin Column Name and Destination Column Name to those column names from your input data.

Configure the transform
Configure the transform

The transform calculates the distance and the travel times and outputs them as two new columns in the process result.

Process result after the transform
Process result after the transform

Data Preview showing the transform output
Data Preview showing the transform output

Web services like the one used here may charge per use. To prevent your transform from needing to execute for every request or user, consider using cube storage and scheduling your cube to update periodically.

5. See also

Dundas Data Visualization, Inc.
400-15 Gervais Drive
Toronto, ON, Canada
M3C 1Y8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours:
Phone: 9am-6pm, ET, Mon-Fri
Email: 7am-6pm, ET, Mon-Fri