Tutorial

Getting Started with the Analytics Extensions API

Difficulty Level
Advanced
Programming Languages
C#

The Tableau Analytics Extensions API provides valuable business insights by integrating your dashboard with third-party or custom tools. Analytics extensions are custom queries that execute outside the Tableau runtime environment, boosting the calculation toolkit. Tableau integrates these external query results with the locally sourced data from a visualization.

 

You can use analytics extensions from a calculated field by calling a particular script function for one of four available data types. In this case, the data type refers to the type of data returned from the analytics extension.

 

Requesting data from an external web API from within a Tableau calculation field enables you to perform analytic operations (calculations) on the data. Additionally, integrating data from a web service into a visualization (Viz) is much easier than adding yet another data source to your Tableau workbook. While Tableau supports an impressively long list of data sources (MySQL, Dropbox, Actian, Matrix, and so on), there are limitations for certain data source combinations. Also, managing the joins between the data sources within one Tableau workbook can be complex. Analytics extensions make this easier.

 

In addition, a web service may offer functions that are not available in Tableau. While the Tableau toolbox of built-in functions satisfies many use-cases, an external web service can leverage other languages and libraries for greater functionality. For example, consider a requirement to display real-time data from publicly available third-party web services or integrate dynamic predictions from a machine learning model. A custom web service can simply wrap the web services and expose them to Tableau as analytics extensions. 

 

Finally, because of security policies, you may not have access to a data source in its raw form. A custom web service may satisfy these security requirements and allow Tableau users to retrieve query results through an analytics extension call.

This tutorial introduces you to the recently released Tableau Analytics Extensions API. We look at the details of this new feature and walk through an example of how you can build your analytics extensions using the C# .NET Core Web API. You can use just about any language with the Analytics Extensions API, as long as you’re running on a system that can expose a REST API. To follow along, check out the full project code.

How the API works

Let’s start with a bird’s-eye view of analytics extensions.

 

 

First, you implement a web service that listens for requests from Tableau. Tableau integrates the web service’s data with its own data sources and applies it to the Viz. This provides a clean separation of concerns between various languages and other third-party web APIs for flexibility.

Tableau sends the script function call information to your custom web service using an HTTP POST request. The web service returns data to Tableau. This data is equal in length to the input data fields or a single value of the data type the script function name specifies. It’s the web service’s job to interpret the expression in the first argument.


The input data field arguments in the calculated field code must be aggregate expressions. You can supply any number of these arguments. When a script command executes inside a calculated field:

  1. Tableau creates a data array for each argument at the Viz’s level of aggregation.
  2. Tableau packages the array with the command expression as a JSON document.
  3. Tableau sends this JSON as the body of one HTTP POST request to a web service.
  4. A web service receives the request and processes the script expression, reading the data arrays to create an equal-sized results data array or a single value.
  5. A web service returns the results array as the response to the original POST request.
  6. Tableau renders the results as applied in the Viz’s calculated field.

You can implement analytics extensions in any language that supports listening and responding to HTTP requests.

The analytics extensions functions define the expected return type of the data:

  • SCRIPT_BOOL
  • SCRIPT_INT
  • SCRIPT_REAL
  • SCRIPT_STR

Here is an example use of the SCRIPT_REAL function:

  SCRIPT_REAL("_arg1 * _arg2", [List Price], [Quantity])

This is a call to a built-in function called SCRIPT_REAL. It passes an expression that says to multiply the first item in _arg1 by the first item in _arg2, the second item in _arg1 by the second item in _arg2 and so on until complete. It also specifies two data fields, List Price and Quantity, as those arguments.

Anatomy specifics

When you design or refresh a Viz, Tableau sends a POST request to an endpoint named evaluate on your web service to fetch data. Your web service must take specific action based on the string expression you specify in the first argument of a script function call.

Analytics extension examples

Using the C# .NET Core Web API, the following example project demonstrates three simple analytics extensions. We’ll walk through the steps to create this project using Microsoft Visual Studio Code.

First, let’s see what Tableau can visualize using these extensions. The Tableau reports in this tutorial use Microsoft’s AdventureWorks 2016 database.

This Viz compares real-time shipping costs by country and postal code.

 

