Email alerts with Tableau

27 Feb 2014, by Alexis Guinebertiere

What is alerting?

Alerting is a common Business Intelligence feature. Instead of refreshing a dashboard or report regularly to see if something went wrong in your business, you would receive an email alert automatically. Some call this managing by exception.

The event that triggers the alert could be a metric crossing a threshold, such as an inventory level dipping below the reorder level. In this example, the email subject would look like the following:

"Alert! Inventory for Widget A is 25, which is below the reorder level of 30!"

The content of the email would be a report that shows the metric in context. In our example this could possibly be the inventory level trend.

Does Tableau offer alerting?

While it does provide system administrator alerting capabilities for system health issues, Tableau 8.1 does not provide business user alerting capabilities.

That was the bad news.

The good news is that Tableau is actually quite gifted at both implementing business rules and producing static content such as images and PDFs.

So what’s missing? Mainly the emailing mechanism. What we used to call “mail merge” in Microsoft Word in 1995: take a list of recipients, take a template email, and merge them to send out emails.

How we are going to implement alerts

To close this gap and turn Tableau Server into an alerting server, here are the overall tasks:

  1. Create a workbook that monitors inventory levels. A tab will identify the managers that need to be alerted, and another tab will have the content that needs to be emailed out

  2. Install the ruby development environment. Luckily there are one-click install packages out there.

  3. Configure Tableau Server to accept requests for trusted tickets. This does two things for you. First, the script will not need to know any password, it will simply request trusted tickets to perform its work. Second, ans this optional, it enables the emailing script to impersonate the recipient of each email instead of using filters to filter the relevant information. This impersonation is necessary if you have row-level security implemented in published datasources.

  4. Install an SMTP mail server if you don’t already have one

  5. Test it! We’ll run the script on the sample workbook I provide here, and see the emails go out!

Sample data and workbook

To help us with this exercise, I put together a (very) simple dataset and workbook. The download links are below in the TODO list. The dataset is stored in an excel spreadsheet, one table per tab, and looks like this:

inventory

product inventory

reorder level

product inventory

managers

product inventory

As you can see, plates are managed by both melanie and christophe. Other products are split between these two managers.

The workbook contains two tabs. The first tab shows the products for each manager. All three tables have been joined together. A T/F calculated field on alert indicates if the product is on alert. It is defined as [inventory] <= [reorder_level].

product inventory

The second tab shows the managers we need to alert. We simply added the on alert field as a filter that keeps true.

product inventory

Setting up the workbook and publishing to Tableau Server

TODO

  1. Download the excel spreadsheet, save it to a location accessible by your server, e.g. your desktop if Tableau Server is installed locally, or a shared folder if Tableau Server is on a separate machine.

  2. Download the inventory alerts workbook

  3. Open the workbook, edit the data source connection, point it to your copy of the spreadsheet on your desktop - use the shared folder URI if your are using a shared folder, e.g. \\sharedfileserver\ folder\inventory.xlsx

  4. Save and publish to Tableau Server. Make sure to uncheck the Include External Files on the publish dialog. This ensures that the workbook is still connecting to the original spreadsheet, and not static a copy on the server.

Conventions for the workbook

The workbook should have at least two sheets:

SheetPurpose
recipientsThe recipients sheet should contain two columns. The first one has the name of the recipient, the second one has his/her email address. The heading of the column does not matter.
contentThe content sheet has the content that will be emailed. It should optionnaly have a field that we can use to filter, e.g. in our example workbook the manager field can be used to filter and customize this sheet for a particular recipient. As an alternative to the filter field, the ruby script can use trusted ticket to sign into Tableau Server and impersonate the recipients.

Setting up ruby

TODO

  1. Head over to Ruby Installer and download the ruby installer. As I am writing this the latest version is ruby 1.9.3.

  2. As the name indicates, this is an installer, specially for Windows. Simply run the .exe, click through the setup wizard.

  3. Open a command prompt, type irb, which is the interactive ruby shell

  4. Type print &#39;Hello, world&#39;

  5. You have written your first line of ruby! Close this command prompt.

Testing ruby

Setting up an email server

If you already have an SMTP server that you can use, skip this test. Otherwise:

TODO

  1. Install an SMTP server. I like hMailServer but any mail server will work.

Enable trusted tickets

This step enables the script to run reports on behalf of the administrator, and if needed, on behalf of recipients too.

To enable trusted tickets,

TODO

  1. Figure out the IP address of the machine that is going to run the alerting script. You can do this with a command prompt and the ipconfig command.

  2. Follow the instructions on how to setup trusted tickets on the Tableau online documentation.

Download the script and test it!

We’re ready to test this!

TODO

  1. Download the tabalert.rb script.

  2. To test it out, open a command prompt, and type:

ruby tabalert.rb inventory_alerts Default 1=1 manager

Let’s review the parameters:

ParameterValue in our ExamplePurpose
workbooknameinventory_alertsThe name of the workbook as published on Tableau Server
sitenameDefaultThe site you published your workbook to
additional url string1=1This will be added to the URL when requesting both the list of recipients, and the content to be sent out. Use this to pass, for example, a parameter value. Or leave it at 1=1 which does nothing.
trusted or field to filtermanagerIndicate here trusted if you want to customize the content sheet by impersonating the recipient, or a field name to place a filter on this field and pass the name of the recipient. In our example, the manager field will be filtered using values found in the first column of the recipients sheet.

Things you may want to customize in the script

Search for this line. You’ll want to replace localhost with your own mail server:

Net::SMTP.start( 'localhost' ) do |smtp|

Comments

comments powered by Disqus