8 Replies Latest reply on May 15, 2013 10:05 AM by Jonathan Shaltz

    Can I use a "multiple tables" data source when the tables have a one-to-many relationship?

    Jonathan Shaltz

      I'm sure this question has already been asked, but I could not find it in my Google and forum searches.  I must be using the wrong keywords.

       

      I have a SQL warehouse with an Orders table and a Customers table, with a one-to-many relationship between them.  When I pull this into Tableau using a "multiple tables" source, it looks like Tableau is performing a simple join between the tables, "flattening" the output:

       

      Customer IDCountryCustomersOrder IDQuantity
      123U.S.17891
      123U.S.18902
      456U.K.19013

       

      This seems to be the case because I see over-counting of fields from the Customers table.  For example, the Customers table includes a Customers field, which is "1" for every record.  So if I query "Customer ID" and "Sum(Customers)" from the Customers table alone, I correctly get "1" for the calculation in every record.  When I query this in my Tableau file with the multiple table source, I instead get higher values, apparently because Tableau has expanded the customer data to appear in each order record.  In the sample above, I'd get "3" though there are in fact just two customers.

       

      Is there a way to avoid this problem?  In QlikView, from which I'm migrating, the app totals metrics separately from each table before presenting the results.

       

      Note that while a distinct count would address the issue of getting the number of customers, my real question is how to prevent flattening in the first place.  Customer counts are just a simple example.

       

      My understanding is that data blending may resolve this, however support tells me that it's limited to 300 K unique values in the join field, which obviously doesn't work with atomic data, for any but the smallest businesses.

       

      Any ideas?  Hopefully this is a simple thing for which experienced Tableau users long since developed workarounds.  Thank you!

       

      - Jonathan