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

Once this sample is installed in your instance, you will find a new transform in the toolbar when editing a data cube. This transform requires three settings:

  1. Your Google Maps Distance Matrix API key. You can obtain one here:

  2. The name of the column in the data with the origin locations.
  3. The name of the column in the data with the destination locations.

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. Extract the solution to the SDK folder

This sample is designed to automatically publish the extension to your Dundas BI instance. First, extract to the SDK folder within the instance:

2.3. Open the solution

To open the Visual Studio solution:

  1. Right-click the Microsoft Visual Studio shortcut, and click Run as administrator.
  2. Click the File menu, and then Open Solution.
  3. Choose the solution located at:
    [Extracted folder]\DistanceTransform\DistanceTransform.sln

After the sample is compiled and added to Dundas BI. Log on to the Dundas BI instance and create a new data cube. 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

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. PublishExtension.targets

This file overrides the AfterBuild target to automatically publish the extension. Modify this file if you make changes to file names, etc. This will create the following file after successfully compiling the solution:

[instance root]\www\BIWebsite\App_Data\Extensions\DistanceTransform\bin\DistanceTransform.dll

It will then touch the web.config to force the web application to reset.

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

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.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

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