2 Replies Latest reply on Feb 3, 2017 11:00 AM by Steph Swierenga

    Blending vs joins for data of different granularity - not sure why.

    Steph Swierenga

      hey, the Tableau docs say this:


      Data is at different levels of detail.

      Sometimes one data set captures data using greater or lesser granularity than the other data set.

      For example, suppose you are analyzing transactional data and quota data. Transactional data might capture all transactions. However, quota data might aggregate transactions at the quarter level. Because the transactional values are captured at different levels of detail in each data set, you should use data blending to combine the data.



      i'm not really sure why blending is an advantage here. If table A contains transactions at the day level, and table B contains quotas rolled up to month, it seems like joins and blending would achieve the same result. I can see that blending allows for some flexibility for defining the join behavior, i.e., i can join on a date part, say 'month', where the cross-database join would only let me join on the full date (if that's all i had). There's an easy work-around for that. Is this the only advantage blending has over joins?



        • 1. Re: Blending vs joins for data of different granularity - not sure why.
          David Li

          Hi Steph, in most cases, you can eventually achieve the same result with blending and joins, but the solutions where blending is viable tend to be much simpler. For instance, consider your two tables of transactions (daily) and quotas (monthly). You can join these so that all transactions in a month have the same monthly quotas, but when you want to start summing up the monthly quotas, you end up needing to do some more complicated stuff, like using LOD calculations, just to get basic values. Not only that, but someone who isn't paying attention may accidentally use duplicated data because the join is asymmetric in granularity.


          Blending may also be less resource-intensive than joins if you end up with some kind of cross-join that multiplies the record count substantially.

          1 of 1 people found this helpful
          • 2. Re: Blending vs joins for data of different granularity - not sure why.
            Steph Swierenga

            Hey, thanks Li, this clears it up for me.  i did some experimenting.  I can see the effect of this in the sub-total calculations. I was under the mistaken impression that Tableau simply did an inner join internally once it received the aggregated sets form each data source - effectively causing the same duplication of higher granularity values to the lower level rows, which it does, but then happily filters them back out for the sub-totals.  I was able to create the LOD calc when using the x-database join which also worked for the sub-totals, but i agree with you, just adds extra complexity for this particular example.

            thanks, steph.