Databases

Passing a JWT as a Tableau Parameter

You’re probably aware that Tableau Parameters are not an inherently secure way to filter down data. A lot of people are using JWTs to pass around tokens with security entitlements, so if you are good with that, did you know you can pass one right into a Tableau Parameter?

I’ve built out an example where the JWT comes through then a function fires off a web service request, as part of the work for using a Web Service / REST API as a live data source, but there’s no reason you couldn’t build a smaller function for processing the JWT and then use just that in Custom SQL in Tableau when building your data source.

Although the following example uses PostgreSQL (and PL/Python), you could theoretically implement this in any language with access to a language that can process JWT. In MS SQL Server, a CLR Stored Procedure can access C# functionality, and the Systems.IdentityModel.Tokens.Jwt namespace appears to have all the necessary functionality to implement a very similar workflow.

Below I’ll work through a workflow using PL/Python on PostgreSQL — again, the same concept could be implemented on any RDBMS with functions.

(more…)

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…)

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…)

“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…)

Using Pass-Through Functions (RAWSQL) for Row-Level Security

NOTE: It is unclear that this works for security, given how caching works in Tableau. Not currently recommended without extensive testing.

 

In the classic text on the subject of Tableau Row Level Security, How to set up your Database for Row Level Security in Tableau, this author discussed the “WHERE method” of doing security look-ups, but advised that since the only practical method for achieving it was Initial SQL, that the “JOIN method” was best practice.

However, it has come to my attention that one of the most overlooked features that has been in Tableau for a long long time can be used to achieve the WHERE method, as well as run any arbitrary function or stored procedure that might be useful in establishing security context. What is this functionality, you are asking yourself (hopefully not out loud but I won’t judge too much): Pass-Through Functions i.e. the RAWSQL commands.

(more…)

Tableau and Write-Back – Together At Last

Editor’s Note: Huge thanks to special contributor Gordon Rose for this blog post.

Tableau helps people see and understand their data – and guarantees that it in the process, it will never make any changes to that data. Tableau is a strictly read-only technology. However, many customers want the ability to modify the data that lies behind a Tableau visualization (Viz), and then, either see those changes immediately reflected in the Viz and/or make other applications aware of those changes. With a small amount of supporting technology, Tableau’s read-only behavior can easily be integrated into so-called “write-back” use cases.

In this blog article, we’ll explore a way to do exactly that – one in which the write-back components are external to the Viz. An alternative approach is one in which those components are more tightly integrated into the Viz itself – that’s for a later blog article to explore. Ideally you will find that you can use one of these two approaches as a launching point for the development of your own write-back use case.

(more…)