6 Replies Latest reply on Jul 11, 2013 10:07 AM by Jonathan Drummey Branched to a new discussion.

    How to do Data blending with 3 common fields and 2 data sources?

    viraj gholap

      Hi,

       

      I need to use 2 data sources and blend 2 tables with 3 common dimensions. PFA the sample data and worksheet. When I link the data based on client_id, mktr and TP_PC_Date then my graph (in Combination tab) shows nothing. If i use only date as common field, then the graph looks good.

       

      Any help is appreciated! Thanks

       

      Regards,

      Viraj

        • 2. Re: How to do Data blending with 3 common fields and 2 data sources?
          Jonathan Drummey

          It looks like what you want is to UNION the two data sources together instead of blending one to another. A Tableau data blend is a variation on a left join that starts with linking dimension(s) from the primary, all fields from secondary source(s) are aggregated to the level of those linking dimension(s). In other words, a data blend will not increase the level of detail in the primary. In your case, the primary does not have all combinations of those fields available, so as you turn on the linking dimensions there is less and less matching data from the secondary.

           

          There are a variety of techniques available to union the data together:

          - copy and paste the data into the same Excel spreadsheet

          - copy the data onto two worksheets in the same workbook, then use Tableau's custom SQL to UNION the data together

          - start with one workbook, then use Excel's linked worksheets capability to link the worksheet from other workbook into the first workbook, then use Tableau's custom SQL to union the data together

          - build a scaffold data source that has all of the unique combinations of linking dimensions and then blend the two data sources to that.

           

          Jonathan

          1 of 1 people found this helpful
          • 3. Re: How to do Data blending with 3 common fields and 2 data sources?
            viraj gholap

            Hi Jonathan,

             

            Thank you for the reply. It is helpful but I got more queries.

             

            1. I have used excel as sample. Actual data resides in 2 different databases and volume is 33 million in db 1 and 18 million in db 2. Therefore, I can not use excel in actual scenario.

            2. You are right. I need to union two data sources. How can I do it when I need to get data from 2 different data sources as mentioned in point 1?

            3. Scaffold data source is not a possible solution since I have at least 5 common fields and data for 2 years. I need ability to plot graph with granularity of Daily on time axis. Meaning, the scaffold data source will have huge number of combinations to maintain. And our data is refreshed everyday midnight and we can not maintain scaffold data source.

             

            Waiting for your inputs!

             

            Regards,

            Viraj

            • 4. Re: How to do Data blending with 3 common fields and 2 data sources?
              Jonathan Drummey

              No matter what, you’re going to need to do some federation of your databases. You didn’t say what kind of databases you were using, I know that Oracle and SQL Server each have the ability to set up linked tables that point to other databases, you can use that to generate your UNION query or a scaffold solution.

               

              The scaffold solution can be completely dynamic, it’s just a matter of getting access to all the values and then writing some queries that do the appropriate combination of unions, left joins, and/or cross products. Tableau can handle billions and billions of records, so the huge number of combinations is not a problem. However, since you have to go through the process of creating a federated database, the UNION query there would be easier to set up and maintain.

               

              Jonathan

              • 5. Re: How to do Data blending with 3 common fields and 2 data sources?
                viraj gholap

                One is Netezza and other is Oracle. Therefore, the option of UNION does not seem possible.

                 

                Thus, am I left with putting both tables in same DB and doing this Union on DB/DW side?

                 

                Also just on the sidenote, we are using Qlikview and it provides ability to concatenate or union two tables from different data sources during scripting. As I understand there is no such scripting or concatenate ability in Tableau. Am I rt?

                • 6. Re: How to do Data blending with 3 common fields and 2 data sources?
                  Jonathan Drummey

                  I did a quick lookup online and there are Netezza ODBC drivers, so you can definitely create the federated system approach and do the UNION on the Oracle side, I don't know about going the other direction.

                   

                  I've done a couple of evals of Qlikview, the ability to UNION multiple tables from multiple data sources is one of the QV features that I wish Tableau had. I know it's "on the list" of potential development items, but don't know if/when it might be released. I thought there was an Idea out there for it, but can't find it. There might be something in Johan's Ideas Collections.

                   

                  Jonathan