Author: bryantbhowell

Web Services / REST APIs as a Data Source in Tableau: A PostgreSQL/Python solution

The trend toward wrapping together data and security filtering into RESTful web services has only increased in the past few years, and for a lot of good reasons. If the REST API is optimized to return very quickly based on a set of filtering parameters, the performance alone can justify the architecture. However, Tableau is planning to do more with the result set than simply display it directly — Tableau is ready to do deep analysis and discover new insights. Because of this, Tableau’s VizQL engine needs something it can query in many different ways as the basis for a data source connection.

How can we bridge the gap between a JSON (or XML, if anyone still does that) object response and a relational query engine?

What if I told you THIS was all that was necessary on the Tableau Desktop side to build a data source that acts as a “live” connection to a web service?:

Accessing the PL/Python Function in Tableau

Custom SQL in Tableau

 

Connect to the PostgreSQL database in Tableau Desktop using whatever credentials you created for an end user.

Then on the Data Connection screen, you’ll find on New Custom SQL on the left:

If you drag that out, then the dialog to define the Custom SQL query appears:

Custom SQL dialog

As you can see, the query is just SELECT * FROM function_name( arguments ), with whatever parameters you want to attach. You can create new parameters right from this dialog box, then put them in place. Make sure to give sensible defaults, and probably most should default to an empty string so that someone can only access if they have the right tokens / etc.

How is this possible?

There’s a whole lot of setup on the PostgreSQL side, but the payoff is the simplicity of the setup in Tableau.

The solution is based on the architecture originally laid out here, but I’ve put together a Dockerfile which builds out a PostgreSQL database ready for your custom set of functions which will dynamically call a RESTful Web Service based on Tableau’s parameters.

(more…)

tableau_tools 5.0 : Python 3 for 2020 (and so much more!)

Just in time for 2020, tableau_tools has gone a thorough upgrade to bring it into the Python 3 era. While the 4 series of tableau_tools was Python 3 compatible, tableau_tools 5.0 and beyond are Python 3 native, dropping support for 2.7 entirely. It also drops support for any version of Tableau before 10.3, which matches Tableau’s official support policy at this point.

In the process, the source has also been completely refactored for anyone who wants to join in and help with the project or just is tracking down a bug or strange behavior. It’s far easier now to find where everything is implemented, understand the logic of it, and make suggestions or changes.

Update to Python 3.6 or better, and then use PIP to install the latest version from PyPi. For all the good details, read more…

(more…)

The Missing Link – Putting a Hyper File from the Hyper API into a TDSX or TWBX File

The Tableau Hyper API is an amazingly powerful tool for building out Extracts that, for whatever reason, cannot be built or maintained using the standard Tableau Server extract refresh process. You can publish a Hyper file directly to a Tableau Server, but there are several drawbacks:

  • Tableau Server will build out an automatic TDS file, taking a rough guess at any type of metadata categorization (Measure vs. Dimensions, Hierarchies, Geographic info, etc.)
  • The only use for this data source will be creating Ad Hoc reports using Web Edit (or hoping someone in Desktop now knows that it exists). You can’t integrate it easily in an existing Workbook
  • It only works with Single-Table Extracts, and in older versions of Tableau, the single table must be named “Extract” or you will encounter errors.

What is missing is a TDS file to pair up with the Hyper file, describing the exact metadata that you want to go along with the Extracted data. In this article, I’ll describe a workflow that result in a fully controlled TDSX file with a newly generated Hyper file.

(more…)

Exposing Limited Amounts of Tableau REST API Functionality to Users via tableau_tools

As of 2020.2, the Tableau Server REST API has two mechanisms for logging in: username/password or a Personal Access Token (PAT) . There is no direct way to directly start a REST API session using a SSO mechanism (SAML, JWT, etc.).  Even if you were able to, you might still want to restrict the user to only do certain actions (for example, enabling Querying methods but not Updates or Deletes).

The best practice for working around this is to wrap the Tableau REST API in another REST API service of your own design. Then within that wrapper, use a Server Administrator level account to log in to the Tableau Server REST API. In this article, we’ll discuss how to achieve this using tableau_tools, with both a simple and a more complex but efficient design pattern.

This pattern only works with an Server Admin account that has logged in using Username/Password. PATs do not allow an admin to Impersonate into a regular user’s session at this time. This is our best practice recommendation for using the Tableau REST API out to a web application embedding Tableau Server views.

(more…)

Apply All buttons in JavaScript and Extensions API

Both the Tableau JS API and Extensions API send commands using Asynchronous JavaScript, based on the Promises JavaScript framework. If you are not well-versed Promises or asynchronous JavaScript in general, it can be difficult to go from the basic examples in the reference guide to more complicated solutions.

Over the years, customers have frequently asked about doing an “Apply All” button using the JavaScript or Extensions API, where filters on multiple different fields can all be applied from a single button click. While there is no way to do this directly in either API at the current time, you can create a process that very efficiently applies all of the filter changes as quickly as possible. This article will show one solution to the “Apply All button” request.

(more…)

Building a Flexible Extract Generator using the Extract API

One of the least mentioned, but incredibly useful APIs in Tableau is the Extract API, which allows you to programmatically create an Extract file (Hyper files starting in 10.5, previously TDE files). The main use case is for data sources that require programmatic access (as opposed to using the one of the native connectors in Tableau). Some situations where this would be useful:

  • Data coming from a Web Service/ RESTful API with an object response
  • ODBC / JDBC drivers that Tableau cannot use
  • Additional programmatic modeling / statistical analysis against a whole data set

This post is focused mostly on first use case, where you are trying to make data available from some type of Web Service / RESTful API. In particular, if you need to provide only a subset from a very flexible set of possible fields for “ad hoc” analysis, this technique is the most functional solution to the problem.

