3 Replies Latest reply on Dec 6, 2012 2:24 PM by Jonathan Drummey

    Combining Data Sources -- Want data from both sources

    joseph.bertram

      hi everyone,

       

      I'm trying to combine data from 2 different sources.  I've setup the relationships between the sources so that conformed dimensions are linked.

       

      At first everything looked good.  But when I drilled into the data on a linked date dimension, I found out that if there was no data in the primary data source, the secondary data source would be blank, even when there was data in the secondary data source.

       

      How do I combine them equally without loosing data in the secondary data set?  Meaning, if I look at data on the on December 1, 2012, I should see all data from both primary data set and secondary data set, even if primary does not have data on that date but secondary does (and vice versa).

       

      Thanks!

       

      -=Joe

        • 1. Re: Combining Data Sources -- Want data from both sources
          Catherine Rivier

          Hi Joe,

          So unfortunately, I believe a manual link to data between data sources is always a Left Join.  (Just in case you're not familiar, that means all records from your Primary source, and only those records in your Secondary source where the records are equal.)

           

          The easiest solution will work only if you want the opposite:  all records in your Secondary source and only the matching records in your Primary source.  And the solution behind that is to simply reverse which one is Primary and which one is Secondary.  But you'll have the opposite problem with non-matching records...

           

          Other solutions will take a little more work, and depend on your data sources, which is where you'd want to do some manipulation.  You essentially want all records you'd possibly want to show up in whatever data source you use as your Primary, and all of your queries could work.  So if it's Date, for example, you could add within the SQL of your Primary source one record for all dates.  For example, you could Union join to a table that just lists every Date through 2013.

           

          Another potential solution (that probably won't work based on your description above) is to have duplicate versions of the view with one with data source A as the primary, the other with data source B as a primary, and somehow get those to act within one filter (perhaps through a Parameter or Global Filter?).

           

          Vague description of solutions, I know, but hope this starts to help?

          • 2. Re: Combining Data Sources -- Want data from both sources
            joseph.bertram

            cool.  Thanks for the ideas.

             

            I think I'll try to fill in the missing data points and see how well that performs.

             

            Hope version 8 will allow for a full outer join instead of left versus right...

             

            Thanks!

            • 3. Re: Combining Data Sources -- Want data from both sources
              Jonathan Drummey

              @Catherine: You're close, but Tableau's data blending is not a true left join, though life would be easier if it was. What Tableau does is issue two queries, one to the primary data source and one to the secondary. The query on the secondary is an aggregate query at the level of detail defined by the linking fields in the view that will only return one record per combination of the values of the linking fields, whereas a normal left join would return as many rows as were in the secondary that met the joining criteria. Once the data is returned inside Tableau, the secondary is "left joined" back to the primary.

               

              @Joseph: I haven't seen anything in the Tableau v8 beta that would allow for a full outer join, you can always do that via your own query or Custom SQL. In cases like this I recommend those solutions.

               

              I started on this question earlier this afternoon and discovered that you could use Tableau's built-in date padding via Show Missing Values to get the blend to work, but that padding is really sensitive to the arrangement of pills in the view and not suitable for most applications. I wrote a quick blog post on this at http://drawingwithnumbers.artisart.org/blending-using-show-missing-values/.

               

              Jonathan

              1 of 1 people found this helpful