-
1. Re: Multiple Tables Connection Including Multiple Fact Tables Results in Data Duplication
Robert MortonJul 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