When should I build a Flexible Extract Generator?

If you:

  • Know the structure of your web service responses
  • The amount of total fields is reasonably sized
  • The web service responses will not change frequently
  • Workbooks are fully built out and will not allow web editing
  • Data Source structure can be reused across multiple reports (and possibly customers)

then the better solution for Web Service/REST API based data sources is “Live” Web Services Connections in Tableau.

If instead you want to provide a selection screen to generate an Extract that will power a Web Edit session, then it makes sense to build a Flexible Extract Generator process. This is particularly useful when the set of fields could change drastically from extract to extract, or if other processing (such as machine learning) needs to be applied based on differing parameters prior to its use by the end user (that said, if the actual output columns are consistent, the “Live” Web Services solution could still work).

(more…)

“Live” Web Services Connections in Tableau

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”:

Full Embedded Web Services Architecture

(more…)

Keeping Web Edit Content Private

Tableau’s behavior for saving content when using Web Edit follows these rules:

  1. If you are the Content Owner, you can Save or Save As
  2. If you are not the Content Owner, you can Save As

Save As is only allowed to Projects where you (or the groups you belong to) have a Save permission set to “Allow”.

Since a newly Saved Workbook will take the Default Permissions of the Project it saves into, if other people also have permissions for that same Project, they will also be able to access that content. This leads to several different strategies for controlling the privacy of content created through Save As.

Possible solutions:

  • A Project Per Team / Group
  • A Project Per User
  • A REST API script that “fixes” Permissions
  • Publishing a New Copy rather than Save As

(more…)

Session-level Single-Sign On

Tableau Server doesn’t currently (as of 2018.2) have a dedicated “service” for authentication when doing Single Sign-On. Instead, a Tableau Server session is established the first time you load a Viz using a given SSO method. Whether using Trusted Authentication, Windows Authentication, or SAML, when the first viz loads, that is when the authentication actually happens, and after that point, there is a Tableau Server session cookie, so that the authentication doesn’t have to happen continuously.

Traditionally, particularly for Trusted Authentication, instructions have been given to request and send a new ticket for each load of any Viz. But this introduces extra, unnecessary authentication requests, and can even lead to a “race condition” when you are loading multiple Vizes in the same page, where sessions are being created and overriding each other as separate tickets are processing at the same time. This same issue can affect ANY of the SSO methods when loading multiple vizes.

The Hidden / Empty Viz Solution

As mentioned above, once the first Viz has been authenticated, there is a Tableau Server Sesssion cookie that will be used for all subsequent requests. So to create a “login” service, we simply need to login as quickly as possible to a Viz. This is very similar to what you need to do for Trusted Authentication SSO into the full Tableau Server UI.

The simplest Viz possible is all we need (literally, a single filter on a page using a totally blank Data Source can be used). Every user is the Tableau Server should have access to this Viz. As soon as the user is authenticated into the main application, you should load the simple viz — if using Trusted Authentication, this request should include the trusted ticket. You can hide the Viz div under something or out of sight (the div shouldn’t actually have visibility:hidden though because some browsers don’t like that prior to the Viz being initialized), so that the user doesn’t see this load process. Or do it quickly in a page that then redirects to the next page. It’s up to you.

For even more performance, you can request the PNG version of the Tableau viz, which will return an image instead of loading the full JS viz object. All that is required is adding “.png” to the ending of the Viz location you would be loading, for it to be an image request. Given that ability, you can actually redeem the trusted ticket completely in JavaScript prior to initializing the Viz using the Tableau JS API. You can do this anywhere in the site once you know who the user is; redeeming the Trusted Ticket will establish the Tableau Server session and any subsequent viz renderings will use that session.:

<pre>// One way to redeem a trusted ticket would be a .png request
function redeemTrustedPng(tableauServerBaseUrl, siteContentUrl, workbook, view, trustedTicket){

    if(siteContentUrl === null){
        redemptionUrl = tableauServerBaseUrl + "/trusted/" + trustedTicket + "/views/" + workbook + "/" + view + ".png";
    }
    else{
        redemptionUrl = tableauServerBaseUrl + "/trusted/" + trustedTicket + "/t/" + siteContentUrl + "/views/" + workbook + "/" + view + ".png";
    }
    // Now load the image, but not actual place in the visible part of the DOM
    var redemptionImg = new Image();
    redemptionImg.onload =  function () {
        console.log("Trusted ticket redeemed!")
        trustedImageLoadResponse(true);

    }
    redemptionImg.onerror = function () {
        console.log("Trusted ticket image retrieval failed");
        trustedImageLoadResponse(false);
    }

    // Actually load the image here
    redemptionImg.src = redemptionUrl;
}

function trustedImageLoadResponse(response){
    // This is a stub for the page itself to do something with

}</pre>

Session Timeout with Trusted Tickets

The one advantage of continually sending Trusted Tickets is that the Tableau Server session is continually extending as each ticket is sent. If you only do one Trusted Ticket to establish a session, how do you keep from timing out and sending the user to the Tableau Server sign-in box? The answer is to set your own timer cookie, and whenever it times out, reestablish the session using the Empty Viz. You shouldn’t need to do this with SAML or AD, because they will automatically call out and reestablish their sessions, but you could.

Sign-Out

Tableau Server sessions will end naturally based on the value you have set for them using tabadmin / TSM. Actually forcing a Tableau Session to sign-out is a little tricky — recent versions of Tableau Server understand a SAML IdP Signout, or you could try to use the REST API to signout, but in the latest version of Tableau Server, the REST API technique requires reverting to a simpler, less secure type of session cookie.