Analytics für alle
Die vor-Ort Lösung für Unternehmen.
Die gehostete Lösung für Unternehmen.
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.
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:
The following bullets are some Tableau specific best practices that the Fortune 100 joint customer employed and benefited tremendously from:
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).