Author: bryantbhowell

Row Level Security using Microsoft Analysis Services Cubes in an External- Facing Environment

Later versions of Microsoft Analysis Services (MSAS) allow you to configure user and role based data security within the cube itself. However, this functionality only works when that particular user is logged in directly to the cube. In Tableau, this can be accomplished via Kerberos.

What about when you are using MSAS cubes in an external facing solution, with users who are not in the local domain? Cube connections in Tableau don’t have the equivalent of a Data Source Filter the way relational database connections do, and there is no way to pass the USERNAME() function into a Calculated Member the way you can in a relational calculated field.

In this case, the manual “User Filter” functionality can achieve a reasonable solution.

(more…)

Publishing Extracts from a Template Data Source using tableau_tools

With the release of tableau_tools 4.0.0 and Tableau Server 10.5, most of the pieces are in place in the library and in the product itself to allow for an efficient path for publishing unique extracts for different customers all from a single original data source (or workbook) template.

The basics steps of the technique are:

  1. Create a template live connection to a database table, Custom SQL or a Stored Procedure in Tableau Desktop. This does not need to be the final table/custom SQL or Stored Proc; you can use a test or QA data source and switch it programmatically to the final source
    1. Optional: Set up your the appropriate filtering for a single customer / user / etc. — whatever the main filtering field will be. You can instead add this later programmatically.
  2. Save that file (TDS or TWB)
  3. Use the tableau_tools.tableau_documents sub-module to programmatically add any additional filters or modify the filters / parameters you set
  4. Use tableau_tools to alter the actual table / SP / Custom SQL to the final version of that customer
  5. Add an extract to that data source in tableau_tools. This will use the Extract API / SDK to generate an empty extract with the bare minimum of requirements to allow it to publish and refresh
  6. Save the new file. It will be saved as a TWBX or TDSX, based on the input file type
  7. Publish the file to Tableau Server
  8. Send an Extract Refresh command to Tableau Server using the REST API (using the tableau_tools.tableau_rest_api sub-module).
  9. Extract will refresh based on the information in the TDS and be filled out with information just for the specified customer/user/whatever you filtered

(more…)

Isolating Tableau Server Performance Issues

In this post, I’ll be describing a set of steps to follow to isolate the causes of performance issues on Tableau Server.

Here are the basic steps:

  1. Test the workbook in Tableau Desktop. Does it perform well? If yes:
  2. Test the workbook in Tableau Desktop on the Tableau Server machine. Does it perform the same as it did on the previous machine? If yes:
  3. Publish the workbook to Tableau Server, and find a time when there is low-to-no usage on the Tableau Server. Go to the published workbook. Did it perform relatively the same as the test in Step 2 (within 1-3 seconds)?  If yes:
  4. Test the workbook during a time of high usage on the Tableau Server (either natural or do load testing using TabJolt).

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

Securely Passing Parameters into a Tableau Viz at Load Time

The standard answer for enforcing user-based data entitlements in Tableau is to use Row Level Security, where the user is authenticated in Tableau Server and then tied into an “entitlements view” in the database so that the user only ever sees data they have access rights to.

However, we are very often asked about passing parameters in to the viz to filter down information directly at load time, often driven by an application that Tableau vizes are embedded in. This post is about a few methods of implementing this behavior, and the security implications of each of them.

Basics of Security

Everything must be HTTPS

I’ll start by saying, to do any of this securely, you need EVERY resource you are working with to be using the HTTPS protocol (latest TLS version). If anything is not HTTPS, you could be passing important information in the clear.

Using URL Parameters to set a Filter directly is NOT SECURE

You can use the URL Parameter syntax to directly set the values for a Filter on any field, but this is completely insecure. Why? Because the following two methods will clear any filter and reveal all of the rows of data. Unless you have the JS API turned off, there is no way to prevent this.


Sheet.clearFilterAsync(fieldName);
Sheet.applyFilterAsync(fieldName, "", tableau.FilterUpdateType.ALL);

Tableau Parameters are the (potentially) secure way to make an adjustable Data Source Filter

The only way to prevent a user from resetting a filter value is by making it a Data Source Filter.  Thankfully, you can use a Calculated Field for the Data Source Filter. If you use Tableau Parameters in the Calculated Field, the Parameter value(s) can be set to change what is filtered, and you will have a Data Source Filter that cannot be altered by the JS API (or the end user).

However, there are quite a few considerations to make this a truly secure method for setting filter values:

(more…)

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

Republishing Extracts from One Site (or Server) to Another with tableau_tools

Imagine you have a Data Source (in a workbook or outside of one) which is an extract, refreshing on a schedule. But that same data could be used on a different site, or a different server. There are lots of reasons to have logical partitions that basically need a copy of data, particularly related to security. You might have an internal server that connects to data sources allowing the refresh, but want to push that content to a server that eventually connects to the public Internet.

