3 Replies Latest reply on Dec 21, 2012 3:07 AM by Robin Kennedy

    Resolving chasm traps in Tableau

    Robin Kennedy

      How do you go about resolving a chasm trap (caused by a many to many relationship in your data) where using normal table joins creates a partial Cartesian product in Tableau?

       

      Create two different data sources and blend?

      Make use of COUNTD? (and SUM / COUNTD etc etc)

       

      Or some other way?

       

      Thanks!

        • 1. Re: Resolving chasm traps in Tableau
          Robert Morton

          Hi Robin,

           

          Data Blending is a great way to avoid this kind of problem, because it performs joins only after aggregating each data set separately along a common dimensionality, and the results cannot be further aggregated.

           

          Another approach is to perform the same kind of post-aggregate join yourself using Custom SQL. You will have to aggregate data from both tables to a common dimensionality prior to joining, which you can do using a subquery. This typically follows the form:

           

          select derived_table_1.field1, ..., derived_table_2.field1, ...

          from

          (

          select dim1, dim2, ..., AGG(meas1), AGG(meas2), ...

          from actual_table_1

          group by ...

          ) derived_table_1

          inner join

          (

          select dim1, dim2, ..., AGG(meas1), AGG(meas2), ...

          from actual_table_2

          group by ...

          ) derived_table_2

          on  derived_table_1.dim1 = derived_table_2.dim1

          AND derived_table_1.dim2 = derived_table_2.dim2 ...

           

          I hope this helps,

          Robert

          • 2. Re: Resolving chasm traps in Tableau
            Alex Kerin

            That's a great answer Robert, and convinces me I must take a SQL class

            • 3. Re: Resolving chasm traps in Tableau
              Robin Kennedy

              Thanks very much for your input, Robert.

               

              I also found an old discussion between yourself and Alex on these forums which helped confirm I was heading in the right direction

               

              I found that the best approach for my needs was indeed to use data blending where each 'side' of the 'chasm' was aggregated up individually. I did run into a few issues where I did not want to see the level of detail of the field that defined the relationship between the two data sources in the view, but I could get around that by using WINDOW_SUM table calcs or by creating another data source pointing at the same tables but using a different aggregation.

               

              I think Tableau 8's ability to blend without the use of the field in the viz will really help with these sorts of problems in future!

              1 of 1 people found this helpful