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

Advertisements

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

tableau_tools 4.4.0 released!

I haven’t been announcing the minor point releases of tableau_tools lately, but 4.4.0 is out with a lot of good new stuff:

  • Updated to work with the Extract API 2.0, so you can add the necessary Hyper extracts to 10.5 data sources and workbooks
  • Fully updated and documented mechanism for altering the main table of existing data sources. Change the table name or Custom SQL or…
  • Stored Procedure Parameters can be accessed and set
  • Tableau Parameters can now be added, removed or modified

As always, preferred install is from PyPi using pip install tableau_tools –upgrade or you can see the source at the Releases on GitHub. See the full documentation in the README .

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

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

tableau_tools 4.3.0 released!

tableau_tools 4.3.0 is now up and available on PyPi and GitHub!

If you’ve installed before, just run

pip install tableau_tools --upgrade

There’s lots of good stuff in this release:

  • 100% implementation of the spec. If it is in the Reference Guide, it’s possible through tableau_tools. There are even a few things that aren’t in the reference guide 😉
  • 10.5 / API 2.8 compatibility
  • Vastly improved README file, covering almost all topics
  • Code refactoring broke up some of the larger library files into easier to understand pieces
  • So much more!

As always, please let me know through GitHub if there are any issues.