MODERATOR NOTE: GitHub repository: https://github.com/ben-izd/Tableau-Mathematica-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 Wolfram Language inside Tableau.

This post consists of two code file which are also included at the end of this post:

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

How running Wolfram Language code inside Tableau works

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 Wolfram Language 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 sending Wolfram Language data directly to Tableau works

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 Wolfram Language SocketListen we could run a server and mimic a web page to send data directly from Wolfram Language to Tableau.

Run Wolfram Language code inside Tableau

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

1 - First run server.nb code at the end of this post. 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]))

Wolfram Language equivalent:

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

Clustering data - Example 2

Here we'll use Wolfram Language 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 Wolfram Language calculation and Tableau. Generally, Wolfram Language 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 a 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.

Wolfram Language code:

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

Tableau code:

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

Here are the differences between the two columns:

Load Wolfram Language 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 at the end of this post

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 Wolfram Language) - 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 Wolfram Language):

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]

Notes

For optimal performance, the first row of your data will determine the column's data type for Tableau.

Your data should be a 2-dimensional list in Mathematica.

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.

Github Repository

Code

server.nb code (Github):

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 (Github):

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"]]];