8 Replies Latest reply on Mar 17, 2017 2:09 PM by Mithil Mangalore

    How to handle Parent - Child Hierarchies

    Peter Schmitt

      Hello,

       

      im quiet new to tableau so please be patient for anything that i probably misunderstood in the tool itself

       

      We write a lot of reports and in almost every report we provide the user the ability to select a department unit where he can investigate all further objects relating to this dep.

      Since we work for a big company there are a lot of different units as well as a variety of depth in the organizational structure.

      All this is represented by mainly a recursive CTE which resolves the parent - child hierarchy.

      So the User selects an "parent" - organizational unit and automatically sees all the data for the other "child" - units. The units are self-referenced. Via an INNER JOIN we only get what we want..

       

      I tried the approach with views (which is basically our CTE) and filtering the data in my workspace-  unfortunately tableau isn't restricting the original dataset from my cte so the logic for the inner joins aren't working.

       

      I read a couple of discussions about this topic but none of them seemed to help me how to setup my DB (with stored procedures or views) or model the data in tableau.

      Can some of you guys try to explain how to properly deal with parent - child hierachies?

       

      Kind Regards,

      Peter

        • 1. Re: How to handle Parent - Child Hierarchies
          Mithil Mangalore

          Hi Peter,

           

          I would suggest getting the data at the lowest level of the hierarchy into tableau. Once it is in you can create a hierarchy inside Tableau. Creating the hierarchy will give users the ability to drill down to the lowest level of that hierarchy in steps. Also, you do not have to worry about defining the values at each level of the hierarchy, Tableau will automatically do it based on the values in the data.

          Refer to this link for more details: Drilling Down and Up in a Hierarchy

          Let me know if you have further questions. Happy to help.

           

          Thanks,

          Mithil

          1 of 1 people found this helpful
          • 2. Re: How to handle Parent - Child Hierarchies
            Peter Schmitt

            Hi Mithil,

             

            thanks a lot for the quick help. For my understanding hierarchies don't work with parent child relations

            So our data basically looks like this - since they're all stored under the same name column i can't create Tableau hierarchies.

             

            example.PNG

             

            My guess is that i have to remodel the data to something flat that is comprehensible to tableau.

            Since we have a variety of depth i'm not fond to create fixed views rather than using recursive CTEs.

            But im lacking the knowledge about whats the best way to do this..

             

            Kind regards,

            Peter

            1 of 1 people found this helpful
            • 3. Re: How to handle Parent - Child Hierarchies
              Jim Dehner

              Good morning Peter

               

              Not totally certain how your data is structured but Tableau does support hierarchies

               

              Using the superstore dataset see the the images below

              I started by breaking the "Product Hierarchy" and then put the Category and the sub-category pills on the rows shelf as individual pills -

              To recreate the Product Hierarchy I dragged the Sub-Category dimension name over the Category name and created the Category,Subcategory hierarchy (Just rename it to anything you want

              Then dragging the Category dimension to the rows shelf brings the hierarchy to rows (note the +/- drill down in the pill)

              Drilling down to sub-category produces the same viz as the first image

              You can create the hierarchy effect in either way - dragging the child over the parent in the dimension area of the left column or by placing them individdually on the vis

               

              Does any of that help?

              Jim

              1 of 1 people found this helpful
              • 4. Re: How to handle Parent - Child Hierarchies
                Peter Schmitt

                Hey Jim, thanks so much for that elaborate reply..

                 

                my data doesn't allow me to create hierachies like in your example, since they are all stored in the same attribute (e.g. name) but have relations to parents. They're stored together in one table and inside of this table are referencing to each other.. i will try to upload a screenshot

                • 5. Re: How to handle Parent - Child Hierarchies
                  Mithil Mangalore

                  Hi Peter,

                   

                  Thank you for clarifying. If you knew the depth, you could use self joins inside tableau to resolve this. Since, the depth of this tree itself is dynamic, you will have to flatten the hierarchy outside Tableau. Unfortunately, Tableau does not have the functionality to deal with such scenario. You might want to use a query with something like connect by prior to flatten it out before getting the data into Tableau.

                  I hope this helps.

                   

                  Thanks,

                  Mithil

                  2 of 2 people found this helpful
                  • 6. Re: How to handle Parent - Child Hierarchies
                    Peter Schmitt

                    Hi Mithil,

                     

                    i'm googling my way through options how to approach this scenario.. After my success i will keep you updated about what's the best way to resolve this =)

                     

                    Thanks already for your helpful answers!

                    • 7. Re: How to handle Parent - Child Hierarchies
                      Jim Dehner

                      Thanks Peter

                      I like Mithil's comments above - did not realize that this could be the case

                       

                      Glad you were able to get a direction

                      Jim

                      • 8. Re: How to handle Parent - Child Hierarchies
                        Mithil Mangalore

                        Thank you Peter. Do let us know if you find something.

                        You can mark the question query as answered so that it helps others with a similar question.

                         

                        Thanks,

                        Mithil