3 of 3 people found this helpful
That's a pretty fair summary, Shawn.
Thanks Richard for the feedback. Greatly appreciated.
Hi Richard, sorry for hijacking that other thread, for some reason the reply button wasn't appearing for me earlier.
Thanks for responding to me in that other thread
No problem about the other thread. The forums software can be a bit temperamental sometimes – or maybe you just weren’t logged on?
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.
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!
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)
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, 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?
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.
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.
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.
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.
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?
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.
Question for you: Why are you throwing away all the 'old' data on your Oracle server?