Hi, I am hoping you all can provide some insight on the best practices (or tradeoffs) re: organizing your data sources in Tableau in a way that maximizes performance.
(1) One option is to approximate a Star-like schema with a fact table linked to various dimension tables through relationships, all done in Tableau. This is how I have been doing it.
(2) Recently though, I've been using Tableau Prep to clean a lot of the data, and the lack of relationship option there, made me wonder whether it's just as good to join all those tables (fact and dimension) together in one table, for example, as part of a flow Tableau Prep, or in Tableau, itself, for that matter.
I am curious how you all approach it, and how you think through any trade-offs.
Thanks,
Andy
Thanks,
Andy
Hi,
I've got several thoughts on this, and I'm sure others could chime in with more.
1) First of all I want to caution you about maximizing performance over all the other factors. You didn't say you were trying to do this, I admit, and it's something I think about a lot. The factor that stands out the most for me is the data pipeline complexity & therefore the level of skills & effort to maintain & extend the pipeline. More than once I've created a best-for-performance data source & views that are so complex that I have a hard time handing them off to other less-skilled team members (or even going back to them myself at a later date) so I'll end up going for simpler views, calculations, etc. that aren't as performant but can be more easily documented, are less dependent on edge case behaviors and less well-known techniques, etc.
2) Another factor is time to refresh/rebuild the data source. For example we've had a few Tableau data sources where (using Desktop/Server) switching from joins to using multiple table extracts saved 45+ minutes per refresh (and made some refreshes possible on lower-memory machines). A little rant: I'm honestly a big frustrated that we even have to think about doing all the joins in Prep or not and disappointed in Tableau (the company). Multiple table extracts have been available in Desktop/Server/Cloud since 2018.3 and we're now just about 4 years later from that and Prep still doesn't support output & update of multiple-table extracts.
3) I'm not 100% sure what your definition of data source performance is, I tend to think of data source performance as the "how fast (and how much memory & CPU is required) for Tableau to return a query from the data source?". Then for me the next three major areas are the computation that happens inside Tableau, the time required to render the view, and the network latency & bandwidth of the user. Those all add up to the total time it takes for a viz refresh. So we could have a really fast data source but that ends up putting too much run-time computational load on Tableau and the viz refresh is too slow. Or the way the viz is designed it's issuing a ton of really large queries to the data source at once that Tableau is not able to batch together and the viz refresh is too slow.
Therefore I can't say with absolute confidence what data structure would even work in Tableau until I've got some idea of what kinds of analysis is desired.
4) Another major factor with Tableau views is that how we build our views, what views are possible, and what kinds of interactivity are feasible all depend to some degree on our data structure. A really simple example is that if I've got a data value for each category & year and the years as columns (i.e. a column for each year's values) then I can build line, bar, etc. charts using Measure Names/Values but if I have a data value for each category & year and years as rows in the data then I can use Year as a dimension filter and get Only Relevant Values as an option, easily use the Year in calculated fields, etc.
Therefore the fastest data structure is only fastest for some set of use cases, and won't be as useful (or even outright unhelpful) for others.
5) You'd mentioned two ways to build the data source, there are a lot more than that between physical joins in a single table, having different Tableau data sources for different use cases, using Tableau data blends, etc. Plus within Tableau data sources we can have aggregated extracts (or do aggregation in Prep), using Hyper or live connections, all the ways that live connections to databases can be optimized, etc. This is a bit where there's an art in choosing which options to get the fast data source that we need.
Jonathan
