6 Replies Latest reply on Nov 25, 2013 12:30 PM by anthony.pierzchalski

    Connect data sources using a middle data source?

    anthony.pierzchalski

      Hi,

       

      I have three data sources that I'm trying to link using a middle data source.  Source 1 has Child# and Invoice$, source 2 has Child# and Parent# and source 3 has Parent# and SalesRepID.  I'm trying to get the Invoice$ per SalesRepID which is contained in source 1 and source 3, but can't directly create a relationship between source 1 and source 3.  I have established relationships between source 1 and source 2 as well as between source 2 and source 3.  However when I try to pull Invoice$ and SalesRepID into the data sheet I keep getting the error message that a relationship has to be established between source 1 and source 3.

       

      Can a relationship be made between source 1 and source 3 that I'm missing?  How do I get the Invoice$ per SalesRepID that I'm looking for?

       

      Thanks

        • 1. Re: Connect data sources using a middle data source?
          Mark Smith

          Are these excel worksheets?  If they are and you put them in the same excel workbook, you can join like regular tables starting with 2...sans the blendage.

           

          If you have to blend, it will work.  I did a simple test using 2 as the primary linking to the others.  You have to make sure 1&3 have the link symbols on (orange).  And to get attributes and measures from both 1&3 to show up properly, I had to include both the linking attributes in the viz.

          • 2. Re: Connect data sources using a middle data source?
            Russell Christopher

            +1 on Marks thoughts - if possible, use your junction table (source 2) as the first table you join on, then join #1 and #3 to it.

            • 3. Re: Connect data sources using a middle data source?
              anthony.pierzchalski

              Mark,  currently they are Excel worksheets but they're built from data from our main database and we're establishing live connections to our database.  Therefore they'll be separate tables/data sources going forward.  Plus certain data sources are used in multiple Tableau workbooks so I want to update the data source once instead of multiple times for each Tableau workbook that refers to the data.

               

              Mark and Russell,  for simplicity sake I just included 3 data sources in my example.  In reality there are closer to 6 separate data sources with many more columns for joining purposes being used in our workbooks and unfortunately there isn't one table/data source  that has a common field with all the other tables/data sources.

               

              Any other thoughts?

               

              Thanks

              • 4. Re: Connect data sources using a middle data source?
                Mark Smith

                It's usually preferable to group tables together in the same connection, so as many as possible can be pulled in already joined.  When you can't because the grain or the connection is different, then you can do blending with the relationships and orange link symbols.

                The downside to that is performance...when you blend tableau basically does the join after the datasource query has already brought back the resultset...kinda secondary processing.  I don't know the specifics, but it likely involves a lot more overhead without the punch of an rdbms.

                At any rate, you can bring them in via blending...the only thing I came across when I tested out your example was that the viz had to include the columns you create relationships for.  This seems to jive with the secondary processing type of scenario...a programmatic lookup or whatever the mechanics are.

                • 5. Re: Connect data sources using a middle data source?
                  Russell Christopher

                  Yeah, with this many tables in play you’re really almost dealing with an ETL issue if you can’t just join them all up…

                  • 6. Re: Connect data sources using a middle data source?
                    anthony.pierzchalski

                    Thanks for the information.  It seems like this situation is adding a little steam into my wanting to build a data warehouse prior to pulling the data into Tableau.