5 Replies Latest reply on Jun 25, 2015 10:13 AM by Emmanuel Pagan

    many to many relationships

    Riyaz Mohammed

      We have data where a vendors, who can belong to one of many industry classifications, and their revenue.  I'm running into issues where a join between a bridge table that contains the vendors and their industry segments and the revenue table is causing the totals to be counted twice in Tableau.  So for example if a vendor belongs to the IT Industry and also the Services Industry and their total revenue was $500, Tableau will show their total revenue as $1,000.  Obviously because it is totaling the revenue column after the inner join.  What tips can be used to avoid this double counting issue?

        • 1. Re: many to many relationships
          Robert Royer

          At the heart of this, it sounds like a database design issue, not really a Tableau dilemma.

           

          Assuming you want to keep the Vendors having multiple industries, the only way to prevent the double counting is to take the "Industry" table off of your join. In the scenario you described, it doesn't sound like it is needed. Alternatively, if you moved the revenue numbers from the "Vendor" table to the "Industry" table, then you could see a breakdown of revenue per industry, per vendor, or both. This assumes you can get to that level of detail. If the DB was set up in this way, you should have any duplicating revenue problems in Tableau.

          • 2. Re: many to many relationships
            Riyaz Mohammed

            Thanks for your response Robert.  I can see how having the revenue level of detail down to the industry level can help alleviate this issue but unfortunately we only have the data at the company level and know what industry segments they belong to.  We definitely need to have both the vendor data and the industry data on the Viz.  The industry data is displayed as a quick filter and the moment when multiple industries are selected, the totals are off.  The only workaround I've found is to force the quick filter to be single select.  I was really hoping Tableau would be able to do what an SQL Analysis Server solution would do where it'd recognize a transaction was part of a many to many relationship and not double count it.

            • 3. Re: many to many relationships
              Robert Royer

              Riyaz,

              We've all been there in one way or another. There's normally some way to work around it. Would you mind attaching a screen shot or your dashboard as you wish it could be? (Resulting in the unwanted double counting). This may help trigger some more ideas.

              • 4. Re: many to many relationships
                Alex Kerin

                There may be some help in this thread: http://community.tableau.com/thread/122286

                • 5. Re: many to many relationships
                  Emmanuel Pagan

                  First I want to thank Alex Kerin, that link pointed me in the right direction although the information in the link didn't clearly spell out how to resolve the issue. I to have the same problem I have a Fact table with Premium which relates to a Dimenion Driver Group table. This relation is 1 to 1. This Dim Driver Group relates to a bridge table Dim Driver Bridge. This relation is 1 to M. The bridge table relates to the actual dimension Dim Driver. Similarly to Riyaz problem I have Premium that can relate to a group of drivers, the original data source doesn't split Premium by driver. The business stakeholders want to be able to filter using all of the drivers attributes and still have the premium related show up. I do not consider this a data format problem, and its nothing that can be fixed.

                  In SSAS its easier to configure M to M relationships with this data structure or at least the documentation available clearly outlines how to do so. To resolve this:

                   

                   

                  1. I created a data source called "Premium", in this data source I have the Fact table with a join to the DimDriverGroup.
                  2. Created a second data source called "Intermediate - Dim Drivers", in this data source I have DimDriverGroup as the primary table with a join to DimDriverBridge which then joins to DimDriver.
                  3. I switched to Sheet1, in the menu bar selected Data -> Edit Relationships. Changed relationship to Custom, then related the "Premium".DimDriverGroup.SK to "Intermediate - Dim Drivers".DimDriverGroup.SK. Clicked Ok. This specifies how the two data sources link to each other.
                  4. Above Dimensions I switched the Data Source to "Intermediate - Dim Drivers" so the Dimensions section shows me the Dims for that data source. I clicked the Link icon next to DimDriverGroup -> SK. This officially linked the two, seems like a redundant step don't know why Tableau required it.

                   

                  After doing this I was able to drag my measures and it did not double count. See the attached image the Column header with the "*" is displaying as such because there are multiple drivers associated to that premium. I'm not a Tableau expert, I do not know what performance implications this will cause with a very large data set but this is the best and only solution I was able to find.

                  Tableau_FactWithBridge.JPG

                   

                  I tried Window Sum methods and it was fluky when dragging in dimensional attributes into the result set. Grand totals would not aggregate correctly.