8 Replies Latest reply on Jan 8, 2018 2:38 PM by kratika.g

    Join two extracts with several dimensions

    Estefania Gomez

      Hello,

       

      I have created two data extracts from different sources and would like to join them and use them as one single extract or workbook. I've already seen some demos that include creating a third datasource containing the fields for the joins but the problem I have is that there are lots of shared dimensions and levels of aggregation. Any suggestion?

       

      Thanks!

      Estefania

        • 1. Re: Join two extracts with several dimensions
          Dimitri.B

          Just to clarify, there are two types of joins in Tableau:

          1. The data source join (back-end) - this is the standard database join where two or more tables from the same data source (e.g. database) are joined in the Tableau connection. In this case database does all the joining and passes the combined singe data set to Tableau.
          2. Tableau's own join (front-end) - this is also known as data blend. Tableau joins two separate datasets after they are retrieved from their own data sources. It is not the same as the back-end join, and has quite a few limitations.

           

          Which join did you have in mind?

          I don't think it is possible to do a back-end join with two extracts, and from your description it looks like this is what you want.

          2 of 2 people found this helpful
          • 2. Re: Join two extracts with several dimensions
            Estefania Gomez

            Thanks Dimitri!

             

            You are right, I would like to do like a back end join with the two extracts. One of the extracts is from a MS Server and the other one from an Oracle server and it's almost imposible to generate only one extract using a connection between the servers because it would take days to retrieve the data!

             

            What I would like to do is the following:

            Let's say I have in extract 1: x1, x2, x3, x4, x5,....... variables and x1, x2, y3,y4,y5 in extract two,...........

            I woud need an extract with x1,x2,x3,x4,x5,....,y3,y4,y5.........  or at least a workbook to work with those variables  using different levels of aggregation....where x1 has thousands of values and y5 36.

             

            Thanks again!

            Estefania

            • 3. Re: Join two extracts with several dimensions
              Dimitri.B

              Estefania,

               

              Because your data is in two different databases, you can't do the normal back-end join it Tableau, where you just add a table to an existing one and configure the join. You can try using one database to connect to the other via ODBC or similar mechanism, but it is very technical and will most likely require assistance of DBA/IT. If successful, this will create a table in one DB that is a copy of a table in another DB, and then you can do the simple back-end join.

               

              Your other option is to blend two extracts in Tableau.

               

              Unfortunately, data blending is too complex to handle in a singe post, especially with no sample data. But you might want to have a look at blending section of Tableau Help to find out how it works:

              http://onlinehelp.tableausoftware.com/v7.0/pro/online/en-us/multiple_connections.html

               

              I must tell you that it sounds simple in principle, but the way it works is quite different from a back-end join, especially if your sheet layout and/or calculations are even moderately complex. So you might need to do some trial and error and looking things up in Tableau help (take a good look at the "troubleshooting" section).

              But it works, and can do wonders if mastered.

              • 4. Re: Join two extracts with several dimensions
                Dean Hewitt

                There may be another option.  If you can generate a data extract from each of the data bases and have these placed in a directory then you can use the Custom SQL option in the Text File Connection dialog window.  You can write your own SQL statement to specify the necessary join.  The data is now in a .tde file and you should have improved performance.  The down side is automating the generation of the two text files.

                 

                Dean 

                • 5. Re: Join two extracts with several dimensions
                  Dimitri.B

                  Continuing with the same logic, a slightly more sophisticated solution is to set up an Access database with ODBC links to the two tables, then either set up a query in Access or do the join in Tableau. The links would be live, so no need to have scheduled data extracts, but Tableau data engine extract might help with performance.

                  • 6. Re: Join two extracts with several dimensions
                    Paulo Wang

                    I am late to this party, but what you are trying to do is now supported on Tableau 10.x.

                    • 7. Re: Join two extracts with several dimensions
                      Anit Shrestha Manandhar

                      Hey Paulo Wang,

                       

                      You ain't late man. Thanks for the update!!

                       

                       

                      I was wondering if there is any code examples for this?

                       

                       

                      Thanks!

                      • 8. Re: Join two extracts with several dimensions
                        kratika.g

                        Hi All,

                         

                        On similar lines , Can we join two  tables extracts in a tableau view?

                         

                        What i mean is..

                         

                        Say we have extract of table A (tde file) and extract of table B(tde file). Can I join this in tableau?