This calculated field generates the Shipping Costs:


  SCRIPT_REAL("Shipping", ATTR([Country Region Code]), ATTR ([Postal Code]))

The next sample displays real-time product trends in the form of Exposure Per Hour:

 

 

This calculated field generates the Exposure Per Hour:

 SCRIPT_REAL("Trending", ATTR([Subcategory]))

 

The following sample displays product prices in different currencies in real-time.

 

 

The following calculated field generates the List Price (JPY). A similar calculated field generates the List Price (USD).

SCRIPT_REAL("Currency:JPY", ATTR(1)) * ATTR([List Price (EUR)])

These samples should inspire some ideas of how you can use analytics extensions. Now let’s look at a web API implementation using the .NET Core Web API framework.

Web API project overview

This demo Web API application listens to the two endpoints Tableau requires, which are evaluate and info, and calls an external Web API in one of its queries to demonstrate a realistic example. This project implements two other queries using only simulated data.

 

The evaluate endpoint provides the Tableau clients with Viz data. The info endpoint informs the clients of metadata about your API such as what authentication it requires, its name, and version.

 

This Web API uses Basic authentication, an optional Tableau connection setting. You can use HTTPS, but note that Tableau Online does not accept a self-signed certificate, only certificates from a certificate authority (CA). Generating a self-signed developer certificate is outside the scope of this Web API project.


Here is our demo project’s layout:

 

Let’s go through the specific steps to create it.

 

Basic setup

First, open Visual Studio Code or your favorite integrated development environment (IDE) to a new folder location. Be sure that you have the .NET SDK installed and the C# extensions if you are using Visual Studio Code. In a terminal, type the following commands:

 dotnet new webapi -o AeWebApiDemo

 del .\AeWebApiDemo\Controllers\WeatherForecastController.cs

 del .\AeWebApiDemo\WeatherForecast.cs

The first command creates a Web API project inside our current directory. Next, we delete the default Web API sample classes, as they are not required.

 

We must also make a few changes to the default programs.cs file. Add the following using statement:

 using Microsoft.Extensions.DependencyInjection;

This allows us to use Dependency Injection to decouple class instantiation from consumption during our web app’s startup process.


Then, update CreateHostBuilder by adding a call to ConfigureServices and AddHttpClient as shown below:

  public static IHostBuilder CreateHostBuilder(string[] args) =>
           Host.CreateDefaultBuilder(args)
               .ConfigureServices((context, services) =>
               {

                   services.AddHttpClient();

               })
               .ConfigureWebHostDefaults(webBuilder => {
                 webBuilder.UseStartup<Startup>();
               });

This enables controller classes to use IHttpClientFactory to make external API calls.

Set up authentication

We must create some classes to handle Basic authentication for Tableau connections. This demo app uses a type filters approach for authentication instead of a middleware approach for added flexibility.

Create a class called BasicAuthAttribute in a folder called TypeFilters:

[AttributeUsage(AttributeTargets.Class | AttributeTargets.Method)]
  public class BasicAuthAttribute : TypeFilterAttribute {
   public BasicAuthAttribute() : base(typeof(BasicAuthFilter)) { }
  }

Then, create a class called BasicAuthFilter:

public class BasicAuthFilter : IAuthorizationFilter {
   private const string Realm = "My Realm";
   public void OnAuthorization(AuthorizationFilterContext context) {
       try {
           string authorizationHeader =
               context.HttpContext.Request.Headers["Authorization"];
           if (authorizationHeader != null) {
               var authHeaderValue =   AuthenticationHeaderValue.Parse(authorizationHeader);
               if (AuthenticationSchemes.Basic.ToString() == authHeaderValue.Scheme) {
                   var bytes = Convert.FromBase64String(authHeaderValue.Parameter ??
                       string.Empty);
                   var usernameAndPassword =   Encoding.UTF8.GetString(bytes).Split(':', 2);
                   if (usernameAndPassword.Length == 2) {
                       var username = usernameAndPassword[0];
                       var password = usernameAndPassword[1];

                       if (IsAuthorized(context, username, password)) {
                           return;
                       }
                   }
               }
           }
           ReturnUnauthorizedResult(context);
       } catch (FormatException) {
           ReturnUnauthorizedResult(context);
       }
   }

   public bool IsAuthorized(AuthorizationFilterContext context, string username, string
       password) {
       return username == "test" && password == "123";
   }

