3 Replies Latest reply on Feb 10, 2019 11:24 AM by Michael Gillespie

    Replacing a data source - calculated fields broken

    James Frick

      In Tableau v 2018.1.7, I have inherited a large workbook with 19 dashboards.  The 19 dashboards are built with a large number of sheets.  There are 84 calculated fields.

       

      The 6 data sources for the dashboard are built with customer SQL code embedded in the workbook, which is very slow to refresh and very time consuming to work with in Desktop.

       

      I have extracted and built 6 published data sources which make refreshing and working in the Desktop better, except replacing the existing data sources breaks most of the calculated fields.

       

      Is there anyway to speed this up?  Or does anyone have any tips or techniques on doing the replace of data sources more efficiently?

       

      Thanks,

       

      James

        • 1. Re: Replacing a data source - calculated fields broken
          Michael Gillespie

          Not enough info to help, James.

           

          What breaks, exactly?  And how does it break?

           

          Where are the calculated fields: in the workbook or in the published data source?

           

          Can you repair them manually?  If not, why not?

           

          I realize this is a case where you can't upload a sample workbook, but as much detail as you can offer will help us help you.

          • 2. Re: Replacing a data source - calculated fields broken
            James Frick

            Here is the sequence of what I do:

             

            1.  Open workbook, open a sheet that uses the data source to be replaced.  All of the dimensions and measures show in blue as correct.

             

            2.  Add a data source.  The original data source is a customer SQL query embedded in the workbook.  The new data source is the exact same custom SQL query I have copied and made into a published data source.

             

            3.  Replace the original data source with the new data source.  Most of the dimensions and measures go red.

             

            When I try to correct this, the calculation formulas reference columns that are not in my second data source.  At least maybe the names aren't.  The two data sources are identical based on the SQL code.  There is no difference here.

             

            What I cannot figure out is how the calculated fields are using column names that are not in the data source.  I am able to trace one calculated field to another, but at some point in following the calculated fields, I reach a point where a column or field referenced in the formula just does not exist in the new data source, at least the name does not exist.

             

            Since there is no documentation on the workbooks, I am trying to reverse engineer the sheets and the calculated fields, and I guess I need to reverse engineer the renamed fields.  (Why doesn't Tableau have some tool to document this on a workbook, like a definition of components in the Workbook?  Other BI tools have this.)

             

            Any way, what am I doing wrong?

             

            Thanks for your reply.

             

            James

            • 3. Re: Replacing a data source - calculated fields broken
              Michael Gillespie

              1.  Open workbook, open a sheet that uses the data source to be replaced.  All of the dimensions and measures show in blue as correct.

               

              Some questions on this:

              A) Is this a live connection or an extract?

              B) Can you edit a calculated field in this data source or do you get prompted to edit a copy?

               

              2.  Add a data source.  The original data source is a customer SQL query embedded in the workbook.  The new data source is the exact same custom SQL query I have copied and made into a published data source.

               

              A) So the original data source is local to the workbook ONLY, and the new data source you have created using the same Custom SQL is published to Tableau Server or Tableau Online?

              B) Extract or live?

               

              3.  Replace the original data source with the new data source.  Most of the dimensions and measures go red.

               

              A) This is confusing: do you mean the Calculated fields that are Dimensions and Measures go red, or do OTHER fields also go red?

              B) Have you looked at the Alias list to see if the raw columns that are being returned by the Custom SQL have been renamed?  Right-click on Measure Names, select "Aliases..." and you'll get a list of everything that has been changed.  Does that list correspond to your red fields?

               

              It's really important here to distinguish what's going on with calculated fields from what's going on with raw or native fields.

               

              Calculated fields in Tableau reference the in-workbook (or in-datasource) alias for the column, NOT the native column name from the table/database.