1 2 Previous Next 18 Replies Latest reply on Oct 11, 2017 6:37 AM by misba.masood

    Live Connection Question

    Shawn Wallwork

      [Question at bottom.] I'm writing a client report that includes this:

       

      Tableau is a query based tool; not a live streaming tool. A Live Connection means that Tableau sends queries to your database, and retrieves data. Generally, these queries will return whatever data is currently in the database; with one important exception – any data currently in Tableau’s cache.

       

      So when a Desktop user changes a filter on a dashboard, this usually causes Tableau to generate a query. This query will go to either your MS SQL Database and retrieve up-to-date data, or it will go to Tableau’s cache and retrieve potentially out-of-date data. The fail-safe way to retrieve current data is to press the F5 key. This tells Tableau to dump the cache and update the view using data retrieved from the database. So far this has been about Desktop. In desktop there is no way to automate the F5 key refresh (at least that I know of).

       

      Server handles data updates/refreshes a bit differently. When you setup a connection on server you can configure how caching is handled:

       

      Server.png

       

      Here’s the Online Help on Data Connections. And here an article that talks about automatically refreshing extracts (toward the end). But remember this is NOT live streaming. The user has to take an action (filter change or refresh click) before new data will be retrieved, even in server.

       

      [Side note: I just finished a Google Hangout with Richard Leeke, and he showed me a Java script he wrote that will do streaming auto-refresh. So I know it can be done, but it is not out-of-the-box Tableau.]

       

      My Question: Am I understanding this correctly? Especially the bits in red. Thanks,

       

      --Shawn

        • 1. Re: Live Connection Question
          Richard Leeke

          That's a pretty fair summary, Shawn.

           

          BTW, the auto-refresh option I showed you is just some javascript which wakes up every so often (after a configurable delay) and calls a method to refresh the data in the view. Essentially the equivalent in Server of automating hitting F5 in Desktop. The only way to achieve the same in Desktop is to use an external tool or script to "send" an F5 keystroke to Tableau Desktop every so often. It's clunky, but easy to do. Various folks (including me) have posted about it on the forum over the years.

          3 of 3 people found this helpful
          • 2. Re: Live Connection Question
            Shawn Wallwork

            Thanks Richard for the feedback. Greatly appreciated.

             

            --Shawn

            • 3. Re: Live Connection Question
              Lionel Yu

              Hi Richard, sorry for hijacking that other thread, for some reason the reply button wasn't appearing for me earlier.

               

              Can you kindly show me the auto-refresh javascript option you mentioned above?  This is something I'm struggling with as I have 10+ worksheets that connect to my Tableau workbook, and it is a pain to reconnect all of them every time I need to run a new report (which is every day, lol!)

               

              Thanks for responding to me in that other thread

              Lionel

              • 4. Re: Live Connection Question
                Richard Leeke

                No problem about the other thread. The forums software can be a bit temperamental sometimes – or maybe you just weren’t logged on?

                 

                Anyway. Just to clarify, are you talking about Desktop or Server? You mentioned Desktop in your other post, but the JavaScript API only applies to Server.

                 

                If you are talking about Desktop, the only way I know of to automate that sort of thing is via the Windows “SENDKEY” function – which lets you send keystrokes to an application. There are numerous tools that let you do that. I’ve used AutoIt in the past. Search the forums on SENDKEYS and you should find various postings.

                 

                Using the JavaScript API with Tableau Server lets you automate a lot of functions. There were several sessions on that at the conference (including one that I did), so if you happen to have gone to the conference you could check out the videos. I also put a little demonstration up on our company blog which lets you download a sample I showed at the conference, demonstrating how to animate a Server view. You can check that out here<http://blog.equinox.co.nz/blog/Lists/Posts/Topic.aspx?Topic=Data%20Visualisation>.

                • 5. Re: Live Connection Question
                  Lionel Yu

                  Oh ok - disappointed to know that the API only works for server, as I have the desktop version.  It seems like so much functionality doesn't work for Desktop, especially regarding the data connections.  Thanks for your input - I'll take a look around for the SENDKEY function.  Thanks Richard!

                  • 6. Re: Live Connection Question
                    Shawn Wallwork

                    Desktop and Server are two very different product. You think Desktop is limited, try using Server to author anything! It'll drive you nuts.

                     

                    Desktop = Authoring

                    Server = Viewing (interacting)

                     

                    ...by design.

                     

                    --Shawn

                    • 7. Re: Live Connection Question
                      angie brannen

                      I am fairly new with Tableau and I'm using Oracle connection to update my data in Tableau workbook and was interested in using a custom SQL to append new data to my historical data in the workbook. Please forgive me, but I haven't read this whole thread through it's entirety. From what I have read, I don't think the F5 function will work with the data load process I am pursuing to perform for my workbook. Or am I misunderstanding something?

                       

                      Angie

                      • 8. Re: Live Connection Question
                        Shawn Wallwork

                        Angie, welcome to the forums! You would think this would be a simple question & answer, but it isn't. There are many, many different scenarios involved in refreshing data. So rather than list them all; what is:

                        ... with the data load process I am pursuing to perform for my workbook.

                         

                        Desktop or Server?

                        A published workbook?

                        Live connection or extract?

                        A packaged or unpackaged workbook?

                        A set refresh schedule or manually refreshing data and/or extract?

                         

                        Essentially what's the end result (what will the users look at, web or workbook), and what are the steps you are taking (or want to take) to get there?

                         

                        Cheers,

                         

                        --Shawn

                        • 9. Re: Live Connection Question
                          angie brannen

                          Hi Shawn, thank you for the quick response.

                           

                          Desktop or Server? Desktop

                          A published workbook? No

                          Live connection or extract? Currently Live

                          A packaged or unpackaged workbook? Packaged

                          A set refresh schedule or manually refreshing data and/or extract? prefer Schedule

                           

                          I tried a CREATE TABLE script for a new table to append future recurring data updates and got the error message when previewing results from the 'Edit Custom SQL' window;

                          • Oracle database error 903: ORA-00903: invalid table name
                          • Unable to connect to the server "okdssp1". Check that the server is running and that you have access privileges to the requested database.

                           

                          but yet I'm connected to this Oracle server and update my data.

                           

                          Bottom line I'm trying to be conservative with our system resources when updating the dashboard with new data; the current process involves executing new data via business intelligence software, then exporting the results to a text file, then appending the new data to an Access table or Excel file (another data source) then loading it into Tableau. I'm trying to go from business intelligence to Tableau via Oracle. Considering I'm somewhat an amateur with SQL & Tableau I hope this all makes sense.

                          • 10. Re: Live Connection Question
                            Shawn Wallwork

                            So when you save a workbook as a Packaged workbook (twbx) Tableau gathers everything up in a Zip file. (You can use any Zip utility to open a twbx file and look inside.) When packaging the workbook Tableau makes a copy of your Excel or Access file and puts the copy in the packaged workbook. So if you change the original data file these changes will NOT show up in the packaged workbook because that workbook is no longer 'looking' at the original file, but looking at the copy instead.

                             

                            Your best bet is to work with an unpackaged workbook with a Live connection to your Oracle DB or an Extract of your Oracle data. In both cases you can skip the Access/Excel step. Then when you need to send a packaged workbook to your client, you'll need to Extract the data (if you haven't already), and then package the workbook. Make sure to Refresh the Extract (not the workbook) before packaging it up.

                             

                            Make sense?

                             

                            --Shawn

                            • 11. Re: Live Connection Question
                              angie brannen

                              Makes perfect sense. Now, next quarter I want to add data but not lose/overwrite original data. In other words I will be running the same query with new date range and I want to keep the last quarter/historical data in Tableau, but it will not be on my Oracle database.

                              • 12. Re: Live Connection Question
                                Shawn Wallwork

                                Then you'll definitely want to work with data Extracts. Then when the new data comes in you will Append data to the data extract, NOT refresh the Extract. If you refresh the whole extract you'll overwrite the old data. One thing to be aware of is that when you Append data to an extract, that is all you are doing, adding rows. If some of the older data changed, these changes will NOT be picked up when you do the append. But it sounds like that won't affect you.

                                 

                                If the older data is being purged from your Oracle DB, then I suggest you keep backups of your tde files as this is the only place that data exists.

                                 

                                --Shawn

                                • 13. Re: Live Connection Question
                                  angie brannen

                                  That is all correct. However, I have looked up 'append' data and see there is unanswered questions/issues with appending data. And plus, I don't an append function in Tableau, just Live or Extract/Edit or Refresh/Filters am I missing an inconspicuous menu? And is there a trick on saving to a tde, I saw the file in the zipped folder so do I save the whole dashboard every time & pull out the tde file to copy/paste new data.

                                   

                                  Now that you have helped me immensely; back to the original question in regards to the F5 capability, is clearing out the cache something I want to avoid?

                                  • 14. Re: Live Connection Question
                                    Shawn Wallwork

                                    The Append data from file will be a multi-step process for you. When new data is added to the database, you'll open a new workbook make the connection, and add database filters to the connection so the old data and newly added data don't overlap. Then you'll extract the data into a new tde file.

                                     

                                    Now when you go into your main workbook, and right-click data connection \Extract\Append Data from File...

                                     

                                    (Of course if you haven't Extracted the data you won't get this option, so you'll need to do this first.) Clicking that option will bring up a dialog box that let's you point at the new tde file you just created. Clicking OK will add these to the bottom of the currently active extract.

                                     

                                    Let's see here are some answers:

                                    • Yes you missed a 'inconspicuous menu' (see above)
                                    • The tde is saved when you do the extract; you are given the option of where to save it to
                                    • Stop using package workbooks! They may not get updated correctly. To unpackage your workbook, simply save it as a twb file. Then go into Edit Data Source and point the connection to a tde (or live connection) outside of the zip file. You may also need to change out where the images are pointing. You can of course not change these out, and the workbook will work fine, but all the resources will be in sub-folders under the folder you saved the twb to. If that house-keeping works for you then fine.
                                    • Only zip the file just before sending it to someone. The best way to do this is to go to File\Export Packaged workbook. (If you just save it as a package workbook, you'll forget to save it back as a twb, and you'll be back messin' with those issues again.)
                                    • Clearing or not clearing the cache doesn't matter. F5 will simply force Tableau to redraw the view, executing all the queries anew. If you were using a live connection, and your data source was changing then this would force Tableau to get the latest set of data. But since you will be using an Extract that is not changing (except when you append new data) using F5 will really just force a redraw. The refresh you should avoid is refreshing the Extract, as this will over-write all the 'old' data.

                                     

                                    Make sense?

                                     

                                    Question for you: Why are you throwing away all the 'old' data on your Oracle server?

                                     

                                    --Shawn

                                    1 2 Previous Next