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. This sample will create a transform in the data cube and use the Google Distance API web service to calculate the distance and travel time between two locations. The resulting values can then be used in data explorations or visualizations created in Dundas BI, like any measure available as part of a data cube process result.

This sample will focus on the steps necessary to create the web service call from your custom transform. If you are new to creating custom transforms, please start by reviewing the article Create a custom transform.

2. Getting started

Once this sample is installed in your instance, you will find a new transform in the data cube designer. This transform requires three properties:

  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.

2.1. Download the sample solution

To download the Custom Transform Sample solution click here.

2.2. Extract the solution to the SDK folder

This sample is designed to automatically publish the extension to your Dundas BI instance. First you must extract the to the SDK folder within the instance. To extract the do the following:

  • Find the SDK folder for your instance. It is located at [instance root]\sdk. For example:
    C:\Program Files\Dundas Data Visualization Inc\Dundas BI\Instances\< instance name >\sdk
  • Extract the to the SDK folder.
  • You should see the following structure:
    [instance root]

    If the sample is not in the exact folder, it will not work correctly.

2.3. Open the solution

To open the Visual Studio solution do the following:

  1. Right click the Microsoft Visual Studio shortcut and click run as administrator.
  2. Click the File Menu, and then Open Solution.
  3. Double-click the solution located at:
    [instance root]\sdk\Samples\CustomTransformSample\CustomTransformSample.sln
  4. Compile and build the solution. The DLL will be created automatically in the following location:
    [instance root]\www\BIWebsite\App_Data\Extensions\GMapsDistanceTransform\bin\GMapsDistanceTransform.dll

The sample is designed to reset the web application after the build succeeds, using PublishExtension.targets. Log on to the Dundas BI instance and create a new data cube. You should see the Google Maps Distance transform added to the list of transforms under Insert Other.

The new Google Maps Distance transform
The new Google Maps Distance transform

3. The project

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

  • GMapsDistanceTransformIcon.png - This is the icon used in the UI by the custom transform.
  • GMapsDistance.cs - This creates the web request to Google’s API by passing the parameters required by the service.
  • GMapsDistanceTransformPackage.cs - This class contains the package information about the extension package.
  • GMapsDistanceTransform.cs - This defines the custom transform.
  • PublishExtension.targets - Used for auto-publishing the extension after the build succeeds.

3.1. GMapsDistance.cs

This is where the GMapsDistanceCalculator class is defined. This class takes the data from the origin and destination columns and passes it to Google’s API along with the API Key by creating a web request. The response from the web service includes the calculation of distance and travel time.

/// <summary>
/// Provides static functions for retrieving data from the Google Maps Distance Matrix API
/// </summary>
class GMapsDistanceCalculator

    // Structure used to store cached results with timestamps to avoid duplicate API calls
    struct CachedGMapsResult
        public GMapsMatrixReturn result { get; set; }
        public DateTime timeRetrieved { get; set; }

    // Result cache which maps origin,destination pairs to timestamped API results to avoid duplicate API calls
    private static Dictionary<Tuple<string,string>, CachedGMapsResult> distanceQueryCache = new Dictionary<Tuple<string,string>, CachedGMapsResult>();

    // Duration to use cached results in minutes
    private static Int32 cacheDuration = 5;

    /// <summary>
    /// General purpose method for calling the Google Maps Distance Transform API
    /// </summary>
    /// <param name="apiKey" />The Google Maps Distance Matrix API key to use to authorize the call.
    /// <param name="units" />The measurement system to use.
    /// <param name="origin" />The starting location to measure from.
    /// <param name="destination" />The ending location to measure to.
    /// <returns>
    /// The resulting GMapsMatrixReturn object.
    /// </returns>
    private static GMapsMatrixReturn gMapsApiCall(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 GMapsMatrixReturn();

        // 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 into a GMapsMatrixReturn object
        GMapsMatrixReturn result = JsonConvert.DeserializeObject<gmapsmatrixreturn>(responseContent);

        // add the result of the call to the cache
        CachedGMapsResult cacheEntry = new CachedGMapsResult();
        cacheEntry.timeRetrieved = DateTime.Now;
        cacheEntry.result = result;
        distanceQueryCache.Add(cacheKey, cacheEntry);

        // return the result
        return result;


This is not the complete code in the file. For the full API structure returned and the calculation for the distance and travel time, see the downloaded solution file.

3.2. GMapsDistanceTransform.cs

This is where the custom transform is defined. The transform defines three inputs: Origin, Destination, and API Key, and outputs the two columns Distance and Travel Time (along with the three input columns). The Distance and Travel Time columns contain values returned from the GMapsDistanceCalculator defined in GMapsDistance.cs.

3.3. GMapsDistanceTransformPackage

This is where the ExtensionPackageInfo class is defined. This class contains the package information about the extension package and will make Dundas BI read the transform.

3.4. PublishExtension.targets

This file overrides the AfterBuild target to automatically publish the extension. Modify the file to reflect the changes for the new transform. This will create the following file after successfully compiling the solution:

[instance root]\www\BIWebsite\App_Data\Extensions\GMapsDistanceTransform\bin\GMapsDistanceTransform.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

Drop the source table on the data cube canvas and add the Google maps Distance transform to it.

Add the Google Maps Distance transform to the data cube
Add the Google Maps Distance transform to the data cube

Configure the transform by adding the Google Maps Distance Matrix API key. The Origin Column Name and Destination Column Name fields should contain the column names in the dataset that these values come from.

Configure the Google Maps Distance transform
Configure the Google Maps Distance 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 after the transform
Data preview after the transform

These measures can now be used for creating visualizations.

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: 7am-6pm, ET, Mon-Fri