Explore US Spend Data With Tableau Extract API

As published on Cleartelligence blog first

Tableau can connect to a huge number of data sources out-of-the-box. This includes pretty much any database, flat file, excel, and more. However, there are some cases where companies and users are looking to connect Tableau to a data source that is not included in that long out-of-the-box connections list. For those cases, Tableau provides the ability to connect to “custom” data sources through several APIs, SDKs and approaches that can be put to use to provide a seamless Tableau user experience. To demonstrate this, my colleague Scott Briden and I set out to create an embedded Tableau dashboard that leverages a government web service which provides information on government spend via XML data.

The US government publishes spend information on its public web site https://www.usaspending.gov/Pages/Default.aspx

The site also provides a web services APIs (https://www.usaspending.gov/DownloadCenter/API/Pages/api.aspx) to allow programmatic data extraction.

To leverage these web services, we chose to use the Tableau Extract API. We created a web page with some selectors that allow the user to select which state, year and how many records to retrieve from the web service. Then we embedded a Tableau dashboard in the page that uses an extract connection hosted on our Tableau server. The web page allows the user to not only select the variables, but also to “refresh” the extract file the dashboard uses. That is done by invoking a program that retrieves the web service XML data, converts it to a Tableau extract using the Tableau Extract SDK and then re-publishes the extract to the server. Finally, once the dashboard page is refreshed, it alerts the user to view the new data in the dashboard. Enjoy…

Posted in Tableau | Tagged , | Leave a comment

Tableau Dashboards as text messages

–Originally posted on cleartelligence.com blog

  The addition of conditional subscriptions in Tableau server version 10.1 opened up the door for easily operationalizing alerts with Tableau dashboards. Not only it is easy to create conditional subscriptions that can automate operational information related tasks, these alerts can be distributed not only via email, but also via SMS or MMS messages. This means that critical, real time information about any data condition can be delivered in a manner that can be consumed by new (and old..) kind of devices for even better visibility and effectiveness. For my example, I first created a basic simple dashboard that is suitable for a phone, or even a smart watch.

I then published the dashboard to the server so it can be used with subscriptions.

Next, I created a new user account with an email address that will be resolved to my phone as a text message. In my case, I use AT&T, so the email format would be xxxxxxxxxx@mms.att.net where xxxxxxxxxx is the phone number. Verizon and other carriers have similar formats.

Next, I was able to easily subscribe the text user and set the text to a subscription which runs every 15 minutes but only sends the message if the threshold condition to the dashboard is met and it has data.

That is all the configuration needed! Next thing I know, I received the text message with the dashboard image on my phone and watch!


Apple watch:


Posted in Tableau | Tagged , , , | Leave a comment

Tableau Piano Chart


Ever since I got to wait for my son during some of his piano classes, I became fascinated by the relationship between music and math. There are so many mathematical concepts in play when playing, writing or even listening to music. One day, I hope to create a way to convert any dataset represented in a chart into a good sounding musical tune (there are many topics to address to make the music sound good, I will perhaps elaborate on some later post..), but for now, I was able to create a Tableau Piano Chart.. Using standard charting techniques in Tableau and an inventive dataset, I got the keyboard look and then set off to write a javascript implementation that will play a sound onMarksSelection…

I was able to find some great piano notes sound files and used the DOM audio object to play a sound whenever a markSelection event fired on the dashboard, passing the relevant note to the function using Tableau JS API. I tried the piano chart on my laptop, and all was well! I then tried it on my iPhone, and alas, it looked good, but was silent.. As it turns out, iOS Safari has a different implementation of the audio object and much of the standard functionality supported on desktop browser is not available.. Getting the sound to play on my iPhone during a tableau marks selection event became quite an adventure, but I finally got it to work! Matt Harrison WebAudioAPI helped me through it. While not 100% perfect, it works well enough, and I can now enjoy a fully functional piano chart in Tableau on my computer and iPhone as well.. Enjoy!

Posted in Tableau | Tagged , , , | Leave a comment

Voice Controlled Tableau Dashboard


The way we interact with computer is changing and in the very near future we can expect our computers and electronic devices to become much better at understanding what we tell them to do, literally. In this example, I used the annyang js library (with slight mods) to integrate voice command activation for Tableau dashboard. The annyang api leverages google voice recognition and currently works with google chrome, but we can expect similar offerings and multi-platform support for this type of functionality from Apple, Microsoft and the rest of the computer industry leaders in the next few years, to allow developers to fully integrate voice activation in their software in a seamless fashion.

*As originally published on Cleartelligence blog

Posted in BI At Large, Tableau | Tagged , | Leave a comment

Mine News Websites – Tableau Live Dashboard


Two years ago I wrote about setting up a process to scrape leading news websites home pages and storing them in HANA for text analysis. I hypothesized about the various analysis one could derive from parsing multiple news websites over time and looking at words and categories patterns over time. Well, after two years of accumulating data, we now have a significant enough dataset to start mining and analyzing for trends. The first attempt I made at building an analytical tool on top of this dataset is a word trend explorer. This is meant to provide users with the a ability to search for any words or terms in the news, review how often the term has been used across time, observe the trend by time only, category or word and source as well, and dive into the specific details by providing the word/phrase content/context for reach search. There are other capabilities that were critical to any such tool as well: performance – it has to be fast, useful – it has to be functional and interesting, usable – it has to be fairly intuitive to use and beautiful – it has to look good and meet modern design standards.

I also wanted to use some tools and technologies that are widely adopted and can be related to by many organizations and users, who can leverage this example as a demonstration of capabilities for their own projects.

So, this dashboard was built using Tableau. It is hosted on Cleartelligence tableau server and is exposed via a proxy to the outside world. We are using trusted authentication to bypass the Tableau login screen and allow users to access the specific content straight from the World Wide Web.

To provide full scope of analysis to the entire dataset, we are leveraging a live connection in this dashboard. It would have been impossible to use an extract with so much data. As you use the tool you will discover that the performance of the live connection, which relies on an SAP HANA in-memory database, is as fast as an extract (if not faster..).

While developing the dashboard several UI techniques were used to refine the look & feel of the dashboard, from leveraging images, and applying various formats to more refined techniques.

Finally, displaying the word mentions in the dashboard required working with BLOB objects in the database (the news data is stored in BLOBs that are parsed in HANA to words with sentence and offsets indices). This required implementing some out-of-the-box functionality in the form of a java program to obtain the relevant blob data from HANA, extract the relevant sentences and display it within the Tableau dashboard using a URL action that is triggered when you click on a month point in the trend bar.

Furthermore, to support a modern web site experience, we implemented a modal window popup from the Tableau dashboard that is being triggered by the URL action.

I hope you enjoy this tool and use it to learn about interesting news related trends. I look forward to adding additional functionalities to this tool, and welcome suggestions and other feedback!

Posted in BI At Large, Data visualization, HTML5, SAP HANA, Tableau | Tagged , , , | Leave a comment

Audit Tableau Filters Application With the JS API

While Tableau Server provides good auditing capabilities in terms of logins and dashboard views, in some cases, you need very detailed and granular auditing of certain dashboards. Like other websites, you may need to understand exactly which features of a dashboard are used by understanding how users interact with a dashboard. Which filters they are using and how, what navigation paths are taken, and what selections are being made by different users. If you do need to get to this level of auditing detail, you can use the Tableau javascript API which provides programmatic means to capture all the interactions on a viz. As a proof of concept, I went ahead and created this demonstration. The proof of concepts consists of two dashboards, laid out side by side on a web page. The left dashboard is being audited. It is loaded with my Oscars data as an extract, and contains several filters. You are encouraged to use the filters and click around this dashboard. The dashboard on the right uses a live connection to the audit database and can be refreshed to view live data by clicking the refresh button on top. As you click on filters on the left, you will see them populated on the right…
To accomplish this, I needed to address three areas of development: database backend audit table, server side service to process the audit event and save them in the table, and a frontend javascript component to utilize the Tableau JS API, capture user clicks and pass thenm to the backend service.
I began with the database layer and created a simple auditing schema with some information I wanted to track about it in a database table. The attributes I am tracking are the audited dashboard workbook file name and url, the dashboard name being clicked on, the username of the tableau user evoking the action, the filter field being applied, the field type, whether the filter is being used in include or exclude mode and the filter values being applied.

With a database table in place, I went on to write a RESTful web service that I could connect to from a webpage via javascript and pass info to be saved in the table.
Due to the asynchronous nature of the filtering and auditing sequences, I ended up putting some more login in the service class to be able to parse a JSON string that may contain multiple filter events. Since a single filter event may contains several values being applied, to save on the “chatter” between the dashboard and the server. This is despite the fact that the auditing is happening in the background asynchronously and does not impact the dashboard performance.


The third leg of this audit stool was the javascript needed to collect the user events and pass them to the service. The Tableau JS API provides methods to get filters and selections, as well as events that can be listened to for both operations. This was the hook needed to develop the audit capability. To handle filters, a hidden sheet is included in the dashboard and is “probed” for the filters being applied to it from the dashboard on each filter or select event. Selection events are also captured. In developing this script, I did not use any framework such as AngularJS, or even jQuery, and kept to the basics.


Finally, I created another dashboard connected live to the audit database, to explore the audit events being captured on the first dashboard, and created a webpage to lay them out side by side. One final issue to grapple with was handling the trusted authentication SSO through the API, and was that was sorted out, the demo was complete. Enjoy!

Posted in Tableau | Tagged , , , , | Leave a comment

Tableau Infographic dashboard – it’s not the tool which makes a dashboard cool

Ever since Josh Tapley published the infographic style dashboard, I have been working to reproduce this design in various tools. I used SAP webi and crystal reports to create similar versions in the past and recently produced another version in Tableau. This is an interactive dashboard, and clicking the Change Data button will refresh the dashboard with new random data. Click the image below for the interactive tableau workbook.

This is another example of why proper design in a dashboarding project is just as important (and in some cases more..) as data design. It is also an example that illustrates how Tableau visual capabilities can be stretched from a dashboarding perspective.. Enjoy!


Posted in Data visualization, HTML5, Tableau | Tagged , | Leave a comment

Change the order of execution of connections in Tableau

I recently ran into a situation where I had to change the order of refresh execution in a tableau dashboard that had multiple connections (extracts) that support multiple worksheets/dashboards.

The original order of execution of the connections is determined by their creation order. The connection that gets created first, gets executed first, then the second, etc, when extracts are used. Tableau actually appends a timestamp to the connection name upon creation, and uses that timestamp appended name to execute the extracts in order when it’s time to refresh them.

For example, I created a tableau dashboard with 3 simple extracts, each one connected to a different excel file. The connections were created in order (Conn1, then Conn2, then Conn3).


Now, I would like to change the order in which these connections are refreshed…

My tableau file was saved as ConnOrderOfExec.twb. To view and modify the order in which connections will be refreshed, follow these steps:

1. Make a backup copy of the original .twb file

2. Rename the .twb file to .xml

3. Open the .xml file with a good and simple text editor (notepad is fine, not word please)

4. You will find a datasource tag with a caption attribute which is the nice name you given the connection and and internal name attribute for each connection with would follow the form of databasetype.timestamp


5. To change the order of connections execution on refresh, simply replace the .timestamp with a number that represents the order of your choice! Be sure to use replace all to replace all the occurrences of the timestamp you are looking to change


6. Save the .xml file, close it and then rename it back to .twb

7. Open the .twb file and now the extracts will refresh in the new order you determined!


Posted in Tableau | Tagged | Leave a comment

Custom tabbed navigation in tableau

This example illustrates how a combination of images used as tableau shapes, with some careful positioning and action filters, can be used to create custom navigation effects that will feel natural and intuitive to most users. Here is a description of the process used to produce this tableau dashboard:
First, using power point, 2 images were created, a selected tab and an unselected tab.


These images were added to tableau shapes repository.


Then, in tableau, these images were used to create two sheets for each tab screen, a selected tab state and an unselected tab state.

03_home_selected 04_home_unselected
Then, 4 dashboards were created with the worksheets containing the images carefully positioned on each dashboard

Finally, action filters were created on each page to navigate to the appropriate page on selection of each navigational tab sheet as needed

The dashboard was published to the server and.. Voila! The custom tabs are ready

Posted in Data visualization, Tableau | Tagged , , | 5 Comments

Tableau recalls dashboard using Extract API

tableau recalls dashboard

In this recalls dashboard, I used custom shapes/symbols to represent each hazard and reflected the number of recalls through the size of the symbols. I also used the manufacturing country to create a map of the recalls, were the size of each country bubble is representing the number of recalls (guess which country gets the biggest bubble…). I also added a link to a detail page that has each recall info, and a link to the agency recall web page about the recall. And the sweetest thing, I will not need to touch this dashboard ever again, it refreshes the data from the xml each night automagically.. Be sure to click the image above to visit the live dashboard!

A couple of years ago I published an HTML5 recalls dashboard on this blog (http://bihappyblog.com/2012/12/17/recalls-dashboard/). To create this dashboard, I connected to the Consumer Product Safety Commission web site and converted the xml data it provides into JSON format to be consumed by the HTML5 dashboard. Recently, I ran into the Tableau Extract API and I thought it would probably be interesting to create a Tableau version of this dashboard, in which I could leverage the Extract API to convert the XML feed provided by Consumer Product Safety Commission in Tableau extract format (.tde file). Once that technical hurdle is accomplished, Tableau could be used to create really neat visualizations quickly and easy. What’s more, this illustrates the possibility of connecting Tableau to virtually any data source, currently supported by Tableau or not, since the Extract API can be used to create a .tde file from virtually any data that can be connected to programmatically.

First thing is first, I made sure I followed the Extract API instructions for Eclipse (http://onlinehelp.tableausoftware.com/current/pro/online/windows/en-us/extracting_TDE_API.html). Once my environment was setup, and I was able to run the sample MakeOrder app to produce a .tde file, it did not took much adaptation to write my own class to connect to the CPSC website to obtain the recalls in their XML format, iterate through the XML document and convert it to the Table format needed to produce a Tableau Extract file. Below is the source code for that..

package com.cleartelligence.tableau.extract.api.sample;

import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.net.URLConnection;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import org.w3c.dom.Document;
import org.w3c.dom.NamedNodeMap;
import org.w3c.dom.NodeList;
import com.tableausoftware.TableauException;
import com.tableausoftware.DataExtract.Collation;
import com.tableausoftware.DataExtract.Extract;
import com.tableausoftware.DataExtract.Row;
import com.tableausoftware.DataExtract.Table;
import com.tableausoftware.DataExtract.TableDefinition;
import com.tableausoftware.DataExtract.Type;
public final class MakeRecalls {
     private static TableDefinition makeTableDefinition() throws TableauException {
        TableDefinition tableDef = new TableDefinition();
        //Create a table definition based on the recall data
        tableDef.addColumn("UPC",       Type.UNICODE_STRING);
        tableDef.addColumn("country_mfg",         Type.UNICODE_STRING);
        tableDef.addColumn("hazard",        Type.UNICODE_STRING);
        tableDef.addColumn("manufacturer",           Type.UNICODE_STRING);
        tableDef.addColumn("prname",        Type.UNICODE_STRING);
        tableDef.addColumn("recDate",           Type.UNICODE_STRING);
        tableDef.addColumn("recallNo", Type.UNICODE_STRING);
        tableDef.addColumn("recallURL", Type.UNICODE_STRING);
        tableDef.addColumn("type", Type.UNICODE_STRING);
        tableDef.addColumn("y2k", Type.UNICODE_STRING);
        return tableDef;    }
     private static void parseRecalls(Table table, String startDate, String endDate) throws Exception    {
           String insertItem = " ";
           TableDefinition tableDef = table.getTableDefinition();
           //URL to obtain recalls XML data - added params for the start and end date so they can be passed in from the main method
        URL url = new URL("http://www.cpsc.gov/cgibin/CPSCUpcWS/CPSCUpcSvc.asmx/getRecallByDate?startDate="+startDate+"&endDate="+endDate+"&userId=test&password=test");
        URLConnection connection = url.openConnection();
        //After examining the xml structure, determined the level needed, iterate to the XML node level needed
        Document doc = parseXML(connection.getInputStream());
        NodeList first = doc.getChildNodes();
        NodeList second = first.item(0).getChildNodes();
        NodeList third = second.item(1).getChildNodes();
        for(int i=0; i<third.getLength();i++)
           //result rows have recalls data in the xml, parse them into the table
           if (third.item(i).getNodeName()=="result") {
                NamedNodeMap attr = third.item(i).getAttributes();
                System.out.println("Wrote line "+i +" into .tde file");
                Row row = new Row(tableDef);
                for (int x=0; x < attr.getLength(); x++) {
                      if (attr.item(x).getNodeValue().length()<1)
                           insertItem = "NULL";
                      insertItem = attr.item(x).getNodeValue();
                      row.setString(x, insertItem);
private static Document parseXML(InputStream stream)
               throws Exception
                   DocumentBuilderFactory objDocumentBuilderFacto
y = null;
                   DocumentBuilder objDocumentBuilder = null;
                   Document doc = null;
                       objDocumentBuilderFactory = DocumentBuilderFactory.newInstance();
                       objDocumentBuilder = objDocumentBuilderFactory.newDocumentBuilder();
                       doc = objDocumentBuilder.parse(stream);                   }                   catch(Exception ex)   {                       throw ex;                   }                         return doc;
      public static void main(String[] args)
   {                //start and end date args for the recalls data as yyyy-mm-dd, for example, 2014-12-31
           String start = args[0];
           String end = args[1];
           //path to .tde
           Path path = Paths.get("C:\\recallsTDE\\recalls-java.tde");
           try {
                //remove any prior version of the .tde
           } catch (IOException e) {
           try (Extract extract = new Extract("C:\\recallsTDE\\recalls-java.tde")) {
                Table table;
                    TableDefinition tableDef = makeTableDefinition();       
                    table = extract.addTable("Extract", tableDef);
        } catch (Throwable t) {

The next step was a bit more tricky, as it was not documented anywhere I could find. How to actually use the .tde file in a tableau server environment so that dashboards can be created off it and refreshed automatically? After experimenting with multiple methods, I settled on the following approach: run the extract program as a runnable jar on a server and then use the tabcmd API to publish the data source into my tableau server. The .bat file I created to run the runnable jar and export the .tde into the server looks like so..:

cd C:\recallsTDE
java -jar "C:\recallsTDE\recalls-java.jar" 2000-01-01 2020-12-31
tabcmd publish "C:\recallsTDE\recalls-java.tde" -o -s http://[my.tableau.server.ip] -u [myTableAdmin] -p [myTableauAdminPwd] -t [mySite]

My runnable jar, recalls-java.jar, sits on the C drive in a directory called  recallsTDE. It takes the argument 2000-01-01 for the first date to look for recalls from the government web site and 2020-12-31 is the end date.

After the java program finishes running and the .tde is created, it is published to the server with the tabcmd command, which you can lookup as well. Documentation for it is par I guess, but you can kind of make your way through it, it is fairly intuitive.

I put this .bat file into a windows schedule which runs daily and now I have my automatically refreshable tableau server data connection to work with.

The next step was of course to create the dashboard in tableau desktop from the data.

And the final step was to leverage the trusted authentication setup I have with our front facing servers (since tableau server sits behind the firewall) to expose the dashboard via a URL that will bypass the login screen. Here is the code for that step as well:

<%@page session="false"%>
<%@page import="java.net.*,java.io.*" %>
String user = "[TableauUserWithAccessToDashboard]";
        String wgserver = "[TableauServerAddress – internal]";
        String dst = "views/[workbook]/[view]";
        String params = ":embed=yes&:toolbar=no&:tabs=no&:refresh";
String ticket = getTrustedTicket(wgserver, user, request.getRemoteAddr());
        if ( !ticket.equals("-1") ) {
            response.setHeader("Location", "http://server/trusted/" + ticket + "/t/site/" + dst + "?" + params);
            // handle error
            throw new ServletException("Invalid ticket " + ticket);
<%!private String getTrustedTicket(String wgserver, String user, String remoteAddr)
        throws ServletException
        OutputStreamWriter out = null;
        BufferedReader in = null;
        try {
            // Encode the parameters
            StringBuffer data = new StringBuffer();
            data.append(URLEncoder.encode("username", "UTF-8"));            data.append("=");
            data.append(URLEncoder.encode(user, "UTF-8"));
            data.append(URLEncoder.encode("client_ip", "UTF-8"));
            data.append(URLEncoder.encode(remoteAddr, "UTF-8"));
            data.append(URLEncoder.encode("target_site", "UTF-8"));
            data.append(URLEncoder.encode("Site2", "UTF-8"));
            // Send the request
            URL url = new URL("http://" + wgserver + "/trusted");
            URLConnection conn = url.openConnection();
            out = new OutputStreamWriter(conn.getOutputStream());
             // Read the response
           StringBuffer rsp = new StringBuffer();
            in = new BufferedReader(new InputStreamReader(conn.getInputStream()));
            String line;
            while ( (line = in.readLine()) != null) {
            return rsp.toString();
        } catch (Exception e) {
            throw new ServletException(e);
        finally {
            try {
                if (in != null) in.close();
                if (out != null) out.close();
            catch (IOException e) {}

And there you have it. Enjoy!

Posted in Data visualization, Tableau, Uncategorized | Tagged , , , | 1 Comment