Interfacing Mathematica with Tableau desktop

Question

Are there any known methods or examples of connecting Mathematica with Tableau in any fashion?

I'll accept any solution that demonstrates a method of either:

1. Calling Tableau from within Mathematica
2. Or programmatically generating Tableau (interactive or static) graphics from Mathematica

I'm looking for something like RLink/MATLink or LibraryLink or some sort of ExternalEvaluation approach.

What I've tried:

• I've tried using an ExternalSession approach through python's tabpy packages, but it didn't work mostly because of lacking documentation in how to setup and use it.

• Another approach didn't try yet is to use Tableau's extension api

References:

Here are all the helpful links I've found so far:

• Notes on using tableau with python:
• Older blog about using tableau with python:
• Docs on using tableau with javascript: https://help.tableau.com/current/api/js_api/en-us/JavaScriptAPI/js_api.htm

Answer 1

This answer is a copy of my Mathematica and Tableau integration GitHub repository. Before you start reading I should mention that even when you are using tabpy, you can only run python code inside Tableau and nothing more. The idea of controlling Tableau Desktop from the outside is a great idea but I along with many others couldn't find any useful information (Stack, Reddit, ...). The only way I find is provided by Allan Thompson posted on linekedin which basically is manipulating Tableau twb file (has XML structure).

Below I'll discuss how you can run Mathematica code inside Tableau or send data from Mathematica to Tableau. If you are interested, I also created a version for Power BI which you can use Mathematica plots and visualizations inside Power BI (answer for your second question inside Power BI). Check out Power BI & Mathematica Integration.

Codes are at the end of the answer.

Warning: Since Mathematica code uses the ToExpression function which can easily be manipulated to harm your computer, only run and open script files that you trust.

Mathematica and Tableau integration

Tableau 10.3, introduce a feature to integrate python with tableau through a library named tabpy. As of version 2021.1, tableau works with external services such as R, Python, and Matlab. Now you can use the power of Mathematica inside Tableau. This answer consists of two section:

• Using server.nb to run Mathematica code inside Tableau (Mathematica should be installed)
• Using web_data_connector.nb to send data directly from Mathematica to Tableau

How to run Mathematica in Tableau

When you run tabpy, it will run a local server that evaluates each request which has python code with the given data and returns back the result. The same procedure can happen in Mathematica with help of SocketListen, we could run a local server and by defining a function that encodes the request, apply ToExpression to them and returning the result.

How to send Mathematica data to Tableau

Consider a JSON file like http://sample.com/file.json, as tableau 2021.1, there is no way to send that file directly to Tableau. Tableau has its own way to handle data from the web called Web Data Connector. In simple terms, you should run some JavaScript code before you handing the data to Tableau. With the help of Mathematica SocketListen we could run a server and mimic a web page to send data directly from Mathematica to Tableau.

Run Mathematica code inside Tableau

1 - Run server.nb code. The code Automatically runs on port 36000. You could change that to any number as long as it's accessible.

2 - Setup Tableau by going to Help > Setting and Performance > Manage Analytics Extension Connections.... Select Tabpy/External API, change Server to localhost or 127.0.0.1 and port to 36000:

3 - Now just like tabpy and running python, you can run Mathematica. Create a Calculated Field in Tableau and use Tableau's SCRIPT_REAL() or other SCRIPT_SOMETHING(). Keep in mind:

• Unlike python, there is no need to use Return
• You can access Tableau's expressions in the code by using arg1 for the first argument, arg2 for the second, and so on
• Since the kernel is the same that runs your notebook, you have access to all the functions and variables you'd in your notebook

Compare to Python - Example 1

Objective: increase the given price by one:

If you want to do it in python with tabpy, you should run this code in Tableau's Calculated Field:

SCRIPT_REAL("return [i+1 for i in _arg1]",SUM([Price]))

Mathematica equivalent:

SCRIPT_REAL("arg1+1",SUM([Price]))

Clustering data - Example 2

Here we'll use Mathematica capabilities to cluster price and quantity:

SCRIPT_REAL("ClusteringComponents[Transpose[{arg1,arg2}]]",SUM([Price]),SUM([Quantity]))

Terminating the Server

After you'd done your work, run the following code in Mathematica to close the connection and shut down the server:

Close[server["Socket"]]
DeleteObject[server]

Possible Issues

If your data depend on very small decimals like 10^-9, you might see a little difference between Mathematica calculation and Tableau. Generally, Mathematica will evaluate your code up to 20 digits in decimal but transferring these numbers to Tableau and storing them may distort them by a very little amount.

For example, I have sample sales data with 3 columns product, quantity, and price. The goal is to calculate the average sales by multiplying the sum of quantity with the average of price for each product.

Mathematica code:

SCRIPT_REAL("arg1*arg2",SUM([Quantity]),AVG([Price]))

Tableau code:

SUM([Quantity])*AVG([Price])

Here are the differences between the two columns:

Load Mathematica data in Tableau

If you want to send dynamic data directly to Tableau without saving it on disk, then this section will help you but beware that loading data with this solution is slower than reading a static file.

