Optimizing your Tableau and Azure SQL DB connectivity

Tableau’s ability to integrate with Microsoft technologies has helped businesses around the world grow stronger and achieve more. As Microsoft pursues its cloud-first strategy, Tableau is there, allowing you to put the full weight of the Azure platform behind your analytics. Along the way we’ve enabled joint customers like Cox Automotive and Hertz, to transform their organizations with self-service analytics in the public cloud.

In recent years, Microsoft SQL and Tableau’s engineering teams have worked closely together to provide superior user experience and improve customer satisfaction with the two platforms. We’ve learned a lot, and today we are sharing some valuable advice on how to optimize the connectivity between Azure SQL DB and Tableau.

Our teams previously teamed up for SQL Server 2016 launch and for Azure SQL Data Warehouse launch. To date, SQL Server is Tableau’s most common data source (in combined cloud and on-premises usage), as detailed in the recent Tableau Cloud Data Brief, which makes our partnership vital for our customers.

Our engineering benchmarks, and several global customer engagements have led us to explore how to leverage the best of both platforms to achieve optimal connectivity and performance. In one such engagement, a Fortune 100 mutual Tableau/Azure SQL DB customer provided our teams full access to their environment and key datasets that we could test and optimize. Over the course of this engagement—which generated excellent “engineer-to-engineer” level dialogue across four time zones—our teams collaborated with the customer to perform numerous performance tests. Then, we documented key takeaways for maximizing performance.

Here are some chief learnings about Azure SQL DB and Tableau:

Out-of-the-box experience works well
Beyond the self-indulging statement, we observed that most customers fared well by simply replicating their on-premises approach, or hitting Azure SQL DB directly. Azure SQL DB uses the same drivers as SQL Server 2016, which reduces complexity here. As you might have already noticed in Tableau Desktop UI, there is a single SQL Server connector for Azure SQL DB, Azure SQL Data Warehouse or SQL Server 2016 - running on premises or in a public cloud like Azure.

Tableau Live Querying provides the best performance
Network bandwidth permitting, live-querying Azure SQL DB with Tableau allows the heavy lifting to occur in Azure SQL DB, while also providing more up to date information to Tableau users - as opposed to extract based connectivity (There are a number of reasons you may still need extracts for, as spelled out in great detail by Tableau Zen Master Jonathan Drummey here). This implies doing some sizing and performance testing with different Azure SQL DB SKUs. In our experience, Azure SQL DB latency and throughput can meet the most stringent Tableau requirements.

For example, we advised a joint customer to move from S0 (10 DTUs) to P1 Premium (125 DTUs), which instantly removed latency issues. The cost impact is commonly offset by user and customer satisfaction.

We also wanted to provide some recommendations on how to monitor and ensure optimal performance of your “Tableau on Azure SQL DB” deployment. As Tableau customer advisor extraordinaire Alan Eldridge mentions in his seminal Tableau whitepaper, Designing Efficient Workbooks, “there is no silver bullet for inefficient workbooks.” Start your monitoring by looking at Tableau’s performance recorder to understand where the time is going. Are there long-running queries? Do you have lots of queries? How slow are your calculations? Is there complex rendering? Use these questions to drilldown on further insights to focus your efforts in the right direction.

Let’s start with the data source. “If it is slow in the data source, it will be slow in Tableau,” says Alan in Designing Efficient Workbooks i.e. performance issues, in most cases, can be traced back to a poorly tuned database. Our friends at Microsoft have put together a full, exhaustive list of tuning steps and checks that are worth performing to make sure your database is responsive.

Three of my favorite tips from this list are:

• Check for index recommendations and usage
• Prioritize statements based on highest execution time
• Examine top queries and associated execution plans (Use performance recorder to identify long running queries)

The following bullets are some Tableau specific best practices that the Fortune 100 joint customer employed and benefited tremendously from:

• Isolate Date Calculations: As much as possible, put together information that can be pre-computed. Tableau will compute it once and the database may be able to use an index.
• Use Boolean Fields: Don’t use 0 and 1 as indicators for true and false. Use Boolean fields as they are generally faster.
• Don’t Change Case: Don’t put UPPER or LOWER in comparisons when you know the case of the values. The database doesn’t know the case, so it will apply the UPPER or LOWER as directed.
• Use Aliases: Where possible, label text using Tableau’s alias feature, rather than in a calculation. Aliases aren’t sent to the database so they tend to be faster.
• Use formatting when possible: Don’t use string functions when you can just use formatting. Then use aliases to label the fields.
• Replace IF / ELSEIF with CASE: It’s a good idea to do this as CASE statements are generally faster.

This is a subset of techniques the joint customer used to make their deployment fast and responsive, but we highly recommend reading the aforementioned whitepaper, Designing Efficient Workbooks by Alan Eldridge, for the exhaustive set of best practices, which apply to all scenarios across different volumes, velocity, and variety of data.

We hope this is useful to you and we’re curious to read your comments and feedback on how you use Tableau and Azure SQL DB. Note that your Azure free trial includes $200 you can use against Azure SQL DB. If you are running your infrastructure and applications in Azure today, remember that Tableau Server is certified to run in Azure, so you can have your entire BI and analytics platform running seamlessly in the Azure cloud.

And, finally, if you’re looking at more complex deployment scenarios and want to upgrade your Tableau and Azure skills, we’d recommend a look at our Tableau and Cloudera Quickstart Azure template.

Acknowledgments: This article is a collaboration between several people. Special thanks to Dan Cory (Tableau), Joe Sack (Microsoft), Nico Caudron (Microsoft) and Gil Isaacs (Microsoft).