<< Back

Using Tableau’s Replace Data Source function

replace datasourceTableau 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

beforeIf 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.

afterPersonally, 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

imageIf 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

imagecalculated 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!

Robin Kennedy

London, UK

11 thoughts on “Using Tableau’s Replace Data Source function

  1. Great article Robin. I spent a lot of time recently trying to unpick the calculated fields that wouldn’t get transferred across (and try to determine why). I am really glad I will have this as a reference for next time so thanks for writing.

  2. In regards to the Calculation_5190609155201802 issue, could you also just move that calculated field that’s being nested onto the detail card? Would that be sufficient for Tableau to consider it to be “used” for purposes of replacing the data source?

    Just wondering if this would be more effective than creating a temp workbook page for the sake of keeping the field, because then other future iterations wouldn’t require you to keep that temp workbook or make it again.

    Great article, thanks!

    B

  3. Good thinking, Brian. You could indeed use the nested calc on the details card, but if it were a dimension it might start to screw with your view, hence the mock sheet(s) instead.

  4. When replacing datasources this way you may find that quick filter configuration settings are lost. The workaround is to make sure all sheets are rendered prior to replacing the datasource.
    Pedro

  5. In my experience, using the “Edit Connection” dialog instead of “Replace Data Source” eliminates a huge amount of headaches… but it may not always be an option.

    When using “replace data source”, if you manually select each sheet in the workbook before replacing, your metadata should carry over to the replaced data source. I believe this works in most every scenario in which the schemas/columns match exactly.

    I, too, hope Tableau will fix this. The workaround Pedro and I have mentioned is discussed here as well:
    http://community.tableausoftware.com/docs/DOC-5359

    I hope this helps users. I now use the “Edit Connection” dialog almost exclusively, as metadata is carried over when this option is used. But I believe this can only be useful when replacing the data source with the same type of connection (SQL Server, Excel, etc.)–I could be wrong on that, but I suspect that is the case.

    1. In addition, all field name changes will carry over to the newly replaced data source when using the “Edit Connection” dialog — simply replace the connection String to point to the new data source, and Viola, everything works like magic. But again, this may only be applicable when the two data sources are of the same type.

      Otherwise, I make sure to select each and every sheet before replacing the data source to avoid the Calculation_452345 type errors, dropped quick filters, etc. For some reason, this tells Tableau to keep all the metadata associated with the data sources used in the sheets within the workbook.

      1. Same issue here.lost quick filters after switching data source.

        Same table adding new field, is tableau needs new data connection? how to update the existing connection with new field?

  6. suppose i have a dashboard with one data source and now i use that dash board into my client web application. can i change data source for dash board without disturbing dashboard in web application? i mean that my client may be changes the data what he want.

  7. Matt Lutton nailed it. Today was the first I’d heard about loading each dashboard prior to “Replace Data Source”. Seems to have worked like a charm. I, too, tend to use Edit Data Connection – less headaches. But in this case Tableau opted not to play nicely with my original data source, so I had to re-do.

    Steps to not forget: 1) Connect your new data source 2) Copy/Paste any calculated fields (dimensions or measures) from old data source to new 3) Load each dashboard in your workbook 4) Replace data source. #2 Can also involve fixing some calcs if you have aliased fields, etc. as mentioned in the article.

Leave a Reply

Your email address will not be published. Required fields are marked *