1 - Run web_data_connector.nb code

2 - Send your data with the sendToTableau function, keep in mind:

• Because of jquery and tableauwdc JavaScript libraries, you and tableau should be able to connect to the internet
• your data should be a 2-dimensional array
• supported data types are: Real, Integer, Boolean, String, Date, null (Missing in Mathematica)
• Missing[] values in data will convert to null
• if no Headers exists, column names for your data automatically generated as C1 for the first column, C2 for the second, and ...

data = Table[{Now, RandomReal[], RandomChoice[{True, False}], RandomInteger[10], "Test"}, 4];
server1 = sendToTableau[data]

2 - In Tableau, Data > New Data Source > Web Data Connector. In the URL section type localhost:37000 or 127.0.0.1:37000

3 - When the page loaded, click on Click here to load

4 - From now, you can use the Refresh button to get the newer version of the data

Sample Result (as shown null also supported, Missing in Mathematica):

Settings

You should change the following code inside SocketListen, but make sure to terminate the server before re-evaluating the code:

(* automatically generated column names *)
(* will use {"C1","C2","C3","C4","C5"} *)

server1 = setupTableauConnector[data]

Your list of names should be the same length as the first row of your data:

(* specify column names *)

server1 = setupTableauConnector[data, "Headers"->{"Column 1", "Column 2", "Column 3", "Column 4", "Column 5"}];

Changing the port with:

(* default port: 37000 *)

server1 = setupTableauConnector[data,"Port"->37500];

Change port and specify column names:

server1 = setupTableauConnector[data,"Headers"->{"C1","C2","C3","C4","C5"},"Port"->37500];

Terminating the Server

After you'd done your work, run the following code in Mathematica to close the connection and shut down the server:

Close[server1]

Possible Issues

Tableau Web Data Connector is built to connect to stable addresses, for example on Tableau 2020.1 which I tested, if you use this method and connect your data via some port, after closing your file, every time you open the file, Tableau tries to connect to the same port and doesn't let you change it unless it connects to that port once. Sometimes that port is in use by another program and you can't use that. The solution is to run on a different port, then open your Tableau file in a text editor, search for the previous port and replace it with the newer one.

Code

server.nb code:

