8 Replies Latest reply on Dec 7, 2018 1:50 PM by Chris Gerrard

    Replace individual value in data source

    Al Genin

      I have a relatively large data set (~5 million rows, 50 calculated metrics) and there are a few bad data values that have come in from the source system.  Basically a cashier fat fingered a transaction, so it reads in the millions of dollars and should read under $10. 

       

      There are only a handful of transactions that need to be changed, and I don't want to alter a bunch of calculated metrics to get there.  I cant change the source (POS system).  Is there a clean and sustainable way that I can overwrite these values in the tableau data source?  The source is generated by a sql query from the source system, refreshed daily tableau server. 

       

      Thanks!

       

      Al

        • 1. Re: Replace individual value in data source
          Chris Gerrard

          When you say "The source is generated by a sql query from the source system" do you mean that the connection is via custom SQL?
          If so, is there a compelling reason for this, or is it because that's the habit, it's the way it's done in other tools, etc?

           

          Up front it's worth mentioning that Tableau was designed to faithfully represent the data it's being asked to access, not as a tool to change existing data.

           

          That said, the simple approach would be to have a set of adjustment data that can be used to offset the problematic source data.

          In this case, a mirror transaction that adjusts the original transaction in all aspects except that the value to be adjusted is made negative by the necessary amount is added.

          The adjustment data is unioned to the original source data, with the result that any time the transaction is included in an analysis it picks up both the original and adjustment amounts, resulting in the 'correct' amount showing up in the final output.

          It would be helpful for the adjustment data set to include an additional field containing a flag value identifying those rows as adjustment rows. This would make it straightforward to identify the adjustments made to the source data, which is going to be of interest.

          • 2. Re: Replace individual value in data source
            Al Genin

            Hi Chris

             

            To answer your question, we are using custom sql to query the source data base (POS system).  This is refreshed daily with yesterdays transactions (incremental  refresh). 

             

            Thanks for the recommendation.

             

            Al

            • 3. Re: Replace individual value in data source
              Michael Gillespie

              Al, the point of Chris' question is more "why are you doing that"?  Custom SQL is useful but has limitations and can cause performance issues.

               

              Is there a reason you are doing that vs. a Tableau extract plus scheduled incremental refresh (for example)?

              • 4. Re: Replace individual value in data source
                Al Genin

                I may not be tracking, but here are the steps we are taking today:

                 

                1. Use custom sql in tableau desktop to create extract
                2. Upload exctract with incremental refresh to tableau server
                3. Configure incremental refresh schedule on tableau server

                 

                Is there a more efficient method that I'm not considering?

                 

                Al

                • 5. Re: Replace individual value in data source
                  Michael Gillespie

                  Getting there!  What is the reason for using Custom SQL?  Are you doing complex joins, or unions?  Are you only returning a subset of columns from the source data?  Are you aggregating/summarizing or otherwise manipulating the data before you bring it into Tableau?

                   

                  Custom SQL can cause performance issues, and can be inefficient in the way it causes Tableau to query the source database.  We're always wary of using it in production vs. creating a view (for instance) in the source data you can directly attach to.

                   

                  This may work just fine for your purposes - there's nothing wrong with it conceptually.  You just have to be aware of the potential drawbacks.

                  • 6. Re: Replace individual value in data source
                    Al Genin

                    Thanks for your help on this one Michael.  We are using custom sql as a temporary solution while we build out data warehouse / BI stack. 

                    • 7. Re: Replace individual value in data source
                      Michael Gillespie

                      Ah, that makes perfect sense!  In fact, it's my favorite use for custom SQL.

                       

                      I'll shut up now...

                      • 8. Re: Replace individual value in data source
                        Chris Gerrard

                        To try and add a little explanation and perhaps some clarity...
                        My experience is that people tend to use custom SQL because that's how they've connected to data with other tools.
                        My position is that custom SQL should only be used rather than establishing a direct Tableau connection when custom SQL offers some real benefits, i.e. accomplishes some functionality that Tableau doesn't provide.
                        I think that custom SQL is vastly over-used, to no good purpose, and some detriment.
                        One drawback of the reflexive use of custom SQL is that it impedes the experience that leads to effective and appropriate use of Tableau.

                         

                        Please don't take this as a criticism of your use of custom SQL, Al. I don't know enough about your situation to have an opinion.