8 Replies Latest reply on Feb 23, 2016 8:22 AM by Jan Sprink

    Automatically refresh extracts and consolidate them via script

    Jan Sprink

      Hello,

      is it possible to automatically add data from a data source to an extract as described in http://onlinehelp.tableau.com/current/pro/online/mac/en-us/help.htm#extracting_addfromfile.html using tabcmd or other scripting tools?

      The objective is to refresh about 30 oracle data source extracts at night and amalgamate them to one after that.

      The fields in every extract are the same. To differ the source, there is a field named "DBNAME", which contains the name of the source database.

      I hope you can help me.

      Best regards,

      Jan

        • 1. Re: Automatically refresh extracts and consolidate them via script
          patrick.byrne.0

          Jan,

           

          I believe what you are trying to accomplish is refreshing the data extracts that are being used by Tableau Desktop on a regular schedule.

           

          This product help should be able to help resolve this issue: http://onlinehelp.tableau.com/current/pro/online/mac/en-us/help.htm#publish_workbooks_schedules.html?Highlight=schedulin…

          The above article talks about setting up extracts to refresh on a schedule.

           

          -Patrick

          • 2. Re: Automatically refresh extracts and consolidate them via script
            Jan Sprink

            Hi Patrick,

            thanks for your answer. Unfortunately this doesn't solve my problem.

            The desired outcome is to refresh about 30 extracts at night and consolidate them after refresh process. Objective is one extract with content of all 30 data sources.

            I think, the uploaded picture illustrates our idea.

            Meanwhile, I implemented a solution using the Tableau SDK (Java). Currently I am in testing process.

            In my development environment on Mac OS X (Version 10.11.3), the interface is running.

            On Windows environment (Windows Server 2012 R2 x64), I always get the following error:

             

            java.lang.UnsatisfiedLinkError: Unable to load library 'TableauExtract': Das angegebene Modul wurde nicht gefunden.

              at com.sun.jna.NativeLibrary.loadLibrary(NativeLibrary.java:194)

              at com.sun.jna.NativeLibrary.getInstance(NativeLibrary.java:283)

              at com.sun.jna.NativeLibrary.getInstance(NativeLibrary.java:244)

              at com.sun.jna.Native.register(Native.java:1065)

              at com.tableausoftware.extract.ExtractAPI.<clinit>(Unknown Source)

              at model.MakeOrder.main(MakeOrder.java:74)

             

            I installed the Tableau SDK under C:\Tableau\Framework and added "C:\Tableau\Framework\bin" to the PATH variable.

            In Eclipse, I added the external JARs out of C:\Tableau\Framework\Java to build path. Both Eclipse and Java JDK are 64Bit versions. (Java 1.8.0_73)

            I also tested with 32Bit, but without success.

            For example, the error occurs when I run the sample file "MakeOrder.java".

            Do you have any idea?

             

            Jan

            • 3. Re: Automatically refresh extracts and consolidate them via script
              Jonathan Velarde

              Good Day!

               

              Hi Jan,

              What I can suggest to you based on my understanding on your illustration, is to have a consolidated datasource. If your Database is from SQL server try to do a single view that consolidate all the fields you need on your report. Join those Tables and create a single view for you to much easy management of your Extracted data and refreshed it on a schedule basis.

               

              Thanks and God Bless

              Regards,

              Jonathan B. Velarde

              • 4. Re: Automatically refresh extracts and consolidate them via script
                Jan Sprink

                Hi Jonathan,

                the problem is that there are about 30 different databases. To that effect, I have to use database links and the whole load lies on one database that calls all select statements.

                That is, there could be a query like

                 

                select *

                from view@db1

                union

                select *

                from view@db2

                 

                and so on. This query will be executed sequential and will be take a long time.

                Therefore, the preparation of every particular extract should be concurrent.

                My current idea is to use the Tableau SDK to prepare the merged extract.

                But now, I have the abovementioned problem with UnsatisfiedLinkError.

                 

                Here again a short conclusion of my environment:

                 

                OS: Windows Server 2012 R2 x64

                IDE: Eclipse Java EE IDE for Web Developers. Version: Mars.1 Release (4.5.1) Build id: 20150924-1200

                Java: JDK Version 1.8.0_73 (x64)

                Tableau SDK: Tableau-SDK-C-Java-64Bit-9-2-4; Install-dir: C:\Tableau\Framework

                 

                I added "C:\Tableau\Framework\bin" to the PATH variable and followed the instructions under

                http://onlinehelp.tableau.com/current/api/sdk/en-us/help.htm#SDK/tableau_sdk_using_java_eclipse.htm

                and

                Using the Tableau SDK Java Samples .

                Unfortunately, I still get the UnsatisfiedLinkError.

                 

                Anybody have an idea?

                 

                Regards,

                Jan

                • 5. Re: Automatically refresh extracts and consolidate them via script
                  Jonathan Velarde

                  Good Day!

                   

                  Hi Jan..,

                  Have you tried to refresh your extracted datasource in tabcmd utility? If so, try to reconfigure it using the batchfile.

                  Please see link below:

                  Tableau Server: Schedule Published Extracts Using Tabcmd

                   

                  Here is my sample batchfile created for AutoBackup and Cleanup.

                  You can modify this based on the requirement you needed.

                   

                   

                  @echo OFF

                   

                  set TS_BIN_PATH="C:\Program Files\Tableau\Tableau Server\8.3\bin"

                  :: This command sets the path to the Tableau Server bin directory in order to use the tabadmin command

                   

                  set TS_BACKUP_PATH="D:\REBACKUP_TABLEAUSERVER"

                  :: This command sets the path to the backup folder - change this to match the location of your backup folder

                   

                  set TS_BACKUP_PATH_NOQ=D:\REBACKUP_TABLEAUSERVER

                  :: This command sets the path to the backup folder with "No Quotation Marks"...you will need this for the copy command below

                   

                  set SAVE_DATE_STAMP=%DATE:/=-%

                  :: This command creates a variable called SAVE_DATE_STAMP which grabs the system time and formats in to look like DD-MM-YYYY

                  :: This gets rid of the slashes in the system date which messes up the commands later when we're trying to append the date to the filename

                   

                  set ERROR_LEVEL = "1"

                   

                  echo %date% %time%: ***** Tableau Server Backup process started *****

                  :: Prints that text to the DOS prompt

                   

                  cd /d %TS_BIN_PATH%

                  :: changes directory to the above path and takes into account a drive change with the /d command

                   

                   

                  echo %date% %time%: Cleaning out old backup files...

                  forfiles -p %TS_BACKUP_PATH% -s -m *.tsbak /D -3 /C "cmd /c del @path"

                  :: Cleans out files in the specified directory that end in .tsbak extension and are older than 3 days

                  :: This ensures that only 3days of backups are saved in the directory. You can adjust the time as needed

                   

                   

                  echo %date% %time%: Backing up data...

                  tabadmin backup %TS_BACKUP_PATH%\TABLEAUSERVER_BACKUPDATA -d

                  :: Backs up the Tableau Server and creates a file \TABLEAUSERVER_BACKUPDATA.tsbak with the system date appended to the filename

                   

                   

                  echo %date% %time%: Checking to see if server is running

                  tabadmin status | findstr RUNNING

                  if "%errorlevel%"=="1" exit /b %errorlevel%

                  :: Checks to see if the Tableau server is running. If the %errorlevel% returns a 1 then server is not running.

                  :: Program is set to exit batch file and ODI will send out an email message to restart the server

                   

                  echo %date% %time%: *** Tableau Server Backup process completed ***

                  :: Prints that text to the DOS prompt to show that the jobs are all done

                   

                   

                   

                  Create a batchfile per datasource you want to extract.

                  Then once you created your batchfile. Run those batchfiles in Windows Scheduler.

                   

                   

                  Hope you could get some ideas on this.

                  Thanks and God Bless

                  Regards,

                  Jonathan B. Velarde

                  • 6. Re: Automatically refresh extracts and consolidate them via script
                    Jan Sprink

                    Hi Jonathan,

                    thanks for the fast answer and the detailed description of your batch file.

                    The problem with different datasources is the obligatory relation between them in Tableau Desktop.

                    I want to UNION all data of all sources. At that, every export has the same structure with a field named "DBNAME" to determine the source.

                    I think the tabcmd utility is good to refresh extracts in certain intervals, but there is no possibility to amalgamate these extracts after refresh.

                    Because of that, I tend to use the Tableau SDK to refresh my extract.

                    Does anybody can help me with the UnsatisfiedLinkError?

                    Thanks and regards,

                    Jan

                    • 7. Re: Automatically refresh extracts and consolidate them via script
                      Toby Erkson

                      Jan Sprink wrote:

                       

                      Hi Patrick,

                      thanks for your answer. Unfortunately this doesn't solve my problem.

                      The desired outcome is to refresh about 30 extracts at night and consolidate them after refresh process. Objective is one extract with content of all 30 data sources.

                      ...

                      Creating individual extracts and then merging/combining/UNIONing those extracts is not possible.

                       

                      Are you using Tableau Server or Tableau Online or only Tableau Desktop?

                      1 of 1 people found this helpful
                      • 8. Re: Automatically refresh extracts and consolidate them via script
                        Jan Sprink

                        Are you using Tableau Server or Tableau Online or only Tableau Desktop?

                        I am using the Tableau Desktop to develop workbooks and the Tableau Server to publish workbooks and data sources.