1 2 Previous Next 22 Replies Latest reply on Sep 30, 2019 7:33 PM by satish.parvathaneni

    Update data with new records with Old in Tableau Prep ?

    satish.parvathaneni

      Hi Folks ,

       

      Can you please help me if tableau prep can update old records with new data ?  Is prep that robust to handle DML operations on the fly ?

       

      Thank you !

       

      Regards

      Satish

        • 1. Re: Update data with new records with Old in Tableau Prep ?
          Ken Flerlage

          Old records with new? If I understand you correctly, then yes--this is base functionality of Prep and the reason why you'd want to build a reusable workflow. When you have updated source data, you can run it through the workflow to get refreshed output data. Is that what you mean?

          • 2. Re: Update data with new records with Old in Tableau Prep ?
            satish.parvathaneni

            Ken ,

             

            Thanks for writing back.

             

            #File-1

            ID  , Date              Desc

            1      9/23/2019     Unit1

            #File-2

            1      9/23/2019     Unit2

            2      9/24/2019     unit3

             

            I have attached sample data which i get from two files.

             

            As you can see in File-2 , i have a update of existing record and  a new record.

             

            So i need to perform a update & insert in tableau prep. Is this doable ?

             

            Thank you !

             

            Regards

            Satish

            • 3. Re: Update data with new records with Old in Tableau Prep ?
              Ken Flerlage

              Yes, Tableau Prep is built to do exactly that. If you attach a sample prep workflow, I can show you how this works in a bit more detail.

              • 4. Re: Update data with new records with Old in Tableau Prep ?
                satish.parvathaneni

                Hi Ken,

                 

                Please find the attached .I have two files in the flow.

                 

                When it is processed with Old and New ( 1 record for update & 2 records for  inserts ) . Final Output should have 3 records with updates and inserts .

                 

                *Update required  for this combination 201803H1C1 for their respective measures ("Shipped Units", "Damaged Units"," Total Damages"). I created a Match Key in each old & new .

                 

                 

                Ken Flerlage

                 

                 

                Thank you !

                Satish

                • 6. Re: Update data with new records with Old in Tableau Prep ?
                  satish.parvathaneni

                  Ken ,

                   

                  Below will be the correct output.  

                   

                  MonthDivisionCarrier NameShipped
                  Units
                  Damaged
                  Units
                  Total Damages
                  201803H1C1400050006000
                  201803H2C2700080009000
                  201803H3C3100001100012000

                   

                   

                  Ken Flerlage

                  Thank you !

                   

                  Satish

                  • 7. Re: Update data with new records with Old in Tableau Prep ?
                    Ken Flerlage

                    I'm sorry for asking so many questions, but...In the case that you provided, the old file really becomes irrelevant because we're just going to overwrite it with information from the second file, but is it possible that you have a record in the first file that is not in the second file? Say 2018-03-01H4C4? If so, would you want to keep that record or throw it out?

                    • 8. Re: Update data with new records with Old in Tableau Prep ?
                      satish.parvathaneni

                      Ken ,

                       

                      Thanks for writing back to me.

                       

                      The old file is the current file which the user's use for reporting purpose .As soon the New file comes in we need to parse both Old & New files and get a final output which can be consumed into hyper .

                       

                      Old File - Will have bulk of the data. Just picked up one record for this exercise.

                      New File - Will have New records & Updated records for those records that previously exists in Old File

                       

                      -----------------------------------------------------------------------------------------------------------------------------------------------------

                      Answers to your Question Below.

                       

                      but is it possible that you have a record in the first file that is not in the second file? Say 2018-03-01H4C4?

                      • Yes .In this case since we don't have any corresponding record in (New File) we will push the record-as is to the output.

                       

                      If so, would you want to keep that record or throw it out?

                      • Yes we will keep it  ,Since there is no updates for that record in ( New File) .

                       

                      Thank you

                       

                      Regards,

                      Satish

                      • 9. Re: Update data with new records with Old in Tableau Prep ?
                        Ken Flerlage

                        Okay, this makes sense. So it's essentially going to be like an incremental update. This is something that is done within data warehouses all the time. Essentially, we'll load an initial set of data, then over time, we'll perform "upserts" to insert new records and update existing ones. This can be tricky because each subsequent load only needs to address the new records. With a true database as the output, this is relatively easy to do, but Tableau Prep doesn't write to a true database, so we need a sort of recursive process where we use the target file as both a target and a source. The problem is that Tableau Prep doesn't allow this--I tried it and got this!

                        So, I don't see a good way to do this sort of incremental update in Tableau Prep, unfortunately. However, I'm going to call in Joshua Milligan to see if he can add anything to this discussion.

                        1 of 1 people found this helpful
                        • 10. Re: Update data with new records with Old in Tableau Prep ?
                          Ken Flerlage

                          Note: One option would be to output this flow to a different file name. But, since that file needs to then become the source the next time you run it, you'd need to essentially rename that output file the next time you run it. That's an ugly manual process, but if that's a viable approach, I'm happy to show you how to put this together.

                          • 11. Re: Update data with new records with Old in Tableau Prep ?
                            Joshua Milligan

                            Hi Satish,

                             

                            Yes, Tableau Prep does not allow a file to be used for both input and output.  So I've worked around that by running a command line script that executes the flow then copies the output file to a new location or new name that can be used as the input for subsequent flows.  Not as clean as I'd like, but not too manual of a process once developed.

                             

                            call "C:\Program Files\Tableau\Tableau Prep Builder 2019.3\scripts"\tableau-prep-cli.bat   -t "c:\My Files\MyFlow.tfl"
                            copy "c:\My Files\Input.csv" "c:\My Files\Input_Backup.csv" /Y
                            copy "c:\My Files\Output.csv" "c:\My Files\Input.csv" /Y
                            

                             

                            This script 1) runs the flow, 2) makes a backup of the existing Input, and then 3) copies the new Output over the old Input.

                             

                            If you were automating via Conductor on Tableau Server, then you'd have to have another approach.  You might look for or develop another process that does some kind of shadow copy or listens for a file update and copies the output file when it changes or on some kind of schedule.

                             

                            Hope that helps!

                            Joshua

                            1 of 1 people found this helpful
                            • 12. Re: Update data with new records with Old in Tableau Prep ?
                              Ken Flerlage

                              Thanks Joshua! So I'm guessing I was on the right track. Do you have to do something similar to what I showed above where you check for:

                               

                              1) New Records

                              2) Changes to Existing Records

                              3) Records with No Changes

                               

                              • 13. Re: Update data with new records with Old in Tableau Prep ?
                                Joshua Milligan

                                Ken,

                                 

                                That pattern looks right. 

                                 

                                I'm assuming when new data comes in, it's a subset of data and you just have to figure out what's new and what's an update - and not that you're trying to do an incremental load from the entire data set each time.  That is, going back to the original example, when File #2 comes in, it doesn't contain all data for all time, just updates and new records and that those have to be incorporated into the existing "master" data set.  If that's true, then this should work great!


                                However, if File #2 contained an updated list of all data, then there's not really much point.  Just process File #2 and overwrite your output.

                                 

                                I'm fairly certain it's the former case, but variations of the latter case might be processed as incremental loads with upserts in traditional ETL tools for performance reasons.  But there wouldn't be a performance gain here because Tableau Prep is going to have to process all the records from both sources.  I'm sure it optimizes queries and calculations, but every record is going to end up going through the flow.

                                 

                                My personal experience with needing to reuse an output as an input has been more along the lines of capturing periodic snapshots of ever changing data - so each snapshot of records gets appended to a master data set and I'm not worried about updates.  But what you've designed here looks like it would work!

                                 

                                Best Regards,

                                Joshua

                                1 of 1 people found this helpful
                                • 14. Re: Update data with new records with Old in Tableau Prep ?
                                  Ken Flerlage

                                  Yep, that's exactly right--the second file will include changes and new records, but will not contain all records. Thanks Joshua!

                                   

                                  Satish, please let me know if you'd like to hear more about this solution.

                                  1 2 Previous Next