Tableau Data Engine power
What follows is a very context-specific scenario where the user needed a live connection without any delay. They needed speed and power. Tableau, I said, could certainly answer that question. Here’s what I learned…
When this works:
- You need a live and fast connection (think ‘speed of thought’ here)
- You have custom SQL which has multiple table joins
- You don’t want to have to query the database every time the user changes the view (these seconds add up)
When this won’t work:
- Large (>200k) record datasets
- When you don’t need a live and fast connection
- When you have a columnar technology (Vertica is a great example)
Essentially, this method is very context specific. Would I use it all the time? Nope. Would I use it when the situation was right? Absolutely.
Let’s keep in mind, though, that the Tableau Data Engine is a phenomenal tool to leverage. And I’m always of the opinion that what the customer/client/user can’t see in our visualizations / dashboards influences their experience in a positive (or negative) way. Imagine if it took over a minute for Google to load a news story or stock quote; we’d get pretty frustrated with everything we did after that point. Quickness and flexibility (low latency, really) in analytic dashboards is just as much a need as a great chart or cross tab.
Okay, so here’s what I did.
Step 1:
Add a column in your sql that you can use as a Context Filter. Ideally, this will be your Slowly Changing Dimension (SCD). You don’t want this dimension to change or have the user filter on it. Otherwise there are performance issues when it’s recomputed.
Step 2:
Connect to your data in the way we’re all accustomed. In our ‘Custom SQL’ case, you just paste (or write) your code in the appropriate window.
Step3:
Make your view in the desired way. This part’s really up to what the user needs. It could be a chart, cross tab, etc. You get the point. Just make it well and don’t make it fragile. And make this first!
Step 4:
Remember that extra column you added? Good. Now, drag it on the filter shelf. When the option comes up, make sure you check the ‘Exclude’ box and then select ‘None’. This will ‘materialize’ the joins. Essentially, it calculates them ahead of time so Tableau won’t have to re-query your database each time the user changes a view in the dashboard.
Step 5
Select that column and use the drop down. You’ll see the ‘Add to Context’ option. Do that. You’ll see the usually query dialog and then you’ll see an ‘Importing Data’ dialog display! Importing data with no extract?! That’s the power of Tableau. If you check the log files for the work you did, you’ll see that Tableau is smart enough to make a temp table of that data. Fast and free J
After you do the above, it should look like this:
Once that’s done, start changing the views. You’ll notice that there is no delay in the ‘executing query’ portion. NOTE: there is that small, up-front cost to generate that temp table. After, however, you won’t experience the 3 or 4 second queries. Hard work up front = more fun down the line.
Enjoy !
-Mike
