Tableau Driver's Ed is a blog series in which we explain InterWorks' unique take on Tableau Drive, a new methodology for enabling self-serve BI in the enterprise.
It’s worth exploring the data you use with Tableau a bit further. In our last article of the Driver’s Ed series, we talked about adding data sources to your Tableau team. The business side sources the data, and the IT side incorporates and manages it. Tableau offers another distinction when it comes to using data in Tableau Desktop, and that is how you connect to your data.
In today’s article, we’ll explore live connections vs. using a data extract. This might be old hat for the seasoned Tableau expert, but let’s review the difference between a live connection and a data extract. A live connection means that Tableau is working directly in your data source. The first thing that needs to be said is that there are circumstances when each should be used.
The data queries that must be implemented use the data source’s resources to compile. If you’ve got a fast data infrastructure, then using a live connection makes a lot of sense. If you have a large database with notorious slow queries, then you’re creating a bottleneck for performance. Tableau is only as fast your data.
One of the big reasons to use a live connection is if your data updates frequently or your reports need to have real-time numbers. In that case, a live connection is the only option. If you find yourself in this situation and your queries are slow, one relatively easy solution is to make summary tables in your data source to reduce the amount of queries that must be run each time Tableau renders a visualization. This may require your database administrator depending on your privileges. Your DBA can also look into further tuning your database or making needed system upgrades to keep things running smoothly.
The other alternative are data extracts. If you have a large, complex, cantankerous data source and you do not require live or near live data, then using a data extract is probably the best option. A data extract is served by the Tableau Data Engine, which eliminates any reliance on database resources. In addition, the Tableau Data Engine is designed for quick analysis without indexing.
The size of your data extract will affect performance, so the number one rule in creating data extracts is to only grab the tables and fields that are absolutely necessary to complete your dashboard. If your data extract contains many millions of rows, performance might be impacted and you’ll need to reconsider how you access your data. This might be a leaner data extract or a live connection to summarized tables in your data source. One great method is to use Tableau’s built in ability to summarize data as it builds the extract and then use a live connection when users need to drill into details.
There are times when it makes sense to use a combination approach:
- Experimentation. If you’ve got a massive data source and you’re creating a new dashboard, consider using an extract with a workable sample size to create a proof of concept viz. Once you’ve ironed out all the bugs and created a compelling dashboard, then you can remove the extract and use a live connection.
- Offline Analysis. Using a data extract can allow you to continue to work in Tableau Desktop even if you don’t have internet access or access to your data. Create an extract on your PC and continue working. When you’re able to reconnect back to your data source, switching your data source from an extract back to the live connection only takes a few clicks.
Preparing your data sources for Tableau analysis is as much an art as it is a science. There are definitive guidelines on what to do and what not do. What makes it super confusing is that these do’s and don’ts are circumstantial based on your particular reports, deployment, databases, etc.
Creating efficient data connections is one of core competencies that InterWorks offers for our clients. If you need Tableau consulting for your organization, contact InterWorks today and let our team help take your Tableau capabilities to the next level.