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”:
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.
Since there’s been so much time and better examples and code, I went back and did a major revision of the The Tenets of Tableau Templates on Multi-tenants which I highly advise everyone reading. It’s the most thorough explanation out there of how to correctly handle SaaS / Multi-Tenancy or Dev->Test->Prod promotion. And no, you do not need Interworks PowerTools to do this process, although they do have some nice features.
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.
Have you heard this one before? “Just connect to your data in Tableau and start visualizing. Then you’ll publish and share with your whole organization.” It’s a great line, because it’s true. You CAN get started with analysis on top of just about any data in Tableau. But “can” is not “should” — what is possible may not be the BEST way, particularly if you want to scale up. When dealing with massive amounts of data, a better solution is to have two data sources: (1) A pre-aggregated data set for overviews, which I’ll call the Overview data source (2) The row-level data set, which I’ll call the Granular data source. Tableau’s abilities to filter between two data sources (actions & cross-datasource filters in Tableau 10) make this an excellent strategy, and one that I have seen massively improve performance over and over.
We have a tendency to answer questions of multi-tenancy in Tableau swiftly with switching between Sites, which are the virtual tenements for your Tableau tenants. But Sites are the simple part of the equation; when there is a need for multitenancy in Tableau, there is most likely existing multi-tenancy in the data systems Tableau must connect to. I’m going to dive into the diverse ways that customers corral their data and outline all the tenets of deploying effectively from a single template to all your tenants.
Tableau has supported Stored Procedures in Microsoft SQL Server (and Sybase and Teradata) since version 8.1, and you can connect the SP parameters to Tableau parameters.
However, there are two features that don’t exist as of 9.2:
- Parameters cannot be set to match a function, such as USERNAME()
- Parameters cannot have multiple values (no array concept)
These are both feature requests that you can go vote up on the Community forum, so go there now and then come back and continue reading!
Until these features are implemented, the only way to set these values dynamically is using Tableau Server’s ability to set parameters programmatically.