4 Replies Latest reply on Mar 13, 2012 5:10 PM by Nathaniel Fitzgerald-Hood

    Dynamic Hierarchy

      We have a very Dynamic Hierarchy (sample example attached). The hierarchy is stored in the below format where the Column ParentId stores the Id of their Parent Member to which it reports to and the Sales is only present in the Lowest Level as shown in the Screen Shot below in Fig 1.

       

      ID
      Name
      Parent_Id
      Sales
      1KLZ_Group

      2Airline1
      3Engg250
      4Com240
      5DN2
      6Travel520
      7Handling530


      Fig 1

       

      To explain this more clearly, below is the structure of the same hierarchy how Tableau understands.

       

      Level 1
      Level 2
      Level 3Level 4Sales
      KLZ_Group

      Airline

      Engg
      40
      KLZ_GroupAirlineCom
      50
      KLZ_GroupAirlineDNTravel20
      KLZ_GroupAirlineDNHandling30



       

      Fig 2


       

      The number of Levels could be very Dynamic, as shown above the Row 1 and Row 2 has only 3 Levels where as the Row 3 and Row 4 has 4 levels and this could increase or decrease.

      Given that I have got the Datasource as shown in Fig 1 is there any way Tableau can decode this Dynamic Hierarchy?

      In Oracle we can use the below inbuilt command in our query to decode this hierarchy.

      select id,name,parent_id

      from <table>

      connect by prior id=<parent_id>

      start with id=1

       

      Whats the best way in Tableau to solve the above query?

        • 1. Re: Dynamic Hierarchy
          Benoit Pigeon

          Hi

          Sorry not to answer to your question but I have the same trouble way before you...

          When you say that Tableau understand the hierarchy, I can't have this.

          Here is my trouble http://community.tableau.com/thread/116826

          Can you give me a workbooksample working ?

          Thanks !

          • 2. Re: Dynamic Hierarchy

            Hi,

            I have not found a solution to my problem. Do you want me to send you the excel Datasource?

            • 3. Re: Dynamic Hierarchy
              Benoit Pigeon

              I have exactly the same problem as you do...

              Hope that someone can help us or we'll have to change the datamodel...

              • 4. Re: Dynamic Hierarchy
                Nathaniel Fitzgerald-Hood

                Hi,

                 

                I've used two database based approaches for solving this for mapping the hierarchy of our organisation. Both have limitations but overall they work. Alas neither makes use of Oracle's connect by or SQL Server's recursive queries so they're not totally dynamic.

                 

                The first forces all groups/units to have the same depth by adding "dummy" elements. This makes things neat but overall just gets messy and then you have phantom groups in there.

                 

                I've converted over to a solution where I just flatten the query results to 7 levels. That's the limitation here - it's a manual depth you can go to. In our case the organisation chart only goes to 7 levels so I know I'm good. And, in reality 7 is a lot on any viz - I usually display subsets of the hierarchy. Here's a smaller example of the query.

                 

                SELECT lvl1.OrgName AS lvl1

                     , lvl2.OrgName AS lvl2

                     , lvl3.OrgName AS lvl3

                     , lvl4.OrgName AS lvl4

                     , lvl5.OrgName AS lvl5

                     , COALESCE(lvl5.Sales, lvl4.Sales, lvl3.Sales, lvl2.Sales, lvl1.Sales) as Sales

                  FROM fact_org_hierarchy AS lvl1

                LEFT OUTER

                  JOIN organisation AS lvl2

                    ON lvl2.parent_id = lvl1.id

                LEFT OUTER

                  JOIN organisation AS lvl3

                    ON lvl3.parent_id = lvl2.id

                LEFT OUTER

                  JOIN organisation AS lvl4

                    ON lvl4.parent_id = lvl3.id

                LEFT OUTER

                  JOIN organisation AS lvl5

                    ON lvl5.parent_id = lvl4.id 

                WHERE lvl1.parent_id IS NULL

                ORDER

                    BY lvl1.OrgName

                     , lvl2.OrgName

                     , lvl3.OrgName

                     , lvl4.OrgName

                     , lvl5.OrgName

                 

                If you apply this to your dataset you'll end up with rows like:

                 

                Name1Name2Name3Name4Sales
                KLZ GroupAirlineEng 50
                KLZ GroupAirlineCom 40
                KLZ GroupAirlineDNTravel20
                KLZ GroupAirlineDNHandling30

                 

                The best way I've found of using this is to split your hierarchy away from your measure data (you're likely to have a lot of various measures).

                 

                Make the flattened hierarchy into a view and just join that to your sales information to give it a hierarchy.

                 

                A field with

                COALESCE(lvl5.id, lvl4.id, lvl3.id, lvl2.id, lvl1.id) as currentItem

                in the view will give you an easy key to join the sales.org_id to.

                 

                The big definiciency in all this is the lack of a truly dynamic hierarchy, but so far this is what's worked for me. I wish Tableau's display handling of NULLs in dimensions was better. Most of the time they show as NULL but I think you can alias that with a space and make them look blank. I haven't tested that fully though.

                 

                Give it a whirl and see if it does the business.