3 Replies Latest reply on Apr 8, 2016 5:22 AM by Kosta Zivic

    How to create Tableau Data Extract from Tableau Data Source and SQL query using Tableau SDK

    Kosta Zivic
    Kosta Zivic Mar 17, 2016 9:45 AM

      Hi everyone!

       

      This might be a duplicate question, but I can't find it explained anywhere.

      If that's the case, please, point me in the right direction.

       

      I need to find a way to create a TDE using an ETL job.

      I wish to simply refresh the extracts on Tableau Server, but due to large extracts, I'd prefer to relocate the process of creating an extract in order to improve performance.

      Scheduled tasks are doing the job for me now, but I wish to give Tableau Server a rest and form the extracts elsewhere.

       

      What I would like to do is this:

      • Run a SQL query somewhere in the ETL to create a dataset
      • Get info about metadata from TDS (most importantly about calculated fields - I wish to be able to populate those in the ETL job which packs them into a TDE later)
      • Calculate the calculated fields with the metadata from TDS
      • Pack it all up into a TDE
      • Republish TDE to Tableau Server

       

      Basically, is there a way to create those calculated fields dynamically from the TDS?

       

      Thanks in advance and all the best!

        • 1. Re: How to create Tableau Data Extract from Tableau Data Source and SQL query using Tableau SDK
          Stephen Hicks
          Stephen Hicks Apr 7, 2016 3:38 AM (in response to Kosta Zivic)

          Hi Kosta

          Any luck yet?

          I'm using SSIS to ALTER VIEW and tabonlinsyncclient.exe to incrementally refresh and append it to tableau online.  I've been able to append hundreds of millions of rows this way.

          But sometimes there are errors, so I'm looking for alternative methods of automating TDE updates.

           

          1) I've got a big tde on tableau online

          2) I can manually create new tde's on my c: drive from my tds (connected to my sqlsvr view)

          3) Then I can easily use tabcmd to append them to my big tableau onlne extract.

           

          (Note: my source data is not a csv, and my tds contains lots of curation I've performed on it, so my tde updates must come from my tds structure.)

           

          The question is for step 2:  how do I use my tds to generate the local c: drive tde without manually doing so with the tableau desktop UI ?  In other words, how do I automate the generation or full-refresh of a local tde file on my c: or unc path ?

          • 2. Re: How to create Tableau Data Extract from Tableau Data Source and SQL query using Tableau SDK
            Kosta Zivic
            Kosta Zivic Apr 7, 2016 6:43 AM (in response to Stephen Hicks)

            Hi Stephen,

             

            I'd love to say yes, but that would be too much - I'm still midway in the

            process.

            What worries me the most are the calculated fields, bins and sets. I'm not

            yet sure whether I can recreate those from *.tde, and most importantly -

            how to automate the process of creation.

            The rest of the process is not a problem - I'm using Kettle (Pentaho data

            integration) with pre-enabled Tableau Data Extract Output feature (check

            that out, it's awesome), I'm just worried about creating the right set of

            columns earlier in the transformation and whether the sets and bins will

            still be there.

            Another issue I have is that simple appending won't do the job alone. It is

            an often situation that some of the records that were previously in the

            data source shouldn't be there after the refresh. Sometimes I add

            additional columns to my TDE and want them to be populated for all the rows

            that were already in the TDE.

            I'm just looking for a way to make sure that sets, bins and calc fields

            will still be there, unchanged, after an extract is made. Basically, I need

            a way to dynamically read that metadata info and recreate the TDE locally

            using TableauExtractAPI. If anyone finds a way to do that, it would solve

            many problems! (heads up for devs, make that happen )

             

            I believe that it is already possible, I just can't find any useful info in

            TableauExtractAPI documentation. Hope somebody will answer our questions

            soon.

             

            Best,

            Kosta

             

            On Thu, Apr 7, 2016 at 12:39 PM, Stephen Hicks <tableaucommunity@tableau.com

            • 3. Re: How to create Tableau Data Extract from Tableau Data Source and SQL query using Tableau SDK
              Kosta Zivic
              Kosta Zivic Apr 8, 2016 5:22 AM (in response to Kosta Zivic)

              Stephen Hicks,so this is what I've came up with:

              Dijagram.png

               

              Since my extracts are generated from DB Views, my idea is to create some kind of "templates" of TDEs with zero rows but correct metadata. Then store TDE and TDS to a *NIX server.

              Extract SQL query from TDS and use it to pull data set from DW (same view, just not limited). After that append the rows to the template TDE and push full TDEs to Tableau Server.

              Please reconsider this flow, I'm open for any flaws you may find in it.

              Best,

               

              Kosta