1 Reply Latest reply on Jul 17, 2012 2:23 PM by Robert Morton

    Multiple Tables Connection Including Multiple Fact Tables Results in Data Duplication

    Scott Bortz
    Scott Bortz Jul 17, 2012 11:41 AM

      Can Tableau integrate fact tables at different levels of detail (with some shared dimensionality) into the same data connection without duplicating the data?

       

      I am able to combine the 2 fact tables very easily with a join on the shared dimensionality, however the join results in data duplication as the header fact data is cloned to match the record count of the detail fact data.

       

      Is there any way to avoid this besides data blending?  I'm working with a large data set on a live connection.  I do not want to fire to independent queries to be synchronized in the Tableau engine.  Is it possible to fire only 1 query and correct the duplication somehow?

        • 1. Re: Multiple Tables Connection Including Multiple Fact Tables Results in Data Duplication
          Robert Morton
          Robert Morton Employee Jul 17, 2012 2:23 PM (in response to Scott Bortz)

          Hi Scott,

           

          You point out that Data Blending is one approach to avoiding this problem, which is correct. Data Blending will query each data source independently and aggregate them to have the same dimensionality (level of detail) prior to joining them. Since you wish to avoid Data Blending, you will need to mimic its approach to pre-aggregating the fact tables to some common dimensionality. You can accomplish this with a Custom SQL query which uses separate, named subqueries (aka 'derived table') for each fact table and joins them in the outer query.

           

          The drawback to this approach is twofold: the custom SQL query may perform worse than having two separate Data Blending queries; and the custom SQL connection will not have the same flexibility as Data Blending when it comes to automatically adjusting to changes in your visualization level of detail.

           

          On a final note, I'll point out that there's no easy recipe for poor performance and poor table organization. Joining two fact tables is simply an incorrect thing to do unless you can manage the level of detail problem. Data Blending helps with this to some degree, but if your system has poor performance in addition to poor table design then there's not much more that Tableau could do to help you.

           

          -Robert