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: https://developers.google.com/maps/documentation/distance-matrix/get-api-key
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 DistanceTransform.zip 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(
"https://maps.googleapis.com/maps/api/distancematrix/json?" +
"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.
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.
Add the Travel Distance transform and connect it to your input data.
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.
The transform calculates the distance and the travel times and outputs them as two new columns in the process result.
