5 Replies Latest reply on Oct 11, 2018 11:37 AM by Dan Cory

    How to auto generate/update extract from excel then update data source on server in using Python?

    Kevin Bui

      Hello, I have a pre-existing reports published on tableau server that I need to update daily.

       

      This is currently how I am updating the reports

      1. I am currently pulling a report from a MS SQL query

      2. I put the query information onto an excel file

      3. I then create a new extract file from this

      4. I replace the data source of the Tableau file in Tableau Desktop

      5. Then I republish the workbook with the new data overwriting the old one.

       

      I've checked out how to create a .hyper file using the EXTRACT API. I understand how to create schema for the .hyper file.

      - What I don't understand is...

           - How to add information to it. So far it only specifies how to name the column and add the data type.

           - And how to replace the/update the data source on Tableau server.

       

      How do I keep my reports/visualizations updated with the latest information programatically utilizing Python without using a live connection using extracts?

      Preferably, I'd rather create the extract file directly from the MS SQL query data and update the data source of the visualization on Tableau Server if possible.

      __________________________________________________________________

      Solutions/Answers:

      A. Should be possible to write SQL query and throw it into an extract immediately. You're going to have to write a general algorithm to handle it though since Tableau does not hand theirs out.

      Building Tableau Data Extract files with Python in Tableau 8 – Sample Usage | Ryan Robitaille.

       

      B.  You can host an external file from your computer if you have Tableau Server connect to your computer. However, you need to have your server admin give Tableau Server permissions to access your computer. It works for excel files. It should work for other sources of data too.

      You'll need to edit the connection before you upload the data source and ADD a UNC path. For, example if your FULL computer name is "someguy1.company" and your file is on your desktop. The path will look something like

      \\someguy1.company\c$\users\username\desktop\data.filetype. You will also need to uncheck "include External Files" when you publish other wise it will upload a static copy of your data to Tableau Server.

      https://kb.tableau.com/articles/howto/automatically-updating-data-in-server-workbook-that-uses-live-connection-to-excel

       

      C. Putting it all together

      1. You can execute a query from code

      1A. (optional) if you need to do anything outside of SQL's abilities with code, you do it here

      2. Use the Tableau Extract API to create the Extract(.hyper file)

      3. Create a visualization utilizing this data

      4. Create a live connection to the extract file(publish data source)

      5. Have a script triggering 1 and 2 and create a new file when you want with the exact same name that you published the data source overwriting the old one. It might be possible to update the .hyper file. I'm not sure as I have no clue how to read the file as of right now.

       

      Pros -

      Lots of control

      Super fast if your database is slow

       

      Cons -

      Much more complicated

      Don't turn off your computer. Shouldn't matter too badly assuming Tableau Server caches the data.