Many organizations have begun standardizing on a “Web Services” layer for access to reporting data, often with a restriction on directly connecting to the underlying data stores that power the Web Service responses. In the majority of cases, the result is a set of RESTful endpoints returning JSON object data, but for the purposes of this article, any variation that involves HTTP requests and responses in a “web-friendly” response format (JSON / XML) will be referred to as “Web Services”.

There are many reasons for adopting this architecture, and I’m here neither to recommend or pass judgement. There is one major implication to this architectural decision though — BI systems that expect a relational model and SQL-compliant querying capabilities do not have a native, natural way to handle these data responses. Tableau falls in this category (I don’t care about any others, but it’s not an issue exclusive to Tableau).

Tableau provides a Web Data Connector technology which helps individual analysts retrieve data from Web Service Data Sources, but current design does not account for data sets to vary depending on the user looking at the workbook, something essential for scalable and secure Tableau Server reports.

However, Tableau’s ability to connect live to a wide range of relational data sources allows us to construct an alternate architecture for accessing Web Services responses “live”:

Using a Relational Database to Store Web Service Responses

Tableau expects data be in a tabular, relational format (Rows of data with well-defined Columns). Often, Web Services data is returned in more of an Object-Oriented format. You can go read deeply about object-relational mapping and object-relational impedance mismatch, but the short version is that everyone has to do work to figure out how to transform an object-oriented response into a tabular form.

Due to this requirement, when you build a Tableau Web Data Connector, it must follow this exact set of steps:

1. Retrieve Web Service Data
2. Transform object-oriented responses into tabular form
3. Create a Table Definition (schema) for the Extract table(s)
4. Insert the rows of data into the tables in the Tableau Extract (Hyper, previously TDE)

If you know how to do Steps 1, 2 and 3, can just as easily insert the rows of tabular data into a relational database table as an Extract file.

Solution: Transform the Web Service response into tabular format, then insert into a relational database table.

Every response, after it is transformed, will be stored in a Response Table in the RDBMS system. Tableau will connect to this Response Table like any other Live Data Source. You connect the workbook to a given response by setting a Tableau Parameter, which I will refer to as the Response ID Parameter. To insure that a user only sees their given Response, a simple Calculated Field should be placed as a Data Source Filter:

[Response ID Column] = [Response ID Parameter]

I want to point out a major difference from other solutions: You are only storing responses to individual Web Service Requests, not trying to pull all of the information from the Web Service. This solution allows for taking advantage of the speed, optimizations, and security layers built into Web Services, while also giving Tableau a relational system with full ODBC compliance to query.

Defining the Response Table(s)

For this architecture, the Response Table(s) in the RDBMS must meet the following conditions:

1. They must be defined ahead of time and exist permanently in the RDBMS, so that Tableau Desktop or Server can connect to them as Data Sources.
2. There must be one Response Table per expected Web Service response. If you need to combine together several Web Service responses together for effective analysis, you can either do that in your transformation process and store them together, or store them each to separate Response Tables.
3. Each Response Table needs a Response ID column to store a unique, non-sequential ID per response. Multiple rows of data for each Response should all have the same Response ID. This can even be true across Response Tables, if there are multiple Web Services called at a time.
4. The table should have an INDEX on the Response ID column to allow for efficient filtering
5. Each Response Table should have a Timestamp column, to allow for removing outdated responses.

Essentially, every Response Table will resemble the following:

Knowing that this is your basic pattern, then the real work is in figuring out what columns you end up with from the original web service response.

Embedded Workbook Architecture

If your Tableau vizes are embedded in any other system (rather than accessed directly via the Tableau Server web interface), then you can handle requesting the response and passing the Response ID parameter entirely outside of the Tableau workbook. One advantage of this architecture is that you can take advantage of any other authorization attributes (tokens, entitlements, usernames / ids) that the embedding application already has available.

Let’s walk through each of the steps in picture form to get an idea of the workflow:

0. Setting the Stage

These are the main components of the architecture. The hexagon in the middle is the code that performs the Retrieve, Transform, and Insert operations. Where it actually “lives” is up to you — it could be incorporated into the Web Application logic, or exist as a separate “service” somewhere. Its main requirements are that it be (1) able to read the Data Web Service and retrieve the responses efficiently and (2) it is able to write quickly to the Response Database.

1. A User Enters the Web Application to see a Tableau Viz

