For Tableau users, sometimes, it is a good idea, to know what is really happening behind. Most of time, some LOD expressions seems complicated.
I search internet and could not find a good explanation. I found “Tableau Log Viewer” and prepared this post.
Dataset I used is artificial. It is explained in detail in former post.
Link
I will use this set, show you the Tableu SQL, and I also executed the same query at Pandas SQL. An overview of data.
I created a Fixed LOD at state level.
FIX STATE ORDERS:
{FIXED [State] : COUNTD( [Order ID] ) }
- State Only
Here we see at data, There is only state at view. Fixed LOD level is also LOD. So we see below numbers.
Associated SQL is at below.
If I just filter by Country,
When I put filter, it changed the query, after the inner join, it is getting, count by State. Filter changes the logic of Tableau execution.(First it must filter the values that will eventually stay in view.)
Also check the join condition. “IS NOT DISTINCT FROM”. It is TSQL working as below.
2) Country State
Now we have Country and State in view.
At SQL we can see from “t0” State and Country selected, because they are in view. And join condition is same as before. So we can deduce, after inner join, it is doing an independent query.(Fixed, not related with items in view)
I loaded the csv into Pandas DataFrame and used SQL.Below you can see with SQL, I can simulate the query and get same result.
3) Country State City
Now we have 3 items at view.
We can see that as expected Tableau makes a Country, State,City query and combines it with independent State query.
Again SQL on Pandas give me same result.
4) Country City
Now State is not at view, how will Tableau, bring it into picture.
“t0" is as we expect, with “t1” and “t2", Tableau joins a data based on Country and City, with an out of view item State. We can think of this as 1 more indirection to the logic.(Combine in an other sub query)
Now lets clear the Country filter.
Now query get simpler. Infact it get combination of (Country, State,City) and joined with State. And did not include state in final result selection.
With SQL we get same result.
5) Country
Here we can see the same pattern like “Country, State, City”. Tableau is making a big join, adding State, and calculating values for only State and joining these. Since State is at a lower layer, it is being aggregated with “SUM”.
Executing SQL on Pandas give same result.
We can see same thing, if I add my artificial column Planet.
6) Country State City Category
We see the same logic here, Joining a big Cartesian of view, with FIXED LOD. And the numeric value we see for the breakdown is value for State, ignoring everything.
In this post, I showed SQL behind Tableau that I cannot find at internet.(Maybe did not search good enough). Also knowing the detail for these simple cases, is OK, for more complicated cases, it is better not to think the SQL behind. This post could be a simple cheat sheet.
