Tableau Performance Tip: Query Fusion

Performance is key to the success of your dashboard. Where awesome visualizations increase the functional value of your dashboard, the user experience, very often, is criticized on its performance. Shortly, a bad performing dashboard is a bad dashboard.

Of course Tableau has anticipated on this by providing us tools and methods to avoid query latency such as tableau extracts. But what to do when tableau extracts are not an option?

What is Query Fusion?

Since version 9.0, Tableau has a new technology for database connections called Query Fusion. This technology will look at all of your dashboard queries and tries to eliminate any redundant queries. So where Tableau 8.x and earlier would run a query for each of your dashboard worksheets, Tableau 9.x will handle the queries in a more intelligent manner and eliminate similar queries.

How do I test it?

By using the Tableau Performance Recording you get a great overview of what’s going on in your dashboard and what exact queries are sent to its data sources.

On how to use the Tableau Performance Recording, I gladly refer to my other blog.

How do I enforce Query Fusion?

So now we know what Query Fusion is, we can easily tweak our dashboards to enforce this technology and reduce the number of queries run.

To align the level of detail of different worksheets I can trigger the Query Fusion technology and reduce the number of queries that need to run.

The dashboard:

For this easy dashboard I used a SQL Server data source and it contains 2 worksheets and 1 filter.

QueryFusion - 001 - NoFusionDashboard

Situation A: no Query fusion

If we have a look at the Performance recording to see what queries were executed, this is the result:QueryFusion - 002 - NoFusion PerfRec

As we can see, 2 queries are sent to the SQL data source, one for each of the worksheets.

Both of them are executed and returned their data.

Situation B: Query Fusion applied

The dashboard result looks the same, but let’s have a look at the Performance recording:

QueryFusion - 005 - Fusion PerfRec

Now we see that only 1 query is executed.

Where’s the difference?

No Query Fusion is applied.

Field

Sheet1

Sheet2

EnglishPromotionName

X

X

YEAR(Order Date)

X

X

EnglishCategoryName

X

X

EnglishSubCategoryName

X

Sum(SalesAmount)

X

Sum(OrderQuantity)

X

With Query fusion is applied

Field

Sheet1

Sheet2

EnglishPromotionName

X

X

YEAR(Order Date)

X

X

EnglishCategoryName

X

X

EnglishSubCategoryName

X

X

Sum(SalesAmount)

X

Sum(OrderQuantity)

X

Including the EnglishSubCategoryName into the Sheet1 aligns both queries on the same level of detail on both worksheets.

Now the Tableau engine recognizes that both queries are on the same level of detail, and therefore applies the “Query Fusion” and sends only 1 query to the SQL Server data source. Which reduces the traffic on my network and database, and so reduces query time in my dashboard.

Win, win, win…

Advertisements

Tableau Performance Recording

Getting your dashboards up to speed can be quiet a difficult if you don’t know where the latency is situated.

By Performance Recording, Tableau offers you a way, both on server and desktop, to get into the details of your dashboard refresh and see what going, where it happens and how long it takes.

A must have for performance tuning your workbooks. All you have to do is start the Tableau Performance Recording, make your workbook action and stop the Performance Recording. A few seconds later, Tableau opens a new workbook with the Performance recording results in it.

Performance Recording is available since version 8.0.

For Tableau Desktop:

Menu bar > Help > Settings and Performance >> Start Performance Recording

PerfRec - Desktop 001 - Start

Make some dashboard operations, or refresh your data source(s).

Next Stop the Performance Recording

Menu bar > Help > Settings and Performance >> Stop Performance Recording

PerfRec - Desktop 002 - Stop

Hold on for a few moments… and the Performance Recording Result workbook pops up.

PerfRec - Desktop 003 - Result

For Tableau Server:

How to enable it:

Settings > Workbook Performance Metrics > Record workbook Performance MetricsPerfRec - Server 001 - Settings

PerfRec - Server 002 - Enable

Getting it started

When opening a workbook or sheet, tableau server calls a URL and appends a session ID to it.

http://tableau.biztory.be/#/views/JumpPlot/HowTo-StepByStep?:iid=1

Add following code just before the session ID

:record_performance=yes&

The URL now looks like this:

http://tableau.biztory.be/#/views/JumpPlot/HowTo-StepByStep?:record_performance=yes&:iid=1

The Performance Recording is enabled which you can see by the “Show Performance Recording in the view toolbar

PerfRec - Server 003 - Show Performance Recording

Click it and the Performance dashboard opens in a new browser screen.

What’s in it?

The Performance Recording dashboard is an interactive dashboard that contains following information.

  1. Slider
    1. Choose what event duration you want to monitor
  2. Timeline
    1. A gantt chart with the events and their duration by Workbook, Dashboard, Worksheet and Event
  3. Events Sorted By Time
    1. My favorite
    2. See what takes the most time in the refresh or rendering of you dashboard.
    3. The events on top are probably the ones you should be focusing on.
  4. Query
    1. If you select a query event, the details of the query are show in the sheet at the bottom.
    2. Very useful when debugging slow queries.

PerfRec - Desktop 004 - Result Query

So with a few clicks you can easily see what’s going on when refreshing your worksheets, filters or data source(s).

This certainly comes at hand when tuning the performance of your dashboard. Some tips and post coming up on that real soon. Stay tuned.