You should consider using the single-table or multi-table connection interface instead of Custom SQL. It's especially important to avoid prematurely grouping and aggregating your data in Custom SQL, since that can lead to mis-computed aggregations in Tableau and can easily cause performance problems. Remove any existing GROUP BY and ORDER BY clauses from your Custom SQL. Furthermore, remove any WHERE clauses and replace them with normal Filters in a Tableau worksheet, or define them as Data Source Filters. If you continue to have bad performance (since many databases have trouble with the subqueries that Custom SQL requires), then consider making a Data Engine extract.
Robert Morton wrote:
... Furthermore, remove any WHERE clauses and replace them with normal Filters in a Tableau worksheet, or define them as Data Source Filters...
Robert, so you're saying that using a WHERE clause isn't a good idea to filter the data down? I would think without them there would be a lot more data coming in if relying upon [sheet] Filters. Why would Data Source Filters be the better solution?
I'm creating a reference document for my end users and your knowledge is good to have. The one spot I'm now fuzzy on is the WHERE bit as I though that would be a good way to narrow down a data pull (and subsequently an extract).
Here's a clarification: If Custom SQL is absolutely necessary, then you should certainly try to reduce the amount of data that the Custom SQL connection works with by including any appropriate WHERE clauses.
However if you are trying to replace your Custom SQL connection with a simple single-table or multi-table connection, perhaps you may be concerned about omitting important filters that existed in the WHERE clause of your Custom SQL. In that case, you can use Data Source Filters to ensure that all queries that Tableau generates will include those filter criteria.
Last, since Data Source Filters are independent of the underlying connection, they are applied only after changing your connection from a live database connection to a Data Engine extract connection. If you wish to have filters applied at the time of creating a Data Engine extract, you must define Extract filters for this purpose. I believe that Tableau bootstraps this process for you by automatically suggesting that the Data Source filters be used as Extract filters as well, but this is optional.
I hope this helps,
Robert, thanks for the info. I've been following your posts on Custom SQL, Multiple Tables & Blends instead of Joins keenly, because I'm working with a client who is trying to work all this out. While I have several questions I'll limit it to one for now.
Background: When I first started working with them they presented me with a data connect with a multi-table join using 6 tables, and literally 75-100 fields. And they only needed 6-10 fields for their workbook. They do NOT have any control or edit permissions to the database (it's third-party vendor). They can enter data, report out data, but they can't create new tables, or edit existing tables. To improve performance I got them to move the connection to Custom SQL and edit it down to the 6-10 necessary fields. This of course greatly improved performance.
Question: Given these circumstances, is there any other way to do this, without using Custom SQL? Am I missing something obvious? When establishing the live connection to their server, is there anyway to keep that server from serving up the kitchen sink, and thus avoid using Custom SQL just to edit the connection.
Robert, thanks, that does help clarify it.
Shawn, I'm guessing you use Multiple Tables and thus serve them everything OR you use Custom SQL.
Is Custom SQL so bad, can it be good? I would say it depends on how it's constructed. But I defer all expertise to Robert
Toby, I get the either/or on multiple tables-Custom SQL. So my question really boiled down to:
- Is there a way to 'filter' (eliminate) fields that will get included in the multi-table join (without writing Custom SQL)
- Is there a way to add a field filter (eliminator) when making the initial server connection (before getting to the multi-table bit)
I'm a server newbie, so there's a good chance I've botched this whole thing up and don't understand how server connections are made.
Yeah Toby that's what I (we) thought to, but in their case it didn't seem to work that way. That was how we thought/hoped it would work. I'll double-check tomorrow but I'm pretty sure we tried that. If we did and it didn't work and it's suppose to work this way with a live connection, then this sounds like it's a case for support.
I'll let you know tomorrow. Thanks for the help with this.
Interesting...and very, very disappointing! Actually, not at all logical from my point of view.
I created two extracts of the same data source. One with everything and the other with almost all fields "unchecked". The resultant extracts were 99.9% the same. The 0.01% difference was a different control character or a line-feed in the output. So no fields were actually removed as I would have expected.
Learned something new. I think I'll shut up now and wait for someone more knowledgeable...
Toby, I greatly appreciate all your experimentation. Especially since I don't have access to server to do these kinds of test. I'm also glad to hear I've not completely misunderstood server connections, and that it doesn't seem I am over looking something completely obvious. Thanks for all your help on this. Really.
Thanks for all the answers.