11 Replies Latest reply on May 30, 2017 1:16 AM by Simon Runc

    Data Blending Architecture

    Bharadwaj Munjuluri

      I was trying to understand Data Blending concept of Tableau and kinda stuck at one place. Please help me get through:

       

      Here is what I think I understood:

      1. Data Blending works similar to Left Join in Relational model, except that the left table (so called Primary Source in Tableau) will not expand as per the right table.

      2. Number of Rows in the left table will remain as is after the data is blend.

      3. It is better to have a one-one mapping of the fields in secondary source to avoid * (asterick) signs.

       

      My Scenario:

      I prepared a sample set of data (2 excel files) and tried different options on Tableau to understand how is data actually blend [sample below]

       

      I created Relationship link on ORIGINAL_CCY and INOUT fields of the PRIMARY. My expectation of how the data would look after the blend is attached as an image. Please correct me if that is wrong.

      Now, in tableau, I tried many options and was able to figure how a scenario is behaving.

       

      Question:

      At a point when I was trying to filter by TRANSACTION = 5 [from PRIMARY source] , I see some columns of SECONDARY source are displayed too and I don't quite understand why?

      How is the blended table actually behaving? What is it's design after it is build.

       

      Please look at the images showing the excel format of blended data and tableau output.Tableau Blended Data.jpegTableau Workbook.jpegTableau Filtered Data.jpeg

       

      PRIMARY

      ORIGINAL_CCYAMOUNTTRANSACTION
      INOUT
      SGD$69.81I
      USD$15.315I
      SGD$59.862O
      USD$15.315I
      USD$15.317I
      USD$15.316O
      SGD$69.863I
      SGD$69.862O
      SGD$69.865I

      SECONDARY

      TRANSACTION NUMBER
      ORIGINAL_CCYNAME
      INOUTAMOUNT
      2SGDMYSELFI$45
      1USDMEI$15.12
      4USDMINEO$48.12
      3USDIO$456.31
      5SGDMYI$78.54
        • 1. Re: Data Blending Architecture
          Tom W

          Hi,

          It would be helpful if you could provide your sample as a Tableau Packaged Workbook instead - Packaged workbooks: when, why, how

          • 2. Re: Data Blending Architecture
            Bharadwaj Munjuluri

            Err.. It would be a while before I get to post a twbx as the Software and Internet do not sit in same computer (hence the images & the manually typed tables).

            All I want to know is how do I picture a Data Blend with the given two tables and blending on two columns - ORIGINAL_CCY & INOUT.

            Any pointers would help?

            • 3. Re: Data Blending Architecture
              Prayson Wilfred Daniel

              Tableau has a very good documentation that I found useful:Multiple Connection

              • 5. Re: Data Blending Architecture
                Ivan Young

                Hi Bharadwaj,

                I think the confusion lies in the fact that Tableau aggregates the data in the secondary source prior to blending or the left join.  The aggregation is at the level of the blended fields, in this case ORIGINAL_CCY & INOUT.  Additionally the filter for transaction will be ignored in the secondary data source as there is no relationship with the primary.

                 

                Basically your secondary data source will look like the table below prior to being joined with the primary. The first and second rows will be joined even when you filter on Transaction #5 which seems to match your output.  What was the result you were expecting?

                 

                Regards,

                Ivan

                 

                    

                Transaction#NameCurrencyInOutAmount
                **SGDI123.54
                1MEUSDI$15.12
                **USDO519.55
                1 of 1 people found this helpful
                • 6. Re: Data Blending Architecture
                  SANDIP SHARMA

                  HI Munjuluri,

                   

                  First of I would like to understand, why you want to use data blending where as you can easily Union All this data in Tableau. To achieve correct output of the Union All, you have to keep the name of your columns same and order of those columns also should be same.

                   

                  If for understanding purpose, you can go with Data blending as well

                   

                   

                  Data Blending output: Yes you are correct it will behave like left join only for Primary Data Source.

                   

                  I have one Question in your third Excel screen shot you take filter on transaction 5 and there is a record under SGD with $69 but in your actual data which is given as a example does not have any value for SGD under transaction 5.

                   

                  I tried the same at my end my result is 100% correct in blending and Union all both.

                   

                   

                  I could not understand where is the problem.

                  • 7. Re: Data Blending Architecture
                    Bharadwaj Munjuluri

                    Thanks Prayson. This was the document I referred to initially and got the knowledge that I posted initially. But this, I am afraid, does not talk on how would filters or aggregates behave the way they do.

                    I was under an impression that once the data is blend, there is a new table (just like when we join two tables in relational model) where I apply a filter on any one column and it would apply all through the joined table.

                    Now it appears that it is not the case.

                    From what @Ivan Young mentioned in the comments below, I picture it like below. Which means:

                    1. Data Blend does not create a new table altogether but creates a link between Primary & Secondary data sources.

                    2. Because of this, if a non-linked column of Primary source is filtered, it does not effect the output of Secondary source.

                    3. A filter applied on the linked columns of Primary & Secondary (in our case - ORIGINAL_CCY & INOUT) will effect both Primary & Secondary.

                     

                    I hope my understanding is correct. Thanks for sharing the thoughts. I will test this soon and share more confusions as they come by

                     

                    DataBlendDesign.png

                    • 8. Re: Data Blending Architecture
                      Bharadwaj Munjuluri

                      Perfect! That was one detailed explanation.

                      Can I suggest:

                      1. Make a video

                      2. Use small set of data for explaining.

                       

                      Also, It took me a lot of time to figure out why you used MIN(POPULATION) when using the join. Because, I did not had a picture of how the data looks after the tables were joined. Same is the case with Data Blend design.

                      • 9. Re: Data Blending Architecture
                        Bharadwaj Munjuluri

                        Sandip, I was trying to understand how Data Blend works.The problem was the level of understanding of the concept of Data blending.

                        Secondly, unions would not work if datasources are from different sources. Like 2 WebDataConnectors or (1 Sybase, 1 Oracle database).

                        Thanks for trying

                        • 10. Re: Data Blending Architecture
                          SANDIP SHARMA

                          IF it is so then you can take joins instead blending. Because in case of heavy data set blending takes time to respond. Any ways you got your answer is most important.

                          • 11. Re: Data Blending Architecture
                            Simon Runc

                            Hi Bharadwaj,

                            Can I suggest:

                            1. Make a video

                            2. Use small set of data for explaining.

                            ...thanks for the feedback, and both good ideas. The article was originally focused on data-granularity, that (sort-of) morphed into a bit about data blending! The key takeouts are that Blending is an Aggregate and Join, and the Aggregates and Join level (aka fields used to create aggregates and join to primary) are determined by which levels are defined (by selecting the little orange blend icons...which are also auto-selected by Tableau if those related fields are in the canvas Level of Detail)...It also acts more like an inner join (if I filter to a region, say using the example from my blog, from the secondary datasource, the entire view gets filtered to only that year.

                             

                             

                            I'll add a dedicated data blend blog (and video) to my "things to blog about list"...it used to be that data blending was used where data resided in different data-sources, but now we can join across data-sourced that one has really gone away, so it's main use case now if where the grain of the 2 data-sources is different (or it can be used to do a few tricks!).