Posted by Eric

September 16, 2019

14:54

Leave Reply

LODs (Level of Detail Expressions) in Tableau

LOD expressions are an amazing, fantastic, and revolutionary enhancement in the Tableau world, but there are some things to keep in mind. An LOD expression is essentially an additional request for data within a database. Under the covers it’s a bit more complicated than this simple statement, but conceptually an LOD is a secondary request for data. So if I am retrieving data from a database, and I use one LOD expression, my data request will (most likely) now have a secondary data request embedded inside it. Tableau is smart enough to minimize the number of data requests it sends to the database, but at some point, LOD expressions will almost inevitably result in additional data requests within the database which means more processing time to assemble the data you asked for.

Take for example:

A basic view will result in this sort of query:

SELECT [Data] FROM [Database Table] WHERE [Condition]

With an LOD in the view the query becomes something like this:

SELECT [Data] FROM [Database Table] AND

( SELECT [Other Data] FROM [Other Database Table] WHERE [Other Condition] )

WHERE [Condition]

Notice that red sub-select statement in there? That’s the sort of thing that an LOD would create. It’s called a sub-query. One of those, like the above example, is not so bad. 25 of those sub-queries, and things start to get *very* messy and slow and cause all sorts of performance issues. And the more complicated the data source, or larger volumes of data, the impact of numerous LODs magnifies performance problems.

One potential thing to consider is… sometimes a single, complex LOD can perform better than multiple, simple LODs. Remember… “lots” of LODs can be bad… “few” LODs can be good. This isn’t a hard fast rule, but more of a consideration for fine tuning performance.

For example, you could do the following:

IF [Condition 1] THEN

{ FIXED LOD : [Aggregate] }

ELSEIF [Condition 2] THEN

{ ANOTHER FIXED LOD : [Aggregate] }

ELSEIF [Condition 3] THEN

{ ANOTHER FIXED LOD : [Aggregate] }

ELSEIF [Condition 4] THEN

{ ANOTHER FIXED LOD : [Aggregate] }

ELSEIF [Condition 5] THEN

{ ANOTHER FIXED LOD : [Aggregate] }

END

That will (in many cases) result in six queries within the database (one “main” select and five “sub-selects”) being constructed and joined together in the database to return results. It isn’t a “multiple of five” performance issue, but it *will* be slower. Note Tableau is sophisticated enough to reduce that “six queries” to the bare minimum required to give you what you’re asking for, but in extreme situations you could theoretically end up with six queries/sub-queries needing to be processed by the database.

Instead, if your LOD dimensions are consistent, you could do this:

{ FIXED :               IF [Condition 1] THEN

                                                [Aggregate]

                                ELSEIF [Condition 2] THEN

                                                [Other Aggregate]

                                ELSEIF [Condition 3] THEN

                                                [Other Aggregate]

                                ELSEIF [Condition 4] THEN

                                                [Other Aggregate]

                                ELSEIF [Condition 5] THEN

                                                [Other Aggregate]

                                END

}

Notice the above LOD contains the same logic as the previous one, but uses a single LOD statement (i.e., single sub-query). The latter will almost invariably outperform the former. Especially when the contents of the LOD include an IF/THEN statement like that, which will execute only one of the aggregates inside the LOD. Note that in the examples I’ve only shown “FIXED” LOD expressions, but whether the LOD is fixed, include, or exclude, the general premise is the same.

To get a hint of this… run a performance recording and look for “compile query” in the results. Under normal circumstances, compiling the query should be a small fraction of the total execution time. If it is a noticeable percentage of the total execution time you may have runaway LOD expressions or table calculations requiring Tableau to figure out how best to request data for you.

If you have questions about LOD’s or require any other assistance with Tableau, contact a Tableau specialist!

Leave a Reply

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

Explore Posts By Category

Archives

Archives

Want to know more?

Contact us