Another Look at Site Catchment Analysis with Tableau 6 (Part 3)

Window function speed

But having said that, the next performance issue is that the WINDOW_XXX() functions do not currently scale well. I first noticed this during the beta, and worked out that the time to execute the window functions increases proportional to the square of the number of rows in the partition being analyzed. This makes these functions effectively useless when analyzing large volumes of data, so I had largely avoided using them since I came across the issue, thinking I would wait until the issue is fixed.

But I have been a little surprised how long it has taken for this issue to float to the top of the priority list, so when I found that I really needed them to get the analysis going properly for Robert’s workbook I decided to have another look at the issue.

I work in system performance engineering (which basically means ideally avoiding but more often finding and fixing system performance issues). Having the response time increase with the square of the amount of work to do is a very familiar issue. In fact this example of “square-law performance” and the process of working out what was happening and finding a workaround made an ideal little case-study for the Performance Engineering section of my company blog: Square-Law Performance. Sorry Tableau folk – but it’s a great example and it’s only one little blemish on a great release.

The WINDOW_XXX() functions perform various aggregates over a “window” onto the rows in each partition of the data being analyzed. The window may be the whole partition or it may be a different subset of the rows for each row in the partition – for example a sliding window of rows either side of the current row. In defining the partitions, one of the properties which can be specified is the sort order, so the partition needs to be sorted to evaluate the function. It only needs to be sorted once per partition, because the sort order is invariant, even if the window moves as each row is evaluated – but currently the window is re-sorted for each row, which is the source of the slow-down.

For small partitions, or if the window is defined as a narrow range around the row being processed, this extra sorting is utterly negligible. But in a lot of cases, such as the uses in this analysis, the required window is the whole partition. For the first version of this analysis, a few calls to WINDOW_SUM() over the full 8,000 rows of location data resulted in the sheet taking three minutes to refresh. For large partitions this can make the functions unusable. The analysis I was doing when I first discovered this involved a partition of 120,000 rows, which took three and a half hours to evaluate (though I only worked that out after initially assuming that Tableau was in a loop and killing it a few times).

Luckily, for cases where the required window is the whole partition, there are a couple of workarounds to avoid the problem. I say “a couple” because there are two common cases.

Result only needed for one row in partition

A very common case, which applies in some of the cases in this workbook, is that all that is required is to evaluate an aggregate once for the whole partition. The field [Population within Radius (blended)] on sheet “Covered (blended)” is an example of this sort. All I need is to evaluate the total population within the selected area, and I only need it once, to display in the sheet header.

The basic window calculation that I need is this:

WINDOW_SUM(SUM([Population]))

But that will be evaluated 8,000 times, including 8,000 expensive sorts of the whole partition.

At first glance, it appears that all that is needed to fix it is this:

IF (FIRST()==0) THEN
WINDOW_SUM(SUM([Population]))
END

That just returns the result for the first row in the partition and NULL for all other rows, but unfortunately it turns out to take almost as long as the original. It took me quite a while to guess my way past this one, but eventually I worked out what must be happening. Tableau actually evaluates the expression in a couple of passes. First it gets the window ready. In this case, because I haven’t specified the optional parameters defining the range of the window, it defaults to the whole partition – which it duly sorts. Then it gets on with actually evaluating the expression, which means that for the first row it calculates the result and for all other rows it just ignores the nicely sorted rows.

Having guessed that that might be what it was doing, I tried another tack. For the first row, where we want the result, specify the entire partition. For all other rows, make the window just the single row being processed, like this:

IF (FIRST()==0) THEN
WINDOW_SUM(SUM([Population]), 0, IIF(FIRST()==0, LAST(), 0))
END

Sure enough, that fixed it. My three and a half hour case came down to about five seconds, and the calculations in the “Covered (blended)” sheet are now too fast to worry about.

Result needed for all rows in partition

The other common case, which applies elsewhere in this workbook, is that the calculation is needed over the whole partition for all rows in the partition and the answer will be the same in each case.

In this case, rather than evaluating the result for every row, we can just evaluate the result for the first row in the partition and copy it for every other row, whilst using the same trick to minimize the cost of the sorts on the other rows which are not going to be used. That can be achieved like this:

PREVIOUS_VALUE(WINDOW_SUM([Covered_Population],
0, IIF(FIRST()==0, LAST(), 0)))

I have raised both the TOTAL() and the WINDOW_XXX() issues with Tableau and expect that both are likely to be resolved in the near future. In the meantime, the above tricks provide adequate workarounds for all the cases I’ve encountered so far.

Add a Comment

Your email address will not be published. Required fields are marked *