3 of 3 people found this helpful
I think you hit the major issues. A few things that I have found very helpful for live connections to SQL Server:
1. Good data modeling can make all the difference. Nicely formed star schemas with no/minimal snow-flaking and proper partitioning / indexing can make huge gains in performance.
2. Allowing Tableau to see the metadata is key. Tableau will optimize its queries based on its understanding of the tables and joins. So make sure you define the joins in the Tableau data conneciton interface (the "Multiple Tables" option). If you set that up in Tableau instead of using custom SQL then Tableau will be able to execute queries that only touch exactly the data needed and at the right level of aggregation.
3. Along the same lines, don't point Tableau to a view that is simply a flattening of your star-schema. That forces the query to join all the tables every time. If you use views, have a view for each table so you can join them together in Tableau. Then Tableau will generate a query that joins only what is needed for your view.
4. Make sure Tableau has the right permissions for the database. It might seem like all it needs to do is read, but if you allow it to have the ability to create temp tables it will use those as needed (especially for context filters) and that can boost performance.
5. Make sure calculated fields are optimized (e.g. avoid string manipulation wherever possible, see if it makes sense to pre-calculate some things and store in the source).
1 of 1 people found this helpful
I'm not sure how helpful you might find this, but I blogged about a month ago on a similar issue. This post is related to improving performance inside of Tableau, ignoring the back-end. However, you have sparked me to create another post related to optimizing the back-end as well. Regardless, ENJOY!
Josh - Thanks for the great insights regarding data joins and temp permissions.
Brad - Nice post. Having very focused (data/scenario) dashboards without superfluous components seems to be great advice where performance is concerned. I suppose it gets fuzzier where true ad-hoc analysis is concerned. Would certainly love to use extracts which gets back to my size/performance question. Looking forward to your back-end optimization article!
I did RTFM (http://onlinehelp.tableausoftware.com/current/server/en-us/help.htm) which contains a performance section within the Administrators Guide. There are some examples for improving extract performance by isolating the data engine and background processes onto different servers. Certainly something to consider.
It's better to be prepared before starting your Proof Of Concept so i suggest you to download Tableau Performance Analyzer provided by certified partner InterWorks and be ready to use it.
- Troubleshoot Your Load Times
With data for each tenant potentially in the hundreds of millions of rows
You might consider as a general approach to move your temporary tables aka tempdb to very fast disks (SSD) or to use a smart cache software for example http://www.intel.com/content/www/us/en/software/intel-cache-acceleration-software.html
Tableau 8 (due for release this month) ships with built-in performance analysis. Very slick!