parse[text_String] := 
 Module[{output, 
   json = ImportString[Last@StringSplit[text, "\r\n"], "RawJSON"]},
  
  If[MissingQ@json["data"], json["data"] = <||>];
  
  (* remove underscore in variable names in script *)
  json["script"] = 
   StringReplace[json["script"], 
    Normal@AssociationMap[StringReplace[#, "_" -> ""] &, 
      Keys@json["data"]]];
  
  (* remove underscore in variable names *)
  json["data"] = KeyMap[StringReplace[#, "_" -> ""] &, json["data"]];
  
  (* set precision for all the real numbers *)
  json["data"] = 
   json["data"] /. 
    x_Real :> 
     ToString@
      NumberForm[x, DefaultPrintPrecision -> 40, 
       ScientificNotationThreshold -> {-40, 40}];
  
  output = 
   ToExpression[
    StringRiffle[#, {"With[{", ",", "},"}, "="] &@(List @@@ 
        Normal@json["data"]) <> json["script"] <> "]"];
  
  Return[output];
  ]

exporter[data_] := 
  ExportString[data, "JavaScriptExpression", "Compact" -> True];

server = SocketListen[36000, Function[{assoc},
    Module[{client = assoc["SourceSocket"], answer, output, evalData1,
       evalData2, success},
     
     (* print each request *)
     Print[assoc];
     
     If[StringStartsQ[assoc["Data"], "POST /evaluate"],
      
      (* evaluate script *)
      evalData1 = EvaluationData[answer = parse@assoc["Data"];];
      
      (* if script successfully executed but contains symbols *)
      If[evalData1["Success"] && 
        Not@MissingQ@FirstPosition[answer, _Symbol, Heads -> False], 
       evalData1 = <|"Success" -> False, 
         "MessagesText" -> {"Result contains symbols."}|>];
      
      (* if script face problem (either in executaion or contains \
symbols) *)
      If[Not@evalData1["Success"], 
       answer = <|"message" -> "Error processing script", 
         "info" -> StringRiffle[evalData1["MessagesText"], "\n"]|>];
      
      
      (* export output *)
      evalData2 = EvaluationData[answer = exporter@answer;];
      
      (* final success is the result of successfuly executing and \
exporting the output *)
      success = evalData1["Success"] && evalData2["Success"];
      
      (* if script faild to execute or export *)
      If[Not@success, 
       answer = 
        "{\"message\":\"Error exporting script \
output\",\"info\":\"Output expression cannot be exported.\"}"];
      
      ,
      (* if any url except /evaluate requested *)
      success = True; 
      answer = 
       exporter@<|
         "description" -> "Server is running on Mathematica.", 
         "creation_time" -> "0", 
         "state_path" -> $InstallationDirectory, 
     "server_version" -> ToString@$VersionNumber, 
         "name" -> "Mathematica"|>];
     
     output = "HTTP/1.1 " <> If[success, "200", "500"] <> " OK" <>
       "\r\nServer: Mathematica/" <> ToString@$VersionNumber <>
       "\r\nContent-Type: application/json" <>
       "\r\nDate: " <> DateString[TimeZone -> 0] <> " GMT" <>
       "\r\nContent-Length: " <> ToString@StringLength@answer <>
       "\r\nVary: Accept-Encoding\r\n\r\n" <> answer;
     
     WriteString[client, output];
     
     (* print response *)
     Print[answer];
     ]
    ]];

server["Socket"]

web_data_connector.nb code:

generateCols[data_List, headers_List : {}] := 
 Module[{types, dtypes, DateTimeObject, firstRow},
  
  firstRow = First@data;
  
  types = {Real -> "tableau.dataTypeEnum.float", 
    Integer -> "tableau.dataTypeEnum.int",
    String -> "tableau.dataTypeEnum.string",
    DateObject -> "tableau.dataTypeEnum.date",
    DateTimeObject -> "tableau.dataTypeEnum.datetime",
    Symbol -> "tableau.dataTypeEnum.bool"
    };
  
  dtypes = 
   If[Head@# === DateObject, 
      If[Length@#[[1]] > 3, DateTimeObject, DateObject], Head@#] & /@ 
    firstRow;
  
  Return@StringRiffle[#, {"[", ",", "]"}] &@MapIndexed[
    StringTemplate["{id: \"`id`\",dataType: `type`}"][<|"type" -> #1, 
       "id" -> headers[[#2[[1]]]]|>] &, # /. types & /@ dtypes]
  ]


generateHTML[data_, headers_List : {}] := 
 Module[{tHeaders, tdata, firstRow, output},
  tHeaders = headers;
  firstRow = First@data;
  
  (* convert data\datetime to javascript type *)
  tdata = 
   data /. {d_DateObject :> 
      StringRiffle[d[[1]], {"new Date(", ",", ")"}], _Missing -> 
      "null"};
  
  (* if headers length doesnt match data length, 
  generate header names *)
  If[Length@firstRow > Length@tHeaders, 
   tHeaders = Array["C" <> ToString@# &, Length@firstRow]];
  
  output = StringTemplate["<!DOCTYPE html>
<html lang=\"en\">
<head>
<title></title>
<meta http-equiv=\"Cache-Control\" content=\"no-store\" />
<meta charset=\"UTF-8\">
<script src=\"https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/\
jquery.min.js\" type=\"text/javascript\"></script>
<script src=\"https://connectors.tableau.com/libs/tableauwdc-2.3.\
latest.js\" type=\"text/javascript\"></script>
<script>
 (function() {
    
    var myConnector = tableau.makeConnector();
  
    myConnector.getSchema = function(schemaCallback) {
        var cols = `cols`;
        var tableSchema = {
            id: \"mathematica\",
            columns: cols
        };
        schemaCallback([tableSchema]);
    };
    myConnector.getData = function(table, doneCallback) {
        table.appendRows(`table`);
        doneCallback();
    };
    tableau.registerConnector(myConnector);
   $(document).ready(function () {
$(\"#submitButton\").click(function () {
        tableau.connectionName = \"Mathematica\";
        tableau.submit();
    });
});
 })();
</script>
</head>
<body>
<button id=\"submitButton\">Click here to load</button>
</body>
</html>"][<|"cols" -> generateCols[data, tHeaders], 
     "table" -> 
      ExportString[
       AssociationThread[tHeaders[[;; Length@#]], #] & /@ tdata, 
       "JavaScriptExpression", "Compact" -> True]|>];
  
  (* remove double quotes around javascript generated date type *)
  Return@StringReplace[
    output, {"\"new Date(" ~~ Shortest[d__] ~~ ")\"" :> 
      "new Date(" <> d <> ")", "\"null\"" -> "null"}];
  ]


setupTableauConnector[data_, options___] := Module[{headers, port},
   
   {headers, port} = 
    Values[Association[options][[{Key["Headers"], Key["Port"]}]]];
   
   If[MissingQ@headers, headers = {}];
   If[MissingQ@port, port = 39000];
   
   (* close any existing port runned by mathematica *)
   Close@SelectFirst[Sockets[], #["DestinationPort"] == port &] // 
    Quiet;
   
   Return[SocketListen[port, Function[{assoc},
       Module[{client = assoc["SourceSocket"], answer, output, 
         evalData1, evalData2, success},
        
        answer = generateHTML[data, headers];
        
        output = "HTTP/1.1 200 OK" <>
          "\r\nServer: Mathematica/" <> ToString@$VersionNumber <>
          "\r\nContent-Type: text/html; charset=utf-8" <>
          "\r\nDate: " <> DateString[TimeZone -> 0] <> " GMT" <>
          "\r\nContent-Length: " <> ToString@StringLength@answer <>
          "\r\nVary: Accept-Encoding\r\n\r\n" <> answer;
        WriteString[client, output];
        
        ]
       ]]["Socket"]]];