   private void ReturnUnauthorizedResult(AuthorizationFilterContext context) {
       // Return 401 and a basic authentication challenge
       context.HttpContext.Response.Headers["WWW-Authenticate"] = $"Basic
           realm=\"{Realm}\"";
       context.Result = new UnauthorizedResult();
   }

  }

This authentication filter uses a hard-coded username and password for simplicity (test and 123, respectively). Replace this code in the IsAuthorized method with a real authentication check for production. Now you can protect endpoint methods in your project by adding the BasicAuth attribute.

Info endpoint

Tableau requires your Web API to implement an endpoint named info that accepts an HTTP GET request. The controller class for this endpoint is:

[Route("[controller]")]
  [ApiController]
  public class InfoController : ControllerBase {
      private IWebHostEnvironment environment;
 
      public InfoController(IWebHostEnvironment _environment) {
          environment = _environment;
      }
 
      [HttpGet]
      [Produces("application/json")]
      [BasicAuth]
      public async Task<ActionResult<JsonDocument>> GetInfo() {
          var path = $"{environment.ContentRootPath}\\tableau-extension-info.json";
          var info = await System.IO.File.ReadAllTextAsync(path);
          var jsonDoc = JsonDocument.Parse(info);
          return Ok(jsonDoc);
      }
  }

Notice how this method uses the BasicAuth attribute, which we created earlier. This method returns a JSON document named tableau-extension-info.json that describes our analytics extension. This document is located at the root of our project folder which is specified by the environment.ContentRootPath variable in the code. Here are the contents of our document:

