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…

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.

Tableau: Visualise a single measure in a doughnut chart (with conditional coloring)

This post describes how to build a doughnut-chart with Tableau based on a calculated percentage (single measure). The first part of this post is showing you how to do this without conditional coloring, while the second part shows you a slightly different approach to enable some conditional coloring in the doughnut.

01. DataSetFor this example we’ll be using a simple data set having 3 products, each with its own sales and costs. With these figures we can easily calculate the products profit margin.

Now create your calculated field that looks like this:

ProfitMargin: (SUM([Sales])-SUM([Cost]))/SUM([Cost])

Usually when creating pie-charts or doughnut-charts you would use a category field but in this case we can’t use that because we don’t have any categories for our data. Therefore we need to create a second calculated field to fill in the rest of our doughnut chart

RestProfitMargin: 1-[ProfitMargin]

Creating a doughnut chart with no specified color-indicator.

In the standard doughnut you drag your “Measure Values” into your Text Mark and your “Measure Names” into your Detail Mark. Now remove all measures except for our ProfitMargin and RestProfitMargin. It should look like this now:

04. MeasureValues

Now select the Mark Type Pie.

05. Mark Type Pie

And now you have your Pie Chart:

06. Pie Chart

For turning this into a doughnut chart we can easily add a measure into our row-section. I’m using the “MIN(Number of Records)” or you could you use a constant value for this. Just make sure both row measures have the same value.

07. RowMembers

As you can see, Tableau has made this into a Multiple Mark Type visualization. For the second Mark remove the objects of the Color Marks, Angle Marks and Label Marks. Modify the Size Mark and make it slightly smaller. (Maybe you can set default color to white)

08. Cleanup Second Pie

Now you can right-click the second row measure and make them Dual Axis. Et voila, you’ve got your doughnut chart. Just clean up labels, hide headers and format your measures and you’re good to go.09. DoughnutNoColor

How to setup conditional coloring in single measure doughnut chart.

To do so we need to create some (new/additional) calculated fields:

PositiveProfitMargin: 
IF [ProfitMargin] >= 0 THEN [ProfitMargin] END

PostitiveRestProfitMargin: 
IF [ProfitMargin] >= 0 THEN 1-[ProfitMargin] END

NegativeProfitMargin: 
IF [ProfitMargin] < 0 THEN [ProfitMargin] END

NegativeRestProfitMargin: 
IF [ProfitMargin] < 0 THEN 1-[ProfitMargin]

The calculations will return nothing or NULL if criteria is not true.

Now do the same actions as a described in the first part using these 4 measures.

10. MultiMeasureValues

Because of the multiple measures we can now specify our color on the different measure. Edit colors of the [Measure Names]

11. MeasureNamesColor

If you add the ProductName on the columns you would get this result:

12. Productdoughnut

We can show the actual ProfitMargin in the inner circle of the doughnut chart and use the same conditional coloring. Simply add PositiveProfitMargin and NegativeProfitMargin to the Label Mark. Now edit the labels and add color to each of the labels.

13. LabelFormatting

The result:

14. TheResult