5 Replies Latest reply on Nov 14, 2013 7:34 PM by chris.schultz

    Is it possible to change the database for an extract on Tableau Sever without having to recreate the extract from scratch?

    chris.schultz

      Here is my challenge:

       

      • We have to create some very large extracts for Tableau Server off of Teradata.  I run out of spool space when creating them with my db user ID so the DBAs are creating an Application UID for Tableau Server.
      • I will have access to the Application UID and password in the DEV and UAT database environments but not production.
      • I will be able to create the extracts off of Teradata DEV or UAT databases but after I publish the extract to server I need to change the database from UAT to PROD and have the DBA change the password on Server so that my extracts are loaded and refreshed eaach month on Server while pointing to production.
      • We can update the UID and password and even database server on Tableau Server under data connections but there is no place to change the database name.
      • It seems like I'll have to recreate the extract in its entirety when I go to create the production extract.  The problem is that I don't have the Application ID and password for PROD and can't bug a DBA each time I need to do this.

       

      Wondering if there is a workaround or if I'm missing something.  Thanks!


      Chris

        • 1. Re: Is it possible to change the database for an extract on Tableau Sever without having to recreate the extract from scratch?
          Robert Morton

          Hi Chris,

           

          This is an interesting situation. First to answer your question, no there is not currently a way to change the database via the Server web interface. You may however be able to script this operation using tabcmd, but I'm not certain about this. It may be a good idea to consult with a member of our Professional Services team, which you can arrange via your sales rep.

           

          Another possibility is to use Query Banding, and have your Teradata DBAs build logic based on the query band to determine when an operation is being initiated by Tableau Server, such as a live analytical query or an extract refresh query. I don't know if the Teradata query banding mechanisms support dynamically changing the database, the spool size, or other criteria that could help with your situation.

           

          Last, I'm curious about why your refresh operations are running out of spool space. When Tableau creates or refreshes an extract it will attempt to consume result set records as quickly as possible, removing them from their temporary holding location in the result set spool. However sufficiently complex queries may require several intermediate operations in the Teradata query engine that use substantial spool space in a blocking manner. Perhaps you have several complex joins involving tables that don't have proper Primary/Foreign key constraints? Or maybe you have a Custom SQL connection with an expensive HAVING or ORDER BY clause that leads to a blocking operation in the query pipeline? (note that ORDER BY in a Custom SQL connection is typically useless, unless also paired with a TOP/LIMIT row-limit clause, which itself is a blocking operation.) Consider replacing complex Custom SQL with a database VIEW and changing your Tableau connection to a single-table connection to that VIEW. Consider adding join constraints as needed to improve spool space usage with complex joins. Consider replacing joins in Tableau with data blending using separate data sources for each dimension table. Finally, you may even be experiencing these problems with a simple connection to a very wide table, so consider creating the extract after hiding columns which are unused or unnecessary in order to reduce the number of columns Tableau includes in the query used to create the extract.

           

          I hope this helps,

          Robert

          1 of 1 people found this helpful
          • 2. Re: Is it possible to change the database for an extract on Tableau Sever without having to recreate the extract from scratch?
            chris.schultz

            Hi Robert.  The reason we're running out of spool space is our requirement to extract 4+ years of monthly transaction data.  This leads to several GB of data which is pretty huge by our standards. My extract is created using Custom SQL against our Teradata production box. The primary table is a wide table but I'm only pulling in a handful of columns from it.  I'm also performing some left joins to some dimension tables.  I am not doing any complex joins and I'm not including HAVING or ORDER BY so that's isn't the issue.  The database is pretty locked down so getting a view created would take some effort.  I guess my next step is to try this using the multiple table join operation in Tableau Desktop.  I'll take a look at your suggestions some more and will update this entry after i give them a try.  Thanks.

             

            Chris

            • 3. Re: Is it possible to change the database for an extract on Tableau Sever without having to recreate the extract from scratch?
              Robert Morton

              Hi Chris,

              Thanks for the update, and good luck on your investigation.

              -Robert

              • 4. Re: Is it possible to change the database for an extract on Tableau Sever without having to recreate the extract from scratch?
                Michel Roberge

                Hi Chris,

                 

                We do it - but it is tricky. If it's only one connection, then you can just parse/edit the TWB file. It's a standard Xml, and it contains your connection strings.

                 

                In our case, what we do is that. Edit the TWB, publish it with the embeded database password, and call up an extract refresh. The refresh will be done on the "new" connection information.

                 

                Unfortunately, this is not done from a frontend. But it works.

                 

                Michel

                • 5. Re: Is it possible to change the database for an extract on Tableau Sever without having to recreate the extract from scratch?
                  chris.schultz

                  I think that's the route we will have to take until Tableau offers more options for managing and editing data sources.  Thank you Michel!