The REST API allow for this fairly easily — you simply download the first workbook, then republish to a different site with the Save Credentials options set to “False”. No credentials means the extract can’t update, but that’s exactly the idea behind this exercise — you want no way to access the database.

Note: The Tableau workbook and data source files will still contain some information about the original live data source that the extract was created from, but no passwords (no credentials are passed to the second site/server). If you need complete lock-down security, I can try and explore how much you can blank out of the XML while still publishing successfully.

This is very easily accomplished via tableau_tools:


# -*- coding: utf-8 -*-

from tableau_tools.tableau_rest_api import *
from tableau_tools import *
import time

o_server = u'http://'
o_username = u''
o_password = u''
o_site_content_url = u''

logger = Logger(u'move.log')

d_server = u'http://'
d_username = u''
d_password = u''
d_site_content_url = u''

t = TableauRestApiConnection26(o_server, o_username, o_password, o_site_content_url)
t.signin()
t.enable_logging(logger)
downloaded_filename = u'File Name'
wb_name_on_server = u'WB Name on Server'
proj_name = u'Default'
t.download_workbook(wb_name_on_server, downloaded_filename, proj_name_or_luid=proj_name)

d = TableauRestApiConnection26(d_server, d_username, d_password, d_site_content_url)
d.signin()
d.enable_logging(logger)
proj = d.query_project(u'Default')
d.publish_workbook(u'{}.twbx'.format(downloaded_filename), wb_name_on_server, proj, save_credentials=False, overwrite=True)

 

 

Killing a Tableau Server Session

Within an embedded application, it can be difficult to make sure that sign-out is achieved in both the application and Tableau Server. Tableau Server supports SAML signout commands, but for all sorts of reasons, this might not always work.

Luckily, it is possible to use the REST API to kill any session programmatically, but you need the session identifier from the Tableau Repository. The question is, how do you know what session belongs to a user? There is a sessions view, but you need a little bit more to get filtering down to the username level:

SELECT
sessions.session_id,
sessions.data,
sessions.updated_at,
sessions.user_id,
sessions.shared_wg_write,
sessions.shared_vizql_write,
system_users.name AS user_name,
users.system_user_id
FROM sessions
JOIN users ON sessions.user_id = users.id
JOIN system_users ON users.system_user_id = system_users.id
WHERE system_users.name = '{username}'

Once you have the session ID, you can send a REST API sign out command.

tableau_tools has both of these commands wrapped in a simple interface.

server = 'http://127.0.0.1'
username = ''
password = ''
readonly_user_password = ""
d = TableauRestApiConnection25(server, username, password)
d.enable_logging(logger)
tab_rep = TableauRepository(server, readonly_user_password)
uname = 'some_username'
sessions_cur = tab_rep.query_sessions(username=uname)
for row in sessions_cur:
    d.signout(row[0])

The signout() method has an optional parameter called “session_token”, that accepts the values of the session_id column of the sessions table and _sessions views (when they are set to the old style of ID — see below). Older versions of tableau_tools had you set the token property directly, but the only way to accomplish this now is through the signout(session_token=”session_id”) method.

Using this in later versions of Tableau Server (10.1+)

In Tableau 10.1, there was a change to the structure of Session IDs to provide additional security features. Unfortunately, this broke the technique listed above. If you do have the need for manually killing sessions via the REST API, you’ll need to revert back to the previous type of Session ID using the following tabadmin setting (or its equivalent in TSM in 2018.2+)


tabadmin set features.ProtectVizPortalSessionIds false

tabadmin configure

Yes, you will need to restart the server for that change to take effect.

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.

(more…)

Exporting Scrolling Worksheets in a Dashboard to PDF

Let’s say you have a Dashboard in Tableau, where at least one Worksheet is a list long enough to have a vertical scrollbar. If you export to PDF in Tableau Server, you will only get an single page, with an image of the scrollable sheet at the top of its scrolling. If you want to give your users the ability to get a PDF with ALL of the data included, you’ll need to set up your workbook in a particular fashion, based on the following rule:

Worksheets that scroll will export out as multiple pages, showing all of the data. Dashboards export out as a single page always.

With this in mind, you can build your Workbooks in the following way:

  1. Put your Dashboard tab first
  2. Put any sheets that scroll in a dashboard directly after the Dashboard tab
  3. Hide any sheets that do not scroll in a dashboard

scrollable-pdf

The user needs to choose “Sheets in Workbook” to get both the Dashboard and the Sheets with all the details, but you can choose which sheets you want in the selector at the bottom.

(more…)