6 Replies Latest reply on Apr 3, 2012 10:46 AM by jasonscarlett

    Can I create a union of two tables in Tableau?

    John King

      In my database I have two tables with identical structures, tblAggregateHistoric which is updated monthly and tblAggregateRecent which is updated daily. While I can use a UNION query to import the two tables, this takes a long time. It would be nice (and a lot faster) if I could import tblAggregateHistoric once per month and import tblAggregateRecent daily and 'Union' them in Tableau. Can this be done? and if so how.

        • 1. Re: Can I create a union of two tables in Tableau?
          Richard Leeke

          When you say "import into Tableau" I presume you mean create a data extract, do you?

           

          If so, you might be able to do what you want using the incremental refresh feature.  If you define a custom SQL connection which is the UNION of your two tables, you may be able to use incremental refresh to add the daily updates.  You might or might not need to do a full refresh after each monthly update - depending on how the identifiers work on the tables.  (i.e. if the result of the UNION of Historic and Recent is the same after the monthly refresh there may be no need for a full refresh.)

           

          Note that I've never tried this, so I could well be missing something.  Worth a look though.

          • 2. Re: Can I create a union of two tables in Tableau?
            John King

            Thanks for your comments Richard. Yes I do mean "create a data extract". As some of the records in tblAggregateRecent have been modified while others have been added, I dont believe I can use incremental refresh. That is why I am using two tables tblAggregateHistoric and tblAggregaterecent.

            • 3. Re: Can I create a union of two tables in Tableau?
              Richard Leeke

              So the modified rows are all rows from tblAggregateRecent and there are no changes to any of the rows from tblAggregateHistoric?

               

              One thing you might be able to do if I understand the setup correctly is create a full extract at start of month when tblAggregateHistoric has just been updated and there is nothing in tblAggregateRecent.  Then save a backup of that extract file.  Each day, take a copy of the backed up extract and do an incremental extract, so you are adding in everything that has changed since the start of the month.  Obviously that incremental extract would get slower and slower over the course of the month, but it could still be much quicker than the full refresh.

              • 4. Re: Can I create a union of two tables in Tableau?
                John King

                Great minds think alike!, I've just come to the same conclusion. However as I understand it, the incremental extract must come from the same 'source'. I achieve this by using a view as the source - initially as 'select * from tblAggregateHistoric' and later as 'select * from tblAggregateRecent'. It appears to work.

                 

                An added benefit is that at month end, the extract of tblAggregateHistoric can be updated with one months data (using a new version of the view)before being saved.

                 

                Thanks for your comments.

                • 5. Re: Can I create a union of two tables in Tableau?
                  Richard Leeke

                  Good idea with the view.  I was thinking you could do it with that custom SQL UNION query, though depending on how your tables are indexed I can imagine your view approach might be better.

                  • 6. Re: Can I create a union of two tables in Tableau?

                    Can someone elaborate on this solution. I am looking for something similar but am having trouble figuring out the whole extract/view process.

                     

                    My situation involves updating Emergency Room data daily with a refresh of the last week or so of data. I envision a base table (5 million records) with a daily update table containing a few weeks of data (50,000 records).

                     

                    Will this solution work on a Tableau Server or just the Desktop?

                     

                    Thanks

                    Jason