14 Replies Latest reply on May 14, 2016 5:58 PM by Matt Coles

    Email blast based on data condition...

    Shawn Wallwork

      Having research this a bit, it seems that in Tableau we can set up email subscriptions to fire emails on a schedule, and we can set up alerts based on Server errors/failures. But we can NOT fire off email alerts based on a conditional statement in the underlying data. (e.g. [Profit Rate]<15%). At least we can't do this within Tableau. (Please correct me and explain how to do this, if I'm wrong.)

       

      My client is running a MS SQL Server 2008 R2. It appears that MS SQL can be set up to do just what I'm trying to do: send email based on conditional statement.

       

      Question: Has anyone set something like this up? Any insights?

       

      Thanks,

       

      --Shawn

       

      PS: I know this isn't strictly a Tableau question, but it is certainly a Tableau need, so I thought I'd ask it here for all T-folks to learn from.

        • 1. Re: Email blast based on data condition...
          Matt Coles

          Hey Shawn. I came to the conclusion a few months ago that I desperately needed a flexible email-alerting mechanism based on dashboards hosted on Tableau Server, so I started my own little project to try and achieve it. It was an interesting problem to try and solve. Right now, it is working on a simple level--if there is any data displayed in the viz, it counts as a trigger, so a simple email gets sent with the data attached in CSV form along with an embedded .PNG image of the view itself. To set up the alert, you just publish a new view, or customize an existing one if you like, and then subscribe to it on a special schedule using the Tableau Server interface. That schedule informs the script as to how often to perform its test on the trigger view, and if it comes back positive, the email is sent.

           

          It has an "advanced" feature that allows customizing to/from/cc/bcc, subject and body, and can send multiple emails per server view depending on what the data says. I'm rewriting that part a bit right now, so it's not as tested as the "simple" alert feature.

           

          Once this has been running in production for awhile and I've gotten some usage on it, and someone has had a chance to review my code (not the prettiest), I plan on sharing. But in the meantime if you want to try and write your own script to fire off an alert for specific vizzes, I'm more than happy to help explain how I'm doing it with mine.

          1 of 1 people found this helpful
          • 2. Re: Email blast based on data condition...
            Shawn Wallwork

            Matthew that's great to know, and yes please explain more about the script you're using! I'm sure we all will be most happy to help you test it. Certainly I will be.

             

            Cheers,

             

            --Shawn

            • 3. Re: Email blast based on data condition...
              Matt Coles

              Sure. The high level is that it's written in Python, and runs every minute via a Windows scheduled task from a machine outside the Tableau Server cluster. It runs a query against the PostgreSQL Repository that checks for subscriptions associated with disabled schedules with a certain naming convention (I prepended "Z" to the names so users don't see them in the list unless they scroll all the way down). If the "run next" time on those subscriptions differs from the last "run next" time from the previous iteration, it triggers the script to obtain a trusted ticket from Tableau Server under the subscriber's username. That ticket is then used to export the CSV of the viz in an HTTP call. Once we have the CSV, we check it for data--is there even 1 row returned? If so, we generate a new trusted ticket, and export the same viz as a PNG. Once we have both, an email is composed to the subscriber that attaches the CSV and embeds the PNG. Then it is sent, and it's off to the next alert that was to be sent.

               

              That's the "simple alert" feature. The "advanced alert" feature requires specific field names in the viz that contain the to, from, cc, bcc, subject, and body values for each email to be sent. It works the same way, but it will actually iterate through the CSV data and send multiple emails based on the values it sees there. It also has a feature for consolidating multiple CSV lines into a single email so that you compose fancier HTML out of multiple data values (for instance, if you wanted to send a user an email with an HTML table full of workbook names).

               

              I'll see if I can get some traction on finishing the advanced bit and getting it reviewed so it can be shared--it's still very pretty sketch right now. But the basic logic wouldn't be too hard to implement if you have a static list of Admin views you wanted to do this with--I could at least share snippets of the code you'd need.

              1 of 1 people found this helpful
              • 4. Re: Email blast based on data condition...
                Paul Banoub

                Closest I've seen is this third-party tool - http://www.metricinsights.com/solutions/push-intelligence-for-tableau/

                 

                No idea if it is any good, we are gonna have a look at it when we get the time.

                 

                It's certainly a valid ask, many of my users have asked for this functionality although I think there is a balance to be had as too much of this may push users towards using Tableau as a monitoring tool, which it isn't of course.

                1 of 1 people found this helpful
                • 5. Re: Email blast based on data condition...
                  Shawn Wallwork

                  Paul thanks for this. I just watched the video and it does look like it does everything we need. Of course there wasn't any pricing information, and sales didn't answer their phone, so I have to wait to see whether it's an 'affordable' options. I'll let you know.

                   

                  Cheers, and Welcome to the forums!

                   

                  --Shawn

                  • 6. Re: Email blast based on data condition...
                    Shawn Wallwork

                    ... And the answer is: $10K minimum for 20-seats on a yearly subscription basis, with $200 per year for all seats above the initial 20 seats. And that $10K+ is paid every year.

                     

                    Nice guy, and he did suggest that if we're only looking for email alerting it's probably not a fit.

                     

                    Guess I'll have to learn some Python. Or maybe @Kettan and I can work out a SQL solution.

                     

                    --Shawn

                    • 7. Re: Email blast based on data condition...
                      Shawn Wallwork

                      Paul Banoub wrote:

                       

                      It's certainly a valid ask, many of my users have asked for this functionality although I think there is a balance to be had as too much of this may push users towards using Tableau as a monitoring tool, which it isn't of course.

                       

                      (Emphasis is mine.)

                       

                      Paul I was with you all the way to the end. In my mind Tableau is primarily a 'data analysis/visualization' tool. That's its mission in life. But once we've analyzed the data and made policy corrections, hoping to 'improve' the situation, I think monitoring the results of our actions is a valid purpose for an analysis/visualization software. (That's the balance you're talking about, right?) It's all about perfecting the cycle through data analysis, right?

                       

                      I find email alerting a much more 'legitimate' feature request than anything dealing with exporting PDFs. If we are not monitoring the changes we implement, then why analyze data at all? What is the purpose of our effort?

                       

                      Cheers,

                       

                      --Shawn

                      • 8. Re: Email blast based on data condition...
                        Matt Coles

                        I agree. There is a lot of benefit to being able to set up alerts based on Tableau dashboards, within the Tableau environment itself. I'll list some of the benefits I see:

                         

                        1. SQL knowledge not necessary (though often helpful).

                        2. No SQL Server admin privileges required, anyone could (theoretically) publish their own alert viz if given permission.

                        3. Creating alerts based on blended data is far easier than in SQL Server

                        4. No additional (scripting) would be required. If you know how to author a workbook and publish it, you should be able to set up your own alert.

                        5. Data exploration goes hand in-hand with deciding what you care enough about to want to be alerted on. Tableau is a great tool for exploring your data, so if you've explored to your satisfaction and settled on an action that fits a given data condition, it's where your alert should also live--why jump into another system?

                        6. Availability of dashboards is limited to permissions you set--anyone could theoretically subscribe to an alert defined by another user if granted permission. SQL Server would likely require the author to add user emails to a table, or worse, the code to be alerted.

                        7. All your logic can be made available to anyone who is interested and has Download rights to your workbook. They can then engineer their own alert.

                         

                        I see a lot of opportunity in all this--very much worth the investment!

                        1 of 1 people found this helpful
                        • 9. Re: Email blast based on data condition...
                          Paul Banoub

                          HI Shawn - thanks for the warm welcome to the forums. I've looked on for a long time without ever getting involved.

                           

                          Shame about the pricing of MetricInsight - that's not workable for us either. I'm sure a realistic deployment price would involve some discounting on that but still pricey.

                           

                          With regards to Tableau as a monitoring tool I think we're missing each other a little. I agree with you about monitoring changes in data and acting accordingly, what I mean is that I don't think Tableau should be used as an enterprise level alerting tool as that's a whole subject in itself. Enterprise monitoring tools are big beasts with masses of features - look at Microsoft SCOM, HPOpenView, Netcool, Nagios or the excellent ITRS Geneos.

                           

                          I wouldn't want someone using a Tableau dashboard to monitor critical data changes, expecting near instantaneous updates and a mass of alert customisation ability. I can see that expectation developing amongst enterprise users.

                           

                          So I'm certainly in favour of basic alerting, but it would also need some user expectation management, at least in my environment.

                          1 of 1 people found this helpful
                          • 10. Re: Email blast based on data condition...
                            Bruce Segal

                            Paul: I happen to agree with Shawn. I can't tell you how often I've been asked to build a dashboard that shows outliers from an expected range of results. E.g., did our sales of online display ads drop or jump by 10% day over day. If they dropped that much then there might be a problem in our technology that we don't know about. If they jumped that much then we want to find out if there's a bigger opportunity.

                             

                            I wish that my end-users would just open up the dashboard every day and check the status, or be happy to get a pdf or .xls file automatically in email. (that can be done fairly easily) But they don't want to do that. They only want to get an alert when it's triggered.

                            1 of 1 people found this helpful
                            • 11. Re: Email blast based on data condition...
                              Paul Banoub

                              Hi Bruce, thanks for the reply.

                               

                              Not sure you've understood my reply fully. There's a big difference from what you're asking and an expectation that Tableau will be able to act as a full-on enterprise monitoring tool with near-instant alert capabilities.

                               

                              Your ask (and Shawns) is totally valid and one that I agree with. My point is that there's a danger people will want the full on Geneos / SCOM / Nagios experience from their alerting and if Tableau commit to something like that it will open up a whole world of aggro.

                               

                              Cheers

                              Paul

                              1 of 1 people found this helpful
                              • 12. Re: Email blast based on data condition...
                                Shawn Wallwork

                                I just wanted to close this thread out, and more importantly, recognize my hero this week: kettan! (He's actually my hero many weeks.) Kettan has a completely different skill set than I do, and is very willing to generously share his knowledge. (Andy Cotgreave that's one of my 'Community' touchstones.) I find Kettan's help invaluable, as I'm sure many of you also do.

                                 

                                Anyway, here's the (publicly-sharable) code Kettan crafted for me:

                                 

                                DECLARE @CSS NVARCHAR(MAX)
                                DECLARE @IntroHTML NVARCHAR(MAX)
                                DECLARE @AddendumHTML NVARCHAR(MAX)
                                DECLARE @tableHTML NVARCHAR(MAX)
                                DECLARE @tableHTML1 NVARCHAR(MAX)
                                DECLARE @sub NVARCHAR(MAX)
                                DECLARE @Color char(7)
                                
                                SET @Color = '#0060A8'
                                
                                SET @sub = 'Data Alert: Markup per Contract Out of Range'
                                
                                SET @CSS = N'<style type="text/css"> body {font-family: Arial, verdana;}'
                                    + ' p{padding: 0; margin-top: 5pt; margin-right: 0; margin-bottom: 0;
                                       margin-left: 0; color:'
                                    + @Color + '; background-color:#FFFFFF;}'
                                    + ' table{font-size:10px; border-collapse: collapse;}'
                                    + ' td{color:'+ @Color + '; background-color:#FFFFFF; border:1px solid '+ @Color
                                    + '; padding:4px;}'
                                    + ' th{color:#FFFFFF; background-color:' + @Color + ';}</style>'
                                
                                SET @IntroHTML =
                                    '<p>This email is sent to you because your data is outside target range.</p>'
                                
                                SET @AddendumHTML = '<p>See more details in the dashboard
                                    <a href="http://tableau02/views/Test-SW/MarkUpPerContract#1">Markup Per Contract</a>
                                    </p>'
                                
                                SET @tableHTML1 =
                                    N'<p><br><b> Markup </b></p><p>Please follow the link at the bottom.</p> ' +
                                    N'<table border="0">' +
                                    N'<tr><th> Contract_Division </th>' +
                                    N'<th> Contract_ID </th>' +
                                    N'<th> Contract_Consultant </th>' +
                                
                                    CAST ( ( SELECT
                                                 "td/@align" = 'left',  td = Contract_Division,      '',
                                                 "td/@align" = 'right',   td = Contract_ID,   '' ,
                                                 "td/@align" = 'left',   td = Contract_Consultant,    ''
                                             FROM dbo.View_my_contract_query
                                             WHERE Contract_Division = 'Services'
                                             ORDER BY Contract_Consultant ASC
                                             FOR XML PATH('tr'), TYPE
                                    ) AS NVARCHAR(MAX) ) +
                                    N'</table> ' ;
                                
                                IF @tableHTML1 IS NULL
                                  RETURN
                                
                                SET @tableHTML = @CSS + @IntroHTML + @tableHTML1 + @AddendumHTML
                                
                                EXEC msdb.dbo.sp_send_dbmail
                                    @profile_name = 'mailprofile',
                                    @recipients = 'recipient1@mycompany.com; recipient2@mycompany.com; ',
                                    @subject     = @sub,
                                    @body        = @tableHTML,
                                    @body_format = 'HTML' ;
                                
                                
                                
                                
                                
                                
                                
                                
                                
                                
                                
                                
                                
                                
                                

                                 

                                (Sub in your specifics and enjoy the results! FYI: This was run in MS SQL Management Studio. And then setup to run on a schedule.)

                                 

                                This code produces a nicely formatted (as always with Kettan) table within the body of an email that is sent out. As you can see it also uses a simple trigger. Even for a guy like me who doesn't know/read HTML, it was quite easy to figure out what he's up to. All I needed to do was to create a query that produces a null if a certain condition isn't met. (My query was very complex so I chose to do it as a CREATE VIEW, instead of putting it within the SELECT statement. If your's is simple, just stick it within the SELECT brackets.)

                                 

                                Anyway, this is a great template Kettan has crafted for our community.

                                 

                                And yes Matt Coles I very much agree with you that having this sort of alerting 'inside' Tableau is the best solution. Hope you'll continue to champion this for us inside Tableau; we'll continue to request it from the outside!

                                 

                                Cheers all, and thank you again Kettan, my hero.

                                 

                                --Shawn

                                 

                                [PS: Kettan please correct me if you found anything I said wrong, or misleading. Thx.]

                                • 13. Re: Email blast based on data condition...
                                  Matt Coles

                                  If you are interested, I've built a version of alerting that can do this natively in Tableau Server, with no SQL job necessary. All the work is being done by a Python script that the Admin sets up--but from then on it is self-service. Email can be sent with HTML markup to any set of recipients, with all content customizable based on viz data. All you need is Tableau Server. We have a private group going where you can test this solution on your own Server dev cluster--just let me know and I'll send an invite.

                                  • 14. Re: Email blast based on data condition...
                                    Matt Coles

                                    Just for posterity, the group is open now and requires no invitation: VizAlerts - Data driven Alerting for Tableau Server