12 Replies Latest reply on Dec 23, 2014 2:18 AM by Rekha Bathla

    Create Hierarchy from fields of 2 different data sources

    Siddharth Surana

      Hey Tab Gurus

       

      I wanted to know if it is possible for us to create a hierarchy (for drill down purposes) using fields form 2 different data sources.

       

      Currently I am using Text files and because my data source has more than 255 columns (400 Columns) and as there is a Jet engine limitation, I had to split the tables into two.

       

      And now I have 2 different tables and just realized that Tableau does not allow me to use a field from my first table and add that to a hierarchy in a second table.

       

      Do any of you have a workaround for this?

       

      Thanks -Sid

        • 1. Re: Create Hierarchy from fields of 2 different data sources
          Siddharth Surana

          Thanks for your reply Shawn. Is this the right place to post these questions?

           

          -Sid

          • 2. Re: Create Hierarchy from fields of 2 different data sources
            Tracy Rodgers

            Hi Siddharth,

             

            Instead of blending the data sources, use a join. Then, you'll be able to create a hierarchy.

             

            -Tracy

            • 3. Re: Create Hierarchy from fields of 2 different data sources
              Siddharth Surana

              Hi Tracy

               

              I am not currently blending the data sources. I bring them in as two separate data sources and create relationships (Joins) within Tableau. Even then it does not allow me to create a hierarchy pulling in fields from both the sources.

               

              Also, just to mention, I am not creating hierarchies using the fields that are used for joining the different data sources. If you see the workbook I have posted, that is exactly how I create joins and bring in the data sources.

               

              Any comments on how this can be done? I do not intend to use actions between two sheets on a dashboard to achieve this. Just plain simple drill downs


              Thanks

              -Sid

              • 4. Re: Create Hierarchy from fields of 2 different data sources
                Jonathan Drummey

                Hi Sid,

                 

                Your terminology is a little confusing...A Tableau data blend is like a left join, but is not a true left join because it will not increase the level of detail in the primary. Dimensions from the primary are brought in as is. All fields from secondary sources are aggregated at the level of detail specified by the blending fields in use for that worksheet. Even if you bring in a dimension from the secondary, under the hood Tableau is aggregating it and the secondary dimension will show asterisks if the view is not at a sufficient level of detail. Beyond that limitation, Tableau does not support building a hierarchy by dragging and dropping fields from one source to another.

                 

                There are a few workarounds:

                - One (probably non-satisfactory) answer is that you are trying to use a hammer (text/Excel files and MS JET) when you need a more advanced tool that can handle more than 255 columns at a time.

                - Another is to build a scaffold data source that has the necessary level of detail for both the data sources (the scaffold could be completely dynamically constructed via custom SQL, a query, or maybe even a Multiple Tables connection), have the hierarchy in that data source, then blend the secondary sources to the scaffold.

                - You could also alter your current primary to have the level of detail, for example by setting up a Multiple Tables connection in Tableau that joins on the primary to add the necessary columns from your other data source. (Since you are using text files as data sources, Tableau will let us join them together so long as they are in the same folder). This may require some mucking around with how you aggregate the measures in the primary. For that reason, I'd tend to go towards constructing a scaffold source if I couldn't use a more robust data tool.

                 

                Jonathan

                 


                1 of 1 people found this helpful
                • 5. Re: Create Hierarchy from fields of 2 different data sources
                  Siddharth Surana

                  Hi Jonathan

                   

                  So yes I believe that my terminology got a bit confusing up there. Just to clarify on what I do currently and what I intend to do.

                   

                  I am currently bringing in data sources (.tab files) saved in different folders (If I save them all in the same folder, the jet engine does not recognize the schema.ini files). I bring them in separate and create joins by going to Data - Edit Relationships.

                   

                  I believe that the data sources have the same level of granularity. The data sources are not just in the same folder and hence the MULTIPLE TABLE option does not seem to work. Even after putting them together in the same folder, the text files stand separate and are not shown in the multiple tables option.

                   

                  Now since I cannot use the multiple table options , the Custom SQL is also off limit for me as the other .tab file is not recognized. (Am I wrong here?) (i.e, even after saving the text files in the same folder, Tableau does not recognize the two files in the Multiple Table option). So is there a way to create a scaffold data source?

                   

                  I use Hive currently, but because Tableau's Hive connector is not as good as we had expected (Speed troubles), hence we decided for the time being to export all hive tables into text files and create extracts from those text files and publish those on the Tableau Server. So we do use a more advanced warehouse, but limitations force us to go back to Jet Engine.

                   

                  Any help would be really appreciated.

                   

                  Thanks

                  -Sid

                  • 6. Re: Create Hierarchy from fields of 2 different data sources
                    Jonathan Drummey

                    One more bit of clarification: Data->Edit Relationships does not configure joins. What that does is specify potential blending dimensions, the actual blending dimensions in use will depend on what is turned on for linking fields in any given worksheet. (I think it's useful to *not* call what's happening in Tableau data blends as a "join", and instead use "linking fields" or "linking dimensions" because the way data blends work is specific to Tableau and anyone who has worked with joins comes in with a mental model of how joins work and can easily get confused).

                     

                    I haven't used schema.ini files, so I'm not sure what's going on there, and I don't understand why Tableau wouldn't be able to use the multiple tables option, that might be one for tech support.

                     

                    As for building a scaffold source, one option would be to build an additional export from Hive that has the dimensions you need for your hierarchy.

                     

                    Jonathan

                     

                     

                     


                    1 of 1 people found this helpful
                    • 7. Re: Create Hierarchy from fields of 2 different data sources
                      Siddharth Surana

                      So just to confirm if I understand this right.

                       

                      1, Data blending is, as you mentioned, using the Custom SQL / Multiple Table option while we are connecting to the data sources. This creates a Left Join as you mentioned previously, with the granularity of the primary data source.

                       

                      2. Linking fields is using Edit Relationships option within Tableau. This can be done after all the data sources are connected and we can do our Custom Linking as well.

                       

                      Are these the only 2 ways to create/ define a relationship / blend between data sources or is there another way to do so?

                       

                      Is this the extent of Joins within Tableau?

                       

                      Thanks Jonathan

                      -Sid

                      • 8. Re: Create Hierarchy from fields of 2 different data sources
                        Jonathan Drummey

                        I think there's still some confusion:

                         

                        - We can talk about the Tableau "data connection" and the underlying "data source" as separate things, but once we get to the point of dragging pills into the view they are roughly synonymous.

                         

                        - Tablea data blends are separate from Custom SQL/multiple tables option, which are part of the data connection. (more on that below):

                         

                        - When you connect to a data source, or go to edit a data connection for an existing data source, you can set up left/right/inner joins between tables/views/etc. using the Multiple Tables option, and can use Custom SQL to do whatever SQL the data source supports (such as if you want a cross product query in JET). There's the special case that I mentioned where multiple text files in the same folder can be treated as separate tables in the same data source (at least in some cases, I'm not sure what's going on in the situation you describe with the Hive exports). Once you've connected to a data source, Tableau generates a row in the Data window for that source.

                         

                        - Once you have two or more data sources in Tableau, then you can use Edit Relationships to view and customize the potential linking dimensions for data blends. Then in a given worksheet you can set up a blend by bringing fields from different data sources into the worksheet, and control the exact relationship for that worksheet by turning on or off linking dimensions in the secondary. Tableau will identify the primary data source with a blue checkmark icon, and the secondary source(s) with an orange checkmark.

                         

                        Does that make sense?

                         

                        Jonathan

                        • 9. Re: Create Hierarchy from fields of 2 different data sources
                          Siddharth Surana

                          Yes it does finally make sense. Though I have been using Tableau for a while now, I got my AHA moment with joins & blending just after I read your explanation.

                           

                          Appreciate your help Jonathan

                          -Sid

                          • 11. Re: Create Hierarchy from fields of 2 different data sources
                            Shob G

                            Hi Sid,

                             

                            could you create a hierarchy using 2 different data sources? Let me know, facing the same issue.

                            • 12. Re: Create Hierarchy from fields of 2 different data sources
                              Rekha Bathla

                              Hi Jonathan,

                              I have two data connections in report- one pointing to teradata and other to MS Access. I am using data blending to bring fields from two  sources into single view.

                              Is there any way in tableau to create hierarchy from fields of two different data sources , for drill down purpose.

                               

                              Thanks,

                              Rekha