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

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.



Flexible Trellis Views in Tableau

Tableau does small multiples really well, but if you really only have one dimension, the only way to display things is in a straight list. With a couple of simple calculations, you can transform that list into a trellis, making 2 dimensions out of one!

Trellis Pies

If you look at the screenshot, you’ll notice that the Columns and Rows are both calculations, Table Calculations to be precise.  These are generic calculations that can be added to any workbook.

[Number of Columns] is an Integer type Parameter, necessary for the modulo and division calculations..

Column Calc

( INDEX() – 1 ) % [Number of Columns]

Row Calc

INT ( ( INDEX() – 1) / [Number of Columns] )

How do these calculations work? The Column Calc is using the Modulo operator (%), which only returns the remainder from division. The remainder will be the same for each multiple in a list — i.e. 3, 5, 7 all have 1 as a remainder when divided by 2. This allows for grouping in the columns.

The Row Calc is basically cutting the Index into as many slices as there are columns.

You set both of these calculations to be Discrete (blue pills) and they will create individual “slots” in a trellis.

In the screenshot, the “listing” element is Sub-Category. So the Compute Using option of both calculations should be set to Sub-Category. This makes the INDEX() function calculate down the Sub-Category field. This field must be on the Marks card somewhere — at minimum in the Detail. You can add in additional fields to make more interesting displays, like the pies. It’s also a great way to display multiple text elements without them being straight lists.

Deselect “Show Headers” and the trellis layout calcs disappear to the end user.

Trellis Text

tableau_tools 4.0 is coming soon

I don’t normally post about things before they are ready, but tableau_tools 4.0 is getting close to release, and I am excited about how much it has improved. This is the big one, and if will probably break all your old scripts, so get ready for some cleanup, but I promise it is worth it.

The 4.0.0 branch is available on GitHub, and you can preview the README files now

tableau_rest_api README

Here are all the big changes:

  • Up to date for version 10.3 / API version 2.6: All methods from all versions are now supported
  • tableau_documents updated to handle version 10 style data sources with cross-database connections.
    • Vastly simplified model of: TableauFile->TableauDocument (Datasource or Workbook)->Datasources->Connections
    • Most getter/setters replaced with properties
    • Merging of the DatasourceGenerator with the actual TableauDatasource class so that single class can work with existing datasources to modify or build one entirely from scratch
    • Improved handling of large workbooks and datasources
  • Massive cleanup of all REST API methods: There is now one method to do anything related to that action, with optional parameters if you need more specificity.
  • Name vs. LUID lookup built in: Every method that can autodetects whether a LUID is being passed, and if not, does a name lookup automatically. You can use real names in almost every method without having to do the lookup first
  • Versioning handled by subclass inheritance. “What is this?” you may be asking. Basically, tableau_tools now implements each new version of the Tableau REST API as a subclass of the previous class, so it retains all features of the older versions, while adding new methods and updating those that have new features. Subclasses for versions are named like: TableauRestApiConnection23 or TableauRestApiConnection26
  • All permissions are handled through the PublishedContent classes: Project, Workbook, Datasource
    • All work on Projects is handled through a Project object.
    • Workbooks and Datasources still require requesting their PublishedContent objects
    • GranteeCapabilities object renamed to Permissions
    • Changes to Permissions must be accomplished via Permissions object
  • lxml removed and replaced fully with Python standard library ElementTree. No more need to install lxml binaries on Windows!
  • All XML requests are now built via ElementTree rather than as text strings
  • docstring typing for all parameters and returns. If there’s no docstring, the method hasn’t been updated yet. This means PyCharm or your other IDE can tell if you are passing in the right types to a given method. Some people will complain this is less Pythonic, but I promise type-safety is incredibly useful

Future goals for the 4.X series:

  • Python 3.3 compatibility
  • Using the requests library for HTTP calls (if there is any benefit, might be worth it for Python 3)

Behold! Emailer 1.2 Introduces Bulk E-mail Mode

The original release of Behold! Emailer was focused on the need of Tableau Server users who wanted to schedule e-mail reports of a PDF from a full workbook. In version 1.2.0, available on GitHub, a Bulk tab has been added to allow for running through lists of jobs to send out to any users. This is useful when you need to push out static results, perhaps filtered on certain factors, to sets of users who are not on the Tableau Server. As long as the output is a static PDF or CSV and not a TWBX, this falls within the Tableau EULA (at the time of writing).

How does it work? Create a CSV file (including the headers) on the following pattern:

To:,CC:,BCC:,Site,View Location,Filter Field Name 1,Filter Values 1,Filter Field Name 2,Filter Values 2,Filter Name 3,Filter Values 3,...

You’ll notice the ellipses at the end (don’t actually include them). That signals that you can make as many slots for additional parameters and filters as you need (up to 25). Just follow the naming convention for the additional fields.

To include multiple items for filtering, separate them with a semi-colon (“;”), like the following:

To:,CC:,BCC:,Site,View Location,Filter Field Name 1,Filter Values 1,Filter Field Name 2,Filter Values 2,Filter Name 3,Filter Values 3
aperson@tableau.com,,,default,BasicSuperstoreSQLServer/Sheet1,Category,Technology;Office Supplies,YEAR(Order Date),2013;2014,,

The program will automatically convert things into lists or change the semi-colons into the right separators. All URL encoding is handled automatically — just make the CSV and load it in. Reference things exactly as you would in Tableau (notice the YEAR(Order Date) example above), with no spaces between the commas.


Developing and Deploying Tableau Content

When you use Tableau in an embedded solution, the “Tableau method” of Desktop users publishing in a free-form method to the Tableau Server on a single site often doesn’t cover the requirements for controls and QA that a software development effort requires. While the classic “dev-test-qa-prod” is not as necessary with Tableau,  any SaaS customer that is embedding Tableau will need a process for deploying Tableau to their customer’s sites from templates.

In this post, I’ll go over the deployment methodology my team recommends to customers. We’ll be looking at the two separate phases, which are often split into different teams: (1) Development (2) Deployment.

Many thanks to Tyler Dugal who helped develop much of this content for our presentation at TC16.


Defaulting to Today (Relative Date) on a Date Range Filter in Tableau

Tableau has two different type of Continuous Date Filters: Relative Date Filters and Date Range filters. While it’s easy enough to switch between them in Desktop, there is no way to flip a Relative Date filter into a Date Range Selector in a view published in Server (without using Web Edit). What if you’d like a default date range relative to today to be selected, but give the user the option to make their own selection?

The classic solutions have involved parameters , but if that is too clunky, here’s a new solution, based on Tamás Földi’s fantastic solution for using the JavaScript API within a published dashboard. A similar solution could set Parameters to a default value rather than Date Range filter. You could even pipe the Tableau Username back into the viz as a parameter automatically for use as a Parameter in Custom SQL (not secure, but at least automatic).