{
      "description": "This is a demo server for Tableau Analytics Extensions.",
      "creation_time": "0",
      "state_path": "e:\\dev\\server",
      "server_version": "0.0.1",
      "name": "Demonstration Server",
      "versions": {
          "v1": {
              "features": {
                  "authentication": {
                      "required": true,
                      "methods": {
                          "basic-auth": {}
       …
  }

This document specifies the type of authentication, a description for your extension, and a version number.

Evaluate endpoint

Tableau gets data from your Web API by sending an HTTP POST request to an endpoint on your API named evaluate. This request’s body has the following structure:

POST /Evaluate
  {
          "script": "script contents",
          "data": {
                  "_arg1": ["val1", " val1", " val1"],
                  "_arg2": ["valA", " valB", " valC"]
          }
  }

Here is an example request that gets the shipping rates for three postal codes in three different countries:

POST /Evaluate
  {
          "script": "Shipping",
          "data": {
                  "_arg1": ["USA", "AU", "CA"],
                  "_arg2": ["22182", "5020", "G1R"]
          }
  }

Again, Tableau generates this request when it runs a calculated field script. For example:

SCRIPT_REAL("Shipping", ATTR([Country Region Code]), ATTR([Postal Code]))

The first argument of the SCRIPT_REAL command corresponds to the script tag contents in the HTTP request. The two command arguments, fields Country Region Code and Postal Code, correspond to an array of country and postal code values passed as _arg1 and _arg2 attributes. The number of _arg attributes in the JSON document is equal to the number of arguments passed to the SCRIPT_ based commands.

Your Web API is responsible for interpreting and responding to the request Tableau sends to the evaluate endpoint. In this example, the script tag content, Shipping, is the name of a real-time query in our web service that retrieves simulated shipping rates.

Big picture strategy

We should step back for a moment and look at the big picture. There are two fundamental approaches to analytics extensions. One is to simply execute the script tag’s contents as an existing scripting language in each index of the data array arguments. A few implementations like this already exist, such as TabPy.

Alternatively, you can implement a domain-specific language (DSL) to link a script command to a ready-made feature or query inside your Web API application. In this case, you can think of your Web API application as a vehicle or platform for software developers to continually add new features for Tableau users to call by name.

Each feature or query should have the following requirements. First, it must be identifiable so users can reference it inside a calculated field script. Second, each query may need to declare the number and type of data field arguments it expects to receive. Third, a query might also offer some options for its execution.

This example web service implementation does all this.

Let’s start with the evaluate endpoint method. Our method performs the following steps:

1.      Receives a web request.

2.      Creates a command.

3.      Executes the command.

4.      Returns the results of the command to Tableau.

Design suggestion: segregate the code that handles the script and data processing from the HTTP POST evaluate endpoint method. Here is the method’s code:

[HttpPost]
  [BasicAuth]
  public async Task<ActionResult<object[]>> Post([FromBody] JsonElement request) {
      //Tableau Authentication/Settings requests this evaluation as a "ping" test.
      if (request.GetProperty(TableauKeywords.Script).GetString() == "return
  int(1)") {
          return Ok(new object[1] { 1 });
      }
 
      var command = TableauCommand.GetFromJsonElement(request);
      command.Query.HttpClientFactory = httpClientFactory;
      var results = await command.ExecuteAsync();
      return Ok(results);
  }  

Notice that there is an if statement stuck at the beginning that provides a [1] response if the script equals the following value: return int(1). Tableau’s connection settings dialog window calls the evaluate endpoint with this script during connection setting validation when you click the Test or OK button in the Analytics Extension Connection dialog window. If your service does not return this value properly, then authentication fails.

Also, the constructor accepts an IHttpClientFactory, which provides the commands with the ability to call other web services.

Before diving into the details of processing requests, here are a few helper classes for working with the Tableau JSON request and other objects.

The TableauKeywords class references the JSON attributes of the HTTP POST request.

 public static class TableauKeywords {
      public static string Script = "script";
      public static string Data = "data";
      public static string Arg(int i) => $"_arg{i + 1}";
  }

The TableauData class maps data argument names (for example, _arg1 and _arg2) to their corresponding data.

  public class TableauData : Dictionary<string, TableauDataField> { }

The TableauDataField class is a data model for query classes.

public class TableauDataField {
      double[] doubleData;
         public double[] DoubleData => doubleData;
         string[] stringData;
         public string[] StringData => stringData;
         int[] intData;
         public int[] IntData => intData;
       public TableauDataField(double[] doubleData) {
        this.doubleData = doubleData;
      }
      public TableauDataField(int[] intData) {
          this.intData = intData;
         }
       public TableauDataField(string[] stringData) {
         this.stringData = stringData;
         }
}

The IQuery interface defines query behavior.

  public interface IQuery {
          Task<object[]> ExecuteAsync(TableauData data);
          bool HasOption { get; }
          Type[] Types { get; }
          IHttpClientFactory HttpClientFactory { get; set; }
  }

The QueryBase class provides a common class for queries.
  public class QueryBase {
          private string name;
          public string Name => name;
          private bool hasOption;
          public bool HasOption => hasOption;
          private Type[] types;
          public Type[] Types => types;
          public string Option { get; set; }
          public IHttpClientFactory HttpClientFactory { get; set; }
 
          public QueryBase(string name, bool hasOption, params Type[] types) {
              this.name = name;
              this.hasOption = hasOption;
              this.types = types;
          }
      }

Now, let’s look at some example query classes that process data and generate results.

Real-time shipping costs

The method below determines the shipping cost for some given country and region codes. It returns a costs array that is the same size as the input arrays. Notice that this class specifies two string types in the call to the base class constructor that corresponds to the two input arguments, _arg1 and ­_arg2.

public class ShippingRateQuery : QueryBase, IQuery {
      public ShippingRateQuery() : base("ShippingRate", hasOption: false,
              typeof(string), typeof(string)) { }
 
      async public Task<object[]> ExecuteAsync(TableauData data) {
          //country and region code
          var arg1Name = TableauKeywords.Arg(0);
          var arg2Name = TableauKeywords.Arg(1);
          if (!data.ContainsKey(arg1Name) || !data.ContainsKey(arg2Name)) {
              throw new MissingFieldException("Shipping Rate Query cannot run. "
              + "Some fields are missing.");
          }
 
          //arg data
          var countryCodes = data[arg1Name].StringData;
          var postalCodes = data[arg2Name].StringData;
          var results = new object[countryCodes.Length];
 
          var r = new Random();
 
          for (var i = 0; i < results.Length; i++) {
              //simulate changing shipping rates.
              var hashOfNames = (countryCodes[i], postalCodes[i]).GetHashCode();
              var modOfHash = hashOfNames % 50;
              var randomNumber = r.Next(1, 100) / 10d;
              var shippingRate = 100 + modOfHash + randomNumber;
              results[i] = shippingRate;
          }
 
          return await Task.FromResult(results);
      }
  }

Notice that var countryCodes = data[arg1Name].StringData; sets the size of the results array using the size of the countryCodes or _arg1 array input arguments. While this implementation simulates the cost data, public APIs offer real-time costs. You can integrate one into this solution with a little more work.

Real-time currency conversion

This example query returns the real exchange rates between the Euro and a currency type that users specify in a calculated field script. This implementation uses one of many real public APIs providing currency exchange rates. The free, but limited, subscription service is good enough for development purposes.

Third-party API services typically require you to create an account and supply an access key in your API calls to their service. Notice that we provide one in the code below that is used in our sample.

<ace width="100%" syntax="C#">var request = new HttpRequestMessage(HttpMethod.Get,
              $"https://api.exchangeratesapi.io/latest?access_key=[your 
  key]&base=USD");

Remember, your analytics extension must return the same number of items as the input array or a single value. This query returns a single value for the currency exchange rate. Note that this class does not take any input data since it does not specify any types in its call to the base class.

public class CurrencyQuery : QueryBase, IQuery {
      public static string Currency = nameof(Currency);
      public CurrencyQuery(string option) : base(CurrencyQuery.Currency,
  hasOption: true) {
          base.Option = option;
      }
 
      async public Task<object[]> ExecuteAsync(TableauData data) {
          var currencyOption = base.Option;
 
          var client = HttpClientFactory.CreateClient("Currency Query Client");
          var request = new HttpRequestMessage(HttpMethod.Get,
              $"https://api.exchangeratesapi.io/latest?access_key=[your 
  key]&base=USD");
          var response = await client.SendAsync(request);
 
          if (response.IsSuccessStatusCode) {
              var responseString = await response.Content.ReadAsStringAsync();
              Console.WriteLine(responseString);
              var currencyJson = JsonDocument.Parse(responseString);
              var jsonRates = currencyJson.RootElement.GetProperty("rates");
              if (jsonRates.TryGetProperty(currencyOption, out var rateJson)) {
                  var rate = rateJson.GetDouble();
                  return await Task.FromResult(new object[1] { rate });
              } else {
                  throw new HttpRequestException("Currency Request Failed: "
                  + $"{currencyOption} not found.");
              }
          } else {
              throw new HttpRequestException("Currency Request Failed: "
              + $"StatusCode: {response.StatusCode}");
          }
      }
  }

Notice that this class uses the option value described earlier. The class uses the option value to find the specific currency rate. You specify the option as part of a script expression for this query in a calculated field. The options syntax in our DSL is:

 "<query-name>[:<option>]"

You can use this extension to multiply the Euros to Yen currency exchange rate with the List Price using the following command to calculate the price in Yen:

 SCRIPT_REAL("Currency:JPY", ATTR(1)) * ATTR([List Price])

Real-time trending

The next example query returns the exposure per hour for given products, which is a trending hashtag measure available in a public API. Again, note how this class declares its argument types in a call to its base class. The string type corresponds to the product name argument.

public TrendingQuery() : base("Trending", hasOption: false, typeof(string))
  { }
 
          async public Task<object[]> ExecuteAsync(TableauData data) {
              //trending search term
              var arg1Name = TableauKeywords.Arg(0);
              if (!data.ContainsKey(arg1Name)) {
                  throw new MissingFieldException("Trending Query cannot run. Search
term
                         field is missing.");
              }
 
              //arg data
              var searchTerms = data[arg1Name].StringData;
              var results = new object[searchTerms.Length];
 
              var r = new Random();
              for (var i = 0; i < results.Length; i++) {
                  //simulate changing exposure per hour.
                  var hashOfNames = searchTerms[i].GetHashCode();
                  var modOfHash = hashOfNames % 50;
                  var randomNumber = r.Next(1, 100000);
                  var shippingRate = 10 + modOfHash + randomNumber;
                  results[i] = shippingRate;
              }
 
              return await Task.FromResult(results);
          }
      }

Next, we look at how our TableauCommand class connects the JSON request with the query classes.

Tableau command

Our TableauCommand class models the Tableau command request and coordinates the calls to the query classes.

public class TableauCommand {
          public string Script { get; set; }
          public TableauData Data { get; set; }
          public IQuery Query { get; }
 
          public TableauCommand(string script, IQuery query, TableauData data) {
              Script = script;
              Query = query;
              Data = data;
          }
 
          async public Task<object[]> ExecuteAsync() => await 
  Query.ExecuteAsync(Data);
 
          public static TableauCommand GetFromJsonElement(JsonElement json) {
              var script = json.GetProperty(TableauKeywords.Script).ToString();
              var dataJson = json.GetProperty(TableauKeywords.Data);
              var query = QueryFactory.GetQuery(script);
              var tableauData = new TableauData();
              for (var i = 0; i < query.Types.Length; i++) {
                  var argName = TableauKeywords.Arg(i);
                  var argJson = dataJson.GetProperty(argName);
                  var argType = query.Types[i];
                  var dataField = CreateTableauDataField(argJson, argType);
                  tableauData.Add(argName, dataField);
              }
 
              return new TableauCommand(script, query, tableauData);
          }
 
          private static TableauDataField CreateTableauDataField(JsonElement argJson,
                          Type argType) {
              if (argType == typeof(string)) {
                  var data = argJson.EnumerateArray().Select(x =
  x.GetString()).ToArray();
                  var dataField = new TableauDataField(data);
                  return dataField;
              } else if (argType == typeof(int)) {
                  var data = argJson.EnumerateArray().Select(x =>
  x.GetInt32()).ToArray();
                  var dataField = new TableauDataField(data);
                  return dataField;
              } else if (argType == typeof(double)) {
                  var data = argJson.EnumerateArray().Select(x =>
  x.GetDouble()).ToArray();
                  var dataField = new TableauDataField(data);
                  return dataField;
              } else {
                  throw new NotSupportedException($"Type: {argType} is not supported "
                  + "as a data field.");
              }
          }
      }

Query factory

The last class we’ll look at is the QueryFactory. This is responsible for mapping the query names in the script argument to their corresponding class implementation, a quick and simple approach to linking. A more robust approach would involve placing custom attributes on the query classes.

 public static class QueryFactory {
       public static IQuery GetQuery(string script) {
           var (name, option) = GetQueryNameAndOption(script);
           return name switch
           {
               "Currency" => new CurrencyQuery(option),
               "Shipping" => new ShippingRateQuery(),
               "Trending" => new TrendingQuery(),
               _ => throw new NotImplementedException()
           };
       }


       private static (string name, string option) GetQueryNameAndOption(string
  script) {
           var parts = script.Split(':');
           return (parts[0], parts.Length > 1 ? parts[1] : null);
       }
   }

 

Implementation summary

Our example web service implementation is a lightweight framework that can help you start developing your own analytics extensions for Tableau. You could also create a formal DSL with a context-free grammar and use a parser-generator to give Tableau users a more robust scripting capability.

Tableau connection setup

Once all the code is in place, run the web service by pressing F5. Try connecting to it from Tableau. To do this, select Help > Settings and Performance > Manage Analytics Extension Connection.

Select TabPy/External API from the drop-down menu, then enter the connection information. Again, our username and password for this example are test and 123. Do not select Require SSL.

 

 

When you press Test Connection, you see this window.

 

 

If there is a mistake, this window.

 

 

If the connection test is unsuccessful, this list of possible reasons will help you troubleshoot:

  • The authentication tag is not set properly in your tableau-extensions-info.json file.
  • Your evaluate endpoint method does not return a single value of “1” in response to a script value equal to this string: return int(1).
  • Your evaluate and info endpoint methods are missing the BasicAuth attribute.
  • The IP address, hostname, or port number is incorrect.

Next steps

In this tutorial, you’ve experienced how easy it is to create Tableau analytics extensions with a lightweight framework in C#. You’ve learned details of the HTTP requests between Tableau and a web service supplying the extensions. And you’ve experienced how user-created calculated field scripts drive data flow to and from extensions.

 

You’ve also seen visualizations using our sample extensions and learned the benefits of using analytics extensions compared to traditional data source connections and the built-in Tableau operations.

 

Now that you know more about using the Analytics Extensions API, you can incorporate these data sources into your Tableau dashboard or create your own. Dive in and explore the rest of Tableau Developer tools.

Last updated: September 13, 2021