The problem described herein has happened to me far too many times and I am somewhat embarrassed to have to write this post, but I feel that someone has to do it. Most people start work in Tableau with a fresh data set. They build their dashboards and worksheets and then suddenly the job is done. For some people, their data source might have to be refreshed in the future and this is easy to do. However, if you use Tableau long enough, there will be occasions when you want to reuse a completed Tableau workbook on a new data file in a new directory, thereby keeping your original work intact. Learning how to reuse a previously completed Tableau workbook is the subject of this post. I am almost ashamed to admit my struggles in this regard and hope that someone doesn’t write to me immediately and say: “Hey, you can do it this way!” I feel like I am exposing myself to snickers and jeering from the true Tableau masters. My only hope is that I can help others learn how to successfully reuse workbooks.
If you like this article and would like to see more of what I write, please subscribe to my blog by taking 5 seconds to enter your email address below. It is free and it motivates me to continue writing, so thanks!
Update the following day
Within a few hours of writing this original post, Tableau Zen Master Jen Underwood wrote and asked me if I had ever tried the “Replace Data Source” option in Tableau. The answer is no, primarily because I didn’t even know that that option existed (I think these features were added in Tableau Version 7, but I’ve been using my method for many years before that). As shown in Figure 1, you can see that the option is not activated (it is shown in light grey) in a normal workbook. According to this Tableau knowledge base article from Nov 22, 2013, there are two methods to choose if you want to replace the data set to be able to reuse a workbook. The first method is completed by adding a second data connection and then replacing the data source and the second method is using a bookmark and then replacing the data source. The technique I described in this post might be considered a third method, even if it is “old school”. Since I haven’t tried the Tableau sponsored methods yet, I’ll save my comments for later, once I have a chance to try these and see how they work compared to the way I have been doing this for years. Thanks Jen!
On countless occasions, I have wanted to reuse a completed Tableau workbook by linking it to a new data source. This normally happens after spending a lot of time building a Tableau workbook (*.twb) that has a multitude of dashboards, calculated fields, parameters, special formats, special approaches, advanced table calcs, etc. I complete the work and demonstrate the results. I think the job is done and it is time to move on to the next task. Suddenly, the problem happens. Someone says to me: “I really like that Tableau workbook and have essentially the same database as you used. Can you make one of those for me?” The next few words that fire in my cerebral cortex are something like: “Oh ?hit, not again!”.
Since I have been hit in the face with this problem many times (and many times it is a self-inflicted injury!), I recently decided to go toe-to-toe with Tableau to document my working solution for reusing workbooks. The problem definition is this: When you have a Tableau workbook that is linked to a particular directory, a particular database source file, and you are using a Tableau data extract (*.tde), making a copy of this workbook for use on another database in another directory is not as easy as it should be. It should be a piece of cake to do, but rarely does it work the way you want it to unless you really pay attention to the details of what you are doing. In some instances in my beginner years of using Tableau, I ended up confused and had to scrap the whole workbook and rebuild it from scratch using the new data source in a separate working directory. Although it is always easier to build a workbook the second time around, I generally don’t want to have to do it.
Luckily for me, the self-descriptive language XML is used to store the Tableau project settings in the Tableau workbooks (*.twb). I have a good working knowledge of XML and this comes in handy when you are trying to do things like this in Tableau. In the video shown below, I explain the approach that I use to make a copy of a completed Tableau workbook so that I can use it in a new directory and with a new data source. This method is reliable and can be replicated as many times as you want.
Here is the multi-step process that I use to make a copy of an existing Tableau workbook. All of these steps are shown in the video below.
- Make a new directory for the new Tableau project.
- Copy the Tableau workbook (*.twb) file that you want to reuse and place it and your new data file into the directory created in step 1.
- In your original directory, make a copy of your existing Tableau data extract (*.tde) file. Why do this? This original data extract will get destroyed by accident because of the way Tableau is currently set up. This will save you from having to rebuild the original extract and all you have to do is rename the file (see step 10 below) after you are finished with the steps listed below.
- Go to the new directory. Open the *.twb file in Tableau. Choose the Data menu, go to last line where the active file name is and click the right arrow to access the menu choice Edit Connection and then browse into your new directory and pick your new data file. While you are in this menu, you might as well edit the data connection name at the bottom of the menu (this doesn’t automatically get updated, although it should when you pick a new data connection) to avoid any confusion in the future.
- Tableau will tell you that a data extract is active and asks you if you want to update it. You need to say yes. When you do this, you will destroy your original extract in your original project directory because the data extract path stored in the *.twb is still is pointing to the original directory.
- Now save your *.twb file and exit Tableau.
- Now you have to manually edit the *.twb file and update the path to the data extract. You need to search on “.tde” and change the path to the new project directory. Save the *.twb file.
- Open the *.twb file in Tableau and you will get a message that says the extract does not exist. Pick the fourth option to regenerate the extract, which will be placed in your new working directory.
- Now you have successfully completed making a copy of a Tableau workbook, with all of its details intact and it is now connected to a new data source. This will work great as long as your new data source has at a minimum the same fields that are used in the original Tableau workbook. You can always add more fields to your new data source but be careful in removing any for fear of losing embedded functionality from the original workbook.
- The last step is to go back to the original directory and replace the *.tde file with the original version of it that you created in Step 3 above. This will relink the proper data with *.twb file in your original project.
This procedure seems a bit extensive but once you do it a few times, it becomes a part of your normal working Tableau toolbox. If Tableau were designed differently, these procedures wouldn’t be needed ( I have a lot of thoughts on that topic floating around in my head). If there is a better way to do this, I don’t know about it (update: now I know of a different option, so see the next section) and I have had many instances where I had to make a copy of an existing workbook for a new data source. Most of the time, my haphazard approach to making the copy failed and left me confused as to why it failed. Using this procedure allows me to save countless hours in replicating existing workbooks with complete reliability and functionality.
Update a week later – How to use the Tableau Replace Data Source Option
I finally had a chance to try the replace data source option that is provided directly by Tableau, as shown in Figure 1. This option can be activated by loading a new data source (which will now be the the second data source in the workbook). Once you load a new data source, the option will become available for you to use and it is very simple to do. I created a short video to demonstrate how it is used. This is a simpler procedure than what I described above as my method, so I’ll now be using it with confidence. The nice thing about this data source replacement utility is that it replaces the links to both the source file and the tableau data extract, including updating the paths to both of these files.