In hopes to help someone who runs into this problem, below is the information on how to reproduce this problem. I've support a case to Tableau and I will update as I learn more from them.
Issue: Workbook calculations corrupted by published data source parameters
When an existing publish data source with an extract has a new parameter added, the existing connected workbooks become corrupted with the newly added data source parameter after saving the workbook. This was noticed when implementing the following workaround reducing the time it takes to modify an existing data source. https://www.tableau.com/about/blog/2013/9/easy-empty-local-extracts-25152
Desktop Version: 9.3.6
Server Version: 9.3.0
Steps to Reproduce Behavior
- Open Desktop and connect to a Microsoft SQL Server
- Select a database and a table.
- Change from Live to Extract.
- Switch to Sheet1 and save the TDE
- Publish the data source to Server
- Close Desktop and reopen
- Create a connection to Tableau Server and select the newly published data source
- Create 2 new parameters (Parm1 and Parm2); type of float
- Create a new calculated field (Test Parms) that has a calculation of [Parm1]+[Parm2]
- Drop new calculated field onto the rows shelf
- Save the workbook to your computer and close Desktop
- Go to Server and locate the published data source and download it opening it up in Desktop
- Create a new parameter called Bad Parm with a type of Boolean
- Show the parameter
- Create a new calculated field called Bad Calc with a calculation of [Bad Parm]
- Switch to the Data Source tab and add a filter to the Extract.
- Filter on Bad Calc excluding True value
- Change the value of the Bad Parm to False
- Publish data source to server overwriting the existing version and close Desktop
- On Server, locate the data source and Refresh Extracts and wait for completion
- In Desktop, open the workbook you saved.
- Notice the Bad Parm does not show up in Parameters, however the Bad Calc does under Dimensions.
- Immediately save it and close Desktop
- Open the workbook up again and notice the Test Parms calculated field is invalid.
- Edit the calculated field and you will see it’s calculation has been changed to [Bad Parm]+[Parm2].
- Also notice the Bad Parm now shows up in Parameters.
I can confirm that the issue exists in Tableau Desktop/Server 10.1. It has triggered for me on a number of workbooks that (1) have parameters and (2) are being migrated to a new data source that has a parameter of its own.
I have access to a 10.5 dev environment, so I will try to reproduce the issue there to see if it's still an outstanding bug in the latest version of Tableau.
Did you submit a ticket with Tableau Support? If so, what did they conclude? I would be very interested to know what they said.
Sorry you found this problem Jamieson. It is a frustrating one. The last response I got from Support was back on 2/14/2017 and it was
I am just following up on this support case.
At this point, my Development team is working to incorporate a fix for the issue by changing the manner in which Metadata is handled.
Though I cannot give a firm date, due to the many moving parts that go into a software release, Tableau maintains a frequent maintenance release cycle.
Given you are still seeing the behavior in 10.1, I suspect they've not fix it yet. I'd be curious to see what you find in 10.5. We're on 10.4.1 and I will see if I can find time to try producing it.
1 of 1 people found this helpful
Thanks for the update Lee. I confirmed that this issue still exists in 10.5. My steps to reproduce are below (slightly modified/simplified from your process):
Initial Data Source
- Create a new workbook "Corruption Test DS" (datasource) with some sample data. I used a few dozen rows from an Excel file.
- Make the data source an extract, then save the workbook. (Hint: save as TWBX to avoid an external TDE file.)
- Publish the data source to Tableau Server 10.5. Call it "Corruption Test".
- Close the "Corruption Test DS" workbook.
- Create a new workbook "Corruption Test WB" (workbook).
- Connect to the "Corruption Test" data source on Tableau Server 10.5.
- Create parameters [WB param 1] and [WB param 2], both type float.
- Create calculated field [WB param test]. Make the calculation [WB param 1]+[WB param 2].
- Put [WB param test] on the Marks shelf.
- Save and close the "Corruption Test WB"
Modify Data Source
- Open existing workbook "Corruption Test DS".
- Create parameter [DS param 1], type boolean.
- Create calculated field [DS param test]. Make the calculation simply equal to [DS param 1].
- Publish the data source to Tableau Server 10.5, using the same name "Corruption Test".
- Close the "Corruption Test DS" workbook.
Observe Corruption in Workbook
- Open existing workbook "Corruption Test WB".
- Observe the following:
- All 3 parameters are visible: [WB param 1], [WB param 2], and [DS param 1]
- The calculated field [DS param test] is visible and correct
- The calculated field [WB param test] is visible but marked as invalid.
- Inspection of [WB param test] shows that it now equals [DS param 1]+[WB param 2].
- Create a new calculated field. Start to type "WB". Observe that only [WB param 2] appears in the auto-lookup. [WB param 1] never appears.
- Note that if you fully type out "[WB param 1]" (including the brackets), the calculation editor will recognize it, syntax highlight it, and accept the calculation as valid.
- If you save the workbook again and reopen, and the issue persists.
An inspection of the various files shows that, yes, the data source and the workbook both have a parameter that is internally named [Parameter 1].
From the TDS of the "Corruption Test" published data source:
From the TWB for the "Corruption Test WB" workbook:
Note that, after the data source has been modified, you can open "Corruption Test WB" and re-save it, and the parameters will have been renamed to avoid a name conflict. Specifically, [DS param 1] was renamed from [Parameter 1] to [Parameter 3] to make way for the existing workbook parameters.
From the <datasources> section of the TWB for the "Corruption Test WB" workbook after the corruption:
However, in the worksheet <view> block, the parameters are cited using unmodified internal names. This seems to be where the issue lies.
2 of 2 people found this helpful
It appears this issue has also been captured in this knowledge base article:
Thanks for all of the info. And thanks for the KB article. It is interesting they say it occurs because the internal ID given to the parms is the same. One would think they would assign a GUID or something to avoid conflicts like this.
Totally agree. Using an internal name of "Parameter 1" is just asking for collisions.
The use of a GUID would be consistent with how Tableau internally identifies calculations, e.g. "Calculation_1599059375666290688" in the screenshots I posted earlier. And even if the display name of the parameters is the same in a workbook and data source (which suggests that the two need to be merged), I reckon the same logic they use for calculations could be applied to parameters. The merge logic for calculations, at least, seems to behave exactly the way I would expect it to, each time I've done a merge.
Anyway, I'll keep an eye on this and see if a fix is forthcoming in Tableau 11.
1 of 1 people found this helpful
You can work-around this issue if you're willing to edit the XML in your Tableau workbook before adding new parameters to your data source. You just need to move your workbook parameters to higher unused parameter numbers so they don't collide with the data source. I have used the following approach with success:
- Create a blank workbook and add your Tableau data source. Save the empty workbook as "Base DS".
- Open the "Base DS" file in a text editor and search for the text "[Parameter ". The first hit should be the block near the top of the file where all workbook parameters are defined.
- Make note of the data source parameters by name and Parameter #.
- Open a backup copy of your existing Tableau workbook in the text editor, search for "[Parameter " again and make note of any parameters in your workbook that don't match up with the data source parameters by Parameter #. These are the local workbook parameters
- Now you just want to move all of your local workbook parameters to higher numbers so they won't collide with the data source. If your highest data source parameter is #5, then move all of your local workbook parameters up to numbers above 10 (higher if you want more room to grow). Use Find/Replace in your text editor and replace all instances of each parameter name. [Paramter 1] becomes [Parameter 10], [Parameter 2] becomes [Parameter 11], etc.
- Save your workbook and re-open it just to make sure everything still works.
- Now go back and add the new parameters to your data source. If the highest was previously Parameter 5, the new ones will be created as Parameter 6, Parameter 7, ... Re-publish your data source to the server.
- Re-open your existing workbook and you should see all of the new parameters appear, and everything else should still work.
Not an ideal, but I find it easier than adding parameters to your data source and then fixing all of the broken references in your existing workbooks. Your mileage may vary.
Thank you Kevin for the suggestion. I will certainly take this into account the next time we experience this problem.