Tableau: TDE Vs LIVE Connection
This is the very beginning of my technical blog writing with Medium. In my first post, I have discussed Tableau because it’s been almost a year when I started using it. I would like to thank my mentor Milankmr who encouraged me to pen down and share my learnings with you all. I hope you’ll enjoy it and will gain some insights from it.
If you’re getting started with Tableau — right after connecting your workbook to the data source, you should be able to figure out and understand whether you would need a Tableau Extract or a Live connection (at times both though !). In this post, I’ll be giving you insights about Extracts Vs Live — Which one you should use and why, and what are the pros and cons of both.
So, Let’s get started!
What are Extracts?
Extracts are one of the most powerful but overlooked tools in Tableau’s arsenal. Tableau Data Extracts (TDEs) are compressed snapshots of data optimized for aggregation and loaded into system memory to be quickly recalled for visualization, accordingly. So, the database isn’t required to build your viz.
A TDE is a columnar store which means it stores values together within a column rather than a row. By only reading in the relevant data (columns) necessary to answer the question, the input and output necessary to query and aggregate data is largely decreased. Therefore, extracts are often faster (but not always!). To understand more about how TDE’s design makes it ideal for supporting analytics and data discovery, you can go through the below blog to know it’s architecture: https://www.tableau.com/about/blog/2014/7/understanding-tableau-data-extracts-part1
There are two options to refresh the data in the extract:
Full Extracts: A full extract rewrites the existing data extract in Tableau data Engine with a new file from the Tableau data source
Incremental Refresh: It will help to add new records that have been added since the last extract has been created. This can be particularly useful if your data extract is to be refreshed daily. You can do so by selecting the checkbox Incremental Refresh in the Extract Data Dialog box
What is a LIVE connection ?
The data source that contains a direct connection to underlying data, relies on a database for all queries and undergoes real-time updates is a live connection.
How to identify if you have created a TDE or LIVE connection ?
Below are the symbols that are used to represent TDE and LIVE connection:
After you have selected your data source, and chosen one of the options from LIVE and Extract in your Data Source tab, you can find the data source name with a symbol of TDE or LIVE connection in the upper left corner of Data pane in your Tableau sheet. This symbol denotes the TDE and LIVE connection. I have also attached the snapshots to know where you can check for these symbols.
When to use Extract and LIVE ?
In addition to choosing the data source in Tableau, you need to decide whether you want to use live or static data. In other words, do you want your analysis and visualizations to be based on the most current data that’s available, or do you want to use an extract that provides a snapshot of the results based on criteria that you select?
Here I have listed — Pros and Cons of both TDE and LIVE which will help you decide the connection based upon your business requirements:
Pros and Cons of TDE
Pros:
- Speeds up the workbook through optimization — Because extracts are embedded within the workbook, they’re faster when you have complex visualizations with large data sets, filters, calculations. Also, where Custom SQL slows down performance, TDE can speed up the performance.
- Offline — Your data source can be utilized when you’re offline and unable to connect to the data source. Eg: When you’re on a plane or in an area having a poor network connection, you can simply create an extract of your data and work on it if you don’t have a connection to your live data source.
- Privacy — By hiding certain fields within your data source and then creating an extract, you can hide the fields that aren’t used, thereby ensuring data can only be viewed by the people intended.
- Portability — A TDE can be bundled with Tableau visualizations in a packaged workbook for easy sharing and collaboration.
- Reduce Load — Replacing a live connection to an OLTP database, or any database with a TDE reduces the load on the database that can result from heavy Tableau user traffic.
- Filters — As you extract data into a Tableau Data Extract, you can begin the process of preparing your data for analysis. In the Extract options under Data > Extract, you’ll be prompted to add Filters to your data extract. While the data source may be very large, you may not need the entire dataset to complete the analysis required. Filters can help pare down a very large data source into only the essential records, thus creating a streamlined data sub-set. Smaller data extracts require less computing power.
Cons:
- Snapshot: Because data is extracted from the data source, the data will remain the same unless it is refreshed.
- Size/Structure: Extracts can become slow to refresh and query depending on the data structure. E.g: many columns and structures
Pros and Cons of Live Connection
Pros:
- Real-time updates — As your viz is directly connected to the underlying data, this ensures data freshness.
- When you need up-to-the-minute data — If things are changing so fast that you need to see them in real-time, you need a live connection to your data. All your operational dashboards can be hooked up directly to live data so you know when your plant is facing overutilization or when you’re experiencing peak demand.
Cons:
- Databases are not always optimized for fast performance (unlike extracts) — As data queries go through the database, they can only as fast as the database itself. Accordingly, working with a live connection may be slow.
- Other factors can affect speed — e.g. Poor network speed and network traffic can slow down your workbook.
- Stress — Live connections, especially within complex workbooks, can stress some traditional databases.
Below I have summarized some of the points that would help you to compare both :
So, now the question is :
Which of the two is best and which one to choose?
And, the answer is BOTH!
Even better is when you don’t have to choose between in-memory and a live connection. Instead of looking for a solution that supports one or the other, look for one that supports choice. You should be able to switch back and forth between in-memory and live connections as needed.
- You want to use a sample of a massive data set to find trends and build your analysis. You bring a 5% sample of the data in-memory, explore it, and create a set of views you want to share. Then you switch to a live connection so your reports are working directly against all the data. Publish your views, and now your colleagues can interact with your analysis and drill down to the part of the data most relevant to their work.
- You’re flying to New York and want to do some analysis on the plane. You bring your entire data set, several million rows, into your local PC memory, and work with it offline. When you get to New York, you reconnect to the live data again. You’ve done your analysis offline and in-memory, but you are able to switch back to a live connection with a few clicks.
Because Tableau can now do analytics so swiftly and gives people the choice to connect directly to fast databases or use Tableau’s in-memory data engine, it has become much more powerful in respect of data exploration and data discovery. This leads to analytical insights that would most likely have been missed before.— ROBIN BLOOR, Ph.D., FOUNDER OF THE BLOOR GROUP
Conclusion
With that I’ll say that it will majorly depend upon your business requirements for which kind of connection you’ll require after you develop your dashboard. With Tableau, you receive more options for deployment, data connections, and collaboration. Hence, you can always switch between your data connections to work according to the conditions prevailing around you which makes Tableau, the market leader in modern BI platforms, offering the greatest analytical breadth, depth, and flexibility.
I usually procrastinate a lot, but I promise I’ll post more stuff — tips, and tricks about Tableau in my upcoming Medium blogs. Till then keep reading! 😄
If you have any questions, you can ping me on my LinkedIn: https://www.linkedin.com/in/sonal-gupta-85169989/
I’ll be very much happy to answer any questions :)