In this embedded architecture, we’re assuming that the Web Application is handling single sign-on and whatever authentication and authorization information that the Data Web Service needs. This might be a token, or some SAML auth parameters in an assertion, or simply a few parameters that need to be POSTed to the web service. The Web Application passes this information to the Retrieve, Transform, and Insert Code, so that it can retrieve the initial response.

2 & 3. The Retrieve, Transform, Insert Code Retrieves the Response

This aspect will entirely be up to you in the Retrieve, Transform, Insert code, but most likely it will involve using an HTTP POST command to retrieve a JSON response.

4. Transform the Response and Insert into Response DB

As mentioned earlier, the most difficult part of working with Web Service data is transforming an object-oriented response into something tabular. Step 4 is where you do that, and I’m leaving all that hard work up to you.

This step is also where you create the unique Response ID. The only real requirement for the response ID is that is be non-sequential, so that end users can’t easily iterate through to see other responses (see Securely Passing Parameters into a Tableau Viz at Load Time for additional discussion). It should also be unique, i.e. not in use in any Response Table at that moment.

With a Response ID generated, you’ll add it to each row you want to insert, along with a timestamp (the timestamp field could probably be filled in automatically by the database at insertion time). Then insert the rows into the correct Response Table in the Response DB.

5 & 6. Send Response ID back to the Web Application and Load the Tableau Viz

Once the insert operation is complete, it’s time to load the Tableau viz. The Response ID should be returned to the Web Application, and then you can construct the URL to load the Tableau viz.

The most efficient way to set the Response ID Parameter in the Tableau Workbook is by appending it to the URL prior to the viz loading:

https://myserver/#/MyWorkbook/MyDashboard?r=xm30x3Xz1hvLfb302cZeoz14YP

This can also be accomplished using the options object of the JS API constructor.

If you need to change to retrieve and load a new Response while the user is exploring the viz, you can use the JavaScript API to update the Response ID Parameter:

Viz.Workbook.changeParameterValueAsync(“r”, “xm30x3Xz1hvLfb302cZeoz14YP”)

7. Tableau Viz Loads with Data Source Filter

The Tableau Workbook should be published with a Data Source Filter in place (as mentioned earlier), set on a Calculated Field which incorporates the Response ID Parameter. With the Response ID Parameter set at load time, the workbook will load up connected only to that specific Response ID’s data in the Response DB. Every query that Tableau generates will include that Response ID Parameter filter in a WHERE clause on the inner-most queries.

Extensions API Architecture when not Embedding

Starting with 2018.2, you can include Extensions in Tableau Dashboards. An Extension typically consists of two parts — a component built of HTML and JavaScript that is visible in the Dashboard, and a web service which can do additional processing.

Since there is no additional application to provide authorization information, you will have to provide some kind of mechanism within the visible Extension component to sign-in / accept a token / do SSO, whatever is necessary to securely establish who the user is for the web service component. Since Extensions are web technology based, you can store information to a cookie when the dashboard is viewed in Tableau Server, so it is possible to define a process where authentication is only necessary once (or once in a while).

Once the authentication / authorization details are figured out, the Extension will send that information to the web service component in an AJAX call, which will handle retrieving the Web Service Response, transforming it into tabular form, and storing it in the Response Table. The web service component will return back the Response ID to the front-end of the Extension, which will set the Parameter value using the Extensions API.

The architecture looks very similar to the previous architecture for an embedded use case:

The main difference is that you have to write a Tableau Extension , which has a front-end piece and a back-end service piece. The back-end service is the Retrieve, Transform, Insert Code, which can be built however and wherever you’d like. The front-end piece needs be in HTML and JavaScript and use the Extensions API itself. The Extensions API is similar to the Tableau JavaScript API, but not exactly the same. The main thing you will be doing is setting the Parameter value, which is accomplished via code like this:

function setResponseParameter(response_id){

tableau.extensions.dashboardContent.dashboard.findParameterAsync("r").then (

function (param) {

param.changeValueAsync(response_id);

}

);

}

The other front-end piece will be writing any SSO integration, but again I’ll leave that fun part to the web-developers who know that best. It should all be standard HTML run by the browser, so cookies and such will work when deployed to Tableau Server (cookies do not work currently in Extensions viewed in Tableau Desktop, but you can always put in a feature request if that is needed functionality).