The contents of this post have been merged into a revised version of How to set up your Database for Row Level Security in Tableau, where they rightfully belonged in the first place.
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?
- 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).
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”:
Tableau’s behavior for saving content when using Web Edit follows these rules:
- If you are the Content Owner, you can Save or Save As
- 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.
- A Project Per Team / Group
- A Project Per User
- A REST API script that “fixes” Permissions
- Publishing a New Copy rather than Save As
A long time request I’ve heard from customers is the need to update the images of Tableau Server vizes within a PowerPoint presentation. Behold! Emailer 2.1 now includes a tab for doing this type of “find and replace” on an existing PowerPoint (PPTX) presentation file. You specify the slide and the location of the viz you’d like to pull, and the tool will download that image, insert it into the PPTX file, and replace whatever other image was previously on that slide. It should be a real time saver for those who have to do this type of activity regularly.
The instructions for using it are all in the README , including the caveat that this is only for slides that have a single image in them currently. Please let me know if you have any issues or other requests around this use case.
The currently available Beta 1 of Tableau 2018.3 includes a long-requested feature for creating multiple table Hyper extracts — that is to say, each table you see in the connection pane will be brought in and stored as separate tables in a single Hyper extract file. Why is this so exciting? Because it’s the end of the need for Defusing Row Level Security in Tableau Data Extracts (Before They Blow Up) Part 1 (and Part 2)!
Starting in 2018.3
- The design for row level security will be the same in both live connections and extracts
- Extract files with security will create much faster
- Best practices for entitlements tables are now feasible in Extracts
Let’s dig into the essentials and how we can make this work for effective Row Level Security.