Tableau has a great little feature that allows you to change all worksheets and dashboards in a workbook at once so that they use a different data source. This is useful for a number of reasons such as changing to a more up-to-date version of the data (if the original data file cannot be updated), swapping in a new data source if the original has been lost, changing from a file based data source (e.g. Excel) to a server based data source (e.g. SQL Server) or to migrate from a ‘test’ environment data source to a ‘production’ one.
This functionality is accessed either from the Data > Replace Data Source… option from the menu bar at the top of the Tableau Desktop screen, or by right clicking on one of your data sources and clicking Replace Data Source… from there. The window shown above then appears and you select the one to be replaced in the top option and the replacement itself in the bottom option.
Easy, right? Well, yes, but there are a few things you need to be aware of when using this functionality as Tableau can sometimes appear to behave in an odd manner. This post examines the limitations and errors you might encounter when using the function and how to fix them.
Default formatting
If you have set up any default formatting in your data source “metadata” i.e. number formats (e.g. currency in $ in thousands, % with 1 decimal place etc.), default colouring, default aggregation, comments, aliases, hierarchies etc. then unfortunately these all have to be set up again. These bits of metadata are kept in the data source .tds file and are not carried across to the new source.
Personally, I think this is a limitation that shouldn’t be present and I hope to see this become automatic in future versions of Tableau. For now, one way to access the metadata in the old source is to open up the .tds file in a program that can read XML (Excel will do), but it is still a manual process to add the metadata to the new source unless you’re really confident in editing the XML in the new .tds file.
It’s worth noting here that any sheet level formatting (i.e. non-default formatting) is maintained during a Replace Data Source action. This can cause some confusion when trying to diagnose why Tableau has carried across some formatting from the old source to the new one.
Different field names
If your new source has got slightly different field names, even a tiny bit different like a trailing space or something, then Tableau will not like it and will show a red exclamation mark next to the original field name, indicating that it does not exist in the database. You can see in the screen shot that the field ‘Postal Code’ has this error.
Luckily this is an easy fix. Simply right click on the field having the error and select Replace References… In the pop up window select the new field (in this case the new field is called ‘Zip Code’) and click OK. This will change all sheets that used the original field called Postal Code to now use the field Zip Code.
Note that if you never used the original field that existed in your initial data source in any sheet or dashboard then you will not see this error, the field will simply not be seen in the new source.
Calculated Fields
Here’s one that causes a fair bit of trouble and confusion. Calculated fields / formulas DO get transferred over to a new source when using the Replace Datasource function, but, similarly to different field names above, if they are not used in any view then they will not be transferred. That might be OK – you don’t need that calculated field anymore anyway, but sometimes this causes a knock on effect because
calculated fields that use other calculated fields (i.e. nested formulas) can go all pear shaped
You may see an error saying ‘The calculation ‘<xyz>’ is invalid’, and upon editing the formula you may see references to things like
Calculation_5190609155201802
in your formula… What the frack is that?! Well, this is Tableau saying it cannot find the calculated field that used to be used as part of the formula for this calculated field so it has given it a temporary name in the mean time.
So, what’s the best approach to fix the issue here? One way is to go back into to your original data source, find the calculated field that has gone AWOL (you might want to click Edit on the second calculated field to see its real name) then copy it across manually to the new data source by right clicking it, selecting Copy, then right clicking in the data window of the new data source and click Paste (note that Ctrl+C and Ctrl+V keyboard shortcuts do not work for this type of copying)
Another way would be PRIOR to using the Replace Data Source feature, create a temporary worksheet or worksheets that use your calculated fields in the view, so that they do get migrated across.
Other things to check
Anything you may have has in Data Source Filters will need to be set up again, and it’s worth checking all those custom or automatic relationships used for blending, particularly if you’ve been using the Replace References option.
If you are planning to use the Replace Data Source feature with Tableau Server data sources, AND those sources live on different servers or different sites on a single server then you’re in for a treat because you’ll have to go through the process twice. This is because your workbook is intrinsically linked to a single site and if you want to switch sites you will first have to log off. You can’t log off until your workbook is not connected to any Tableau Server data sources, so the process for this scenario would be to
1. Create a local copy of the data source
2. Switch from the server data source to the local data source
3. Log off from the server
4. Log on to the new server / site
5. Connect to the new Server data source
6. Switch from the local data source to the Server data source
It isn’t ideal, it does feel a little bit on the manual side, but thankfully it’s usually pretty infrequent to have to do this. Tableau are always striving to improve the way their product operates and make things more streamlined so lets hope that this becomes a little smoother or even automated in future releases.
Have any tips in using this particular feature? Use the comments below to share!