14 Replies Latest reply on Nov 6, 2018 12:26 PM by Brandi Beals Branched to a new discussion.

    Union of multiple data sources

      I would like to union (and not join) two data sources in Tableau that have similar columns (including primary key) and different rows, same as UNION operation in SQL.

      I couldn't find anything in the Tableau documentations and formus on unionizing data sources that are not from the same database.

      I tried creating a third "main" data source that has only a list of all the primary key values and then join the other two data sources to the main one, and just in case I also removed all the automatically identified relationships between the two different data sources. Still, Tableau doesn't recognize that the fields of the two data sources are actually the same fields.

      Any suggestions?

      Thanks!

        • 1. Re: Union of multiple data sources
          Russell Christopher

          Hey Naama -

           

          This is a job better done in the DW or in some sort of an ETL process - we don't support this sort of functionality "out of the box".

           

          Depending on your data sources and if you have SQL Server available, you might be able to make this work by using Custom SQL and leaning on SQL Server's OPENQUERY() function to hit the remote data sources. Assuming you'd configured SQL Server to connect to an Oracle data source (as "OracleDataSource") and MySQL (as "MySQLDataSource"), you could do something like this in Tableau:

           

          SELECT * FROM OpenQuery (OracleDataSource,’SELECT F1, F2, F3 FROM FOO‘)

          UNION ALL

          SELECT * FROM OpenQuery (MySQLDataSource,’SELECT F4, F5, F6 FROM BAR')

           

          Is this a good approach? Nope :)

          • 2. Re: Union of multiple data sources
            Michael Saunders

            I've got a similar problem. I'm importing from 2 different datasets of order level sales from 2 different companies. one source is mysql, the other is mssql.  the tables are all unique rows with a date/time, orderid, and sales$ as well as company name/channel. l and i want to manipulate the date/time files to graph the monthly weekly count and total$ of sales of the 2 companies and create calculated fields to sum the totals. can i do this? not sure what i can even link.

             

            each set also has a column of State where the transaction took place, but i'm assuming that cannot be what i use to link

             

            any ideas?

            • 3. Re: Union of multiple data sources
              Russell Christopher

              You should be able to use "data blending" to accomplish this. Are you familiar with that feature?

              • 4. Re: Union of multiple data sources
                guest contributor

                I am now. I'll spend some time learning more with the online videos. Thank you.

                • 5. Re: Union of multiple data sources
                  Michael Saunders

                  The challenge I have it that i want to link each dataset's date/time stamp field and they don't exactly match up. i was able to get datablending to work by adding separate new columns to each set for year, month, day and hour fields and then linking works however i cannot do date/time manipulation now against the worksheet. any ideas how to link on date/time stamp?

                  • 6. Re: Union of multiple data sources
                    Russell Christopher

                    You'll need to do something like convert the date/time value into a string using the STR function:

                     

                    str([MyDateTimeField])

                     

                    ....and then truncate some of the characters off the end using the MID() function.

                     

                    You'll do this in both data sets, then "blend" on the result.

                    • 7. Re: Union of multiple data sources
                      Jeremiah Lewis

                      Is there anything in Tableau10 that does this? Yes, I know that you can use the ETL process or a blend but I don't have an extra database lying around and I need to JOIN data NOT BLEND.

                       

                      Marc Rueter

                      • 8. Re: Union of multiple data sources
                        SANDIP SHARMA

                        HI Michael,

                         

                        It is really not required to break your date field into three parts, Change your workbook local setting Tableau automatically parse your date fields according to your country default date time standard.

                        For Ex.: I belongs to India and in my Country default date format is dd-mm-yyyy.

                         

                        Scenario:

                        1. Excel Date Field is showing dd-mmm-yyyy

                        2 MSSQL Date field is yyyy-mm-dd

                         

                        (In this case it will read from your computer date setting)

                        My Laptop Date time Setting- 

                         

                        From Excel

                        Database Date Time

                         

                        The moment I will fetch this data into Tableau by default Tableau will parse my Date field according to my laptop date time setting.

                        Why it is happening because my workbook local setting is automatic

                        Now I will change this for my Country, it will change the date field aromatically as per India standard (dd-mm-yyyy)

                         

                        1 of 1 people found this helpful
                        • 9. Re: Union of multiple data sources
                          SANDIP SHARMA

                          As far as data union all concern, Tableau Supports Union All within the data set with same tables layout. In case of two data set you can use Full join it will behave like a Union all only.

                           

                          Hope above two approach will solve your problem.

                          • 10. Re: Union of multiple data sources
                            Jeremiah Lewis

                            It won't really be like a union because you will have multiple fields with the same name. I have used this as a work around to this point but it is incredibly inefficient slows down Tableau considerably with large data sets.

                            • 11. Re: Union of multiple data sources
                              Marc Rueter

                              For Joins, Tableau 10 has data integration.  This allows you to make multiple connections to different data bases in a single data source.

                              • 12. Re: Union of multiple data sources
                                Jeremiah Lewis

                                Yes, I know that but again... I'm NOT LOOKING TO MAKE A JOIN.

                                 

                                I need a union and Tableau does not support cross-database unions.

                                 

                                I have multiple warehouse management systems' databases which are identical in their schema and tables.

                                • 13. Re: Union of multiple data sources
                                  Shay Hayo

                                  Hey Jeremiah,

                                  Have you found a solution to this? I also need to union same table structure from different schema

                                  Thanks

                                  • 14. Re: Union of multiple data sources
                                    Brandi Beals

                                    I have a solution!

                                     

                                    Background: I have four csv files in one location I want to union (no problem), but then I want to union a 5th csv file in a completely different location.

                                     

                                    Solution: So I set up this second data connection and drag my 5th file into the window where I am prompted to join them. In the join select a field from the left and one from the right that have nothing to do with one another. Then select a full join. This gives a similar experience to a union, though your secondary file will have a separate set of columns. I created a variety of IFULL() calculated fields to essentially coerce these columns into one.