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:
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.
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.