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:
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
Install the ruby development environment. Luckily there are one-click install packages out there.
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.
Install an SMTP mail server if you don’t already have one
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:
As you can see,
plates are managed by both
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].
The second tab shows the managers we need to alert. We simply added the
on alert field as a filter that keeps
Setting up the workbook and publishing to Tableau Server
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.
Download the inventory alerts workbook
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.
Save and publish to Tableau Server. Make sure to uncheck the
Include External Fileson 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:
|The 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.|
|The 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 |
Setting up ruby
Head over to Ruby Installer and download the ruby installer. As I am writing this the latest version is ruby 1.9.3.
As the name indicates, this is an installer, specially for Windows. Simply run the .exe, click through the setup wizard.
Open a command prompt, type
irb, which is the interactive ruby shell
print 'Hello, world'
You have written your first line of ruby! Close this command prompt.
Setting up an email server
If you already have an SMTP server that you can use, skip this test. Otherwise:
- 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,
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
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!
Download the tabalert.rb script.
To test it out, open a command prompt, and type:
ruby tabalert.rb inventory_alerts Default 1=1 manager
Let’s review the parameters:
|Parameter||Value in our Example||Purpose|
|inventory_alerts||The name of the workbook as published on Tableau Server|
|Default||The site you published your workbook to|
|1=1||This 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.|
|manager||Indicate here |
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|