With my limited knowledge, this is how Tableau works. There are some cases when calculations are done locally, but most basic calculations are passed on to data source for processing.
A few ways to avoid overloading database would be:
- Use extract and schedule it to refresh during quiet (for database) time
- Use one workbook with a plain custom SQL (no calculations) to make an extract and then use another workbook (with calculations) to use that extract as a data source, so all calculations will be done in the extract, not database.
Wether this will work for you depends on your circumstances, of course.
Yep, I think the first port of call is to create an extract. It'll materialise some of those as part of its optimisation process as well. At the very least it should be pretty quick and will not pass the calculation on.
It's feature for the base datasource to be used for many of these aggregate calcs but like in this instance, having a little more option would be helpful.
There are many posts on our forums discussing why Tableau generates queries wrapped around Custom SQL, and you can find additional information about the types of queries Tableau generates from this recorded presentation for our 2011 Customer Conference:
The gist is that the wrapped SQL is necessary for proper functionality, and these computations are best performed close to the data, within the database. For databases which cannot perform well with nested queries, we generally recommend that you either consider creating a view to replace the Custom SQL (and connect Tableau to the view), or attempt to create the connection in Tableau as a single-table or multi-table connection with any necessary filters as global filters.
Last, you should be careful not to include any unnecessary ORDER BY statements in your Custom SQL. This may introduce premature and unnecessary sorting of the data within a subquery, which can be very costly.
I hope this helps,