Friday, February 14, 2014

Individual Query Auditing with Tableau

Tableau Server has great built-in audit tables for who has looked at what content and when. We discussed this briefly in a previous post related to performance monitoring. What is NOT built-in to Tableau Server, however, is the ability to audit detailed field-level usage, or put in plain terms: who filtered what? (in other words, what was the resulting WHERE clause and WHO ran it?)

It turns out that if you are clever you can get this to work. Here's the brief overview of steps required:

  • In your database, enable tracing - this is not covered in this blog post!
  • Be prepared to report on this tracing. This could be with Tableau Desktop, or some other tool. In the example below, we used SQL Server and Tableau Desktop and only those two tools.
  • On all of your visuals, create a calculated field that tracks the current Tableau Server "Username". You also need to add a unique string with which to grep the answers later on.
  • Optionally, you can add this calculated field to Tableau data source filters - read more about that feature here and here. This is super awesome because it means that you will have enabled detailed auditing not just for Tableau Server web users, but also for any Tableau Desktop users as well!
  • In the example shown below, because we have a calc which results in "SPECIALTRACKINGKEY"+Username, we can then filter out all of the noise in our tracing data, and focus only on the Tableau query usage. Nifty.

In order to publish this workbook to Tableau Public, we first had to delete the magic calculated "tracking field" (Tableau Public does not support user filtering, and nor should it). Here is the definition of that field:

"SPECIALTRACKINGKEY" + Username()

You will need to add this back into your example in order for any of this to work. This subject is generally not for the faint of heart, so take your time and best of luck. Enjoy!

Individual database information for enabling SQL tracing (I'll try to add to this list as time permits):

1 comment:

Jack Horovic said...

As the most convenient application for calculation I used observational audit app https://form.com/mobile-audit/ following the advice of his partner, who knows a lot in this, much more than I know. If you haven`t yet found a way to monitor and improve performance based on compliance with the audit, then this is exactly the application you need to download and test. The result will be very successful and will justify your expectations regarding automation an audit calculations.