7 Replies Latest reply on Dec 20, 2016 1:06 PM by Mike Merritt

    How to edit connection (change Initial SQL) without reloading the data?

    filip.vasilev

      When I realize that something has to be changed in our massive Initial SQL code that queries more than a billion records it takes a full data reload in order to just edit the connection to change a few lines in the Initial SQL code.

       

      Can I simply change Initial SQL code without reloading the data? I want to reload later at our regular scheduled time.

       

      Thanks!

        • 1. Re: How to edit connection (change Initial SQL) without reloading the data?
          Joshua Milligan

          Filip,

           

          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!

           

          Regards,

          Joshua

          • 3. Re: How to edit connection (change Initial SQL) without reloading the data?
            filip.vasilev

            Thanks Joshua!

             

            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.

            • 4. Re: How to edit connection (change Initial SQL) without reloading the data?
              Joshua Milligan

              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?

              • 5. Re: How to edit connection (change Initial SQL) without reloading the data?
                filip.vasilev

                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

                • 6. Re: How to edit connection (change Initial SQL) without reloading the data?
                  Roberto Rösler

                  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.

                   

                  Regards,

                   

                  Roberto

                  • 7. Re: How to edit connection (change Initial SQL) without reloading the data?
                    Mike Merritt

                    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.