I've got some options you might consider:
1. Move the complex SQL to a view. That way you can simply point Tableau to the view and then you can edit the view as needed without reloading until you are ready.
2. Limit the extract to the top X number of records (maybe 100,000 or so). You can build a proof of concept without downloading all 1.X billion until you are ready.
3. Keep a copy of the SQL statement as a text file. Make changes there and only copy/paste into Tableau when you are ready to regenerate the extract.
4. Edit the SQL in the XML of the workbook. If you open a .twb file in a text editor, you'll see that it is just an XML markup containing the metadata and view definitions (packaged workbooks -- .twbx files -- are really just zip files containing the .twb file, data sources, and possibly other external files). You should be able to edit the SQL here without having to reload until you are ready. Please note that this approach is not officially supported and I haven't tested it so disclaimers of "use at own risk, no liability, etc...".
Hope that helps or spawns some other thoughts!
And welcome to the forums Filip!
1. Move the complex SQL to a view...
My tableau extract already points to a view. However, in order for the view to run the massive SQL code saved in the connection as the Initial SQL has to run first and that is where the change often happens. (Initial SQL creates volatile tables that are later used by the view)
Now I have the extract on the server and wondering what is the most elegant way of updating just the Initial SQL part of the connection that extract is using.
Okay, I'm on the same page now!
I think #4 might still a possibility, though it's not a good one. What type of connection are you using? Teradata?
What is the initial SQL doing and what changes are typically made?
Yes, I connect to Teradata.
The fact table at the source has to many records and there are many reference tables in use to pick up all the required attributes. That's why many volatile tables are being created in the process of producing the final dataset for Tableau. If I tried to write the code in a single SQL statement it wouldn't work. It would be running out of spool.
Of course, we could have designed an ETL to do what my Initial SQL is doing, but there are some reasons we did not take that route. Mostly to avoid creating persistent datasets in the database.
I tried No 4 interesting suggestion. However, when I try to open it in Tableau after it says that XML is not well formatted anymore. I am really not sure why because the only special characters I see there are CR/LF
Hi Filip, Joshua,
I experience the same issue with Tableau 8.2 loading data from a large data mart. I'm using the new "JOIN "wizard to connect the fact table to some dimension tables. But what make the situation even worse is, that already to open the "Edit Data Source..." Dialog requires a full reload of the data. And by this I have nothing changed, which will require a second load to save the results in a data extract.
Because there is no custom SQL makes it more difficult to change the XML code (Option 4) or edit the statement out of Tableau (Option 3). I wonder whether there isn't an alternative to view the current connection settings without reloading the data.
I have a fast answer. When, from the worksheet, you double click on the connection as follows
you can then just go to the menu, click "Data", and then click "Initial SQL..."
Then you can see and edit the Initial SQL.
Now, if you want to edit the custom sql, you'll have to run that entire danged Initial SQL code. But! if this is just a copy of your file, you can delete the initial sql, hop into the data connection, and grab the custom sql!
I'm using Tableau 8.2.