2 Replies Latest reply on Jun 21, 2013 4:24 AM by Robin Kennedy

    How to create hierarchy from parent-child relational database table?

    gosta.bostram

      Does Tableau support parent-child hierarchies? If so, how do I create a hierarchy from a relational database table? I have a dimension table which is parent-child instead of having a large snowflake and I'm therefore able to have facts on multiple levels in the same table instead of multiple fact tables with different grain. Other solutions are welcome as well.

       

      The main reason for this is a large set of non-additive goal measures for multiple dashboards while still wanting the actual measures for the same KPIs on more detailed levels. Example: KPI - NetSales, goal values per month for continent, country and county level, also another set of value for the organization levels, actual details for netsales is on day level and so on. Thus having a parent-child table for each dimension would let me put both actuals and goals in the same fact table, instead of having 20+ tables with different grains.

        • 1. Re: How to create hierarchy from parent-child relational database table?
          Siddharth Surana

          Hey Did you find a way to get this done? I am also trying to create a hierarchy from 2 data sources. Can we create a hierarchy using a field from one data source to another field from another data source?

          • 2. Re: How to create hierarchy from parent-child relational database table?
            Robin Kennedy

            I've been looking at this problem too.

             

            I have a single table with all the parent-child relationships in it -- all the way from level 1 down to level N, i.e. Level 1 to 2, level 2 to 3 ... level N to N+1 etc etc. In the table there is the ID, the Parent ID and the Member name. The levels are uneven - some items go down to level 3, some to level 6 etc.

             

            I ended up joining the table to itself N times (left join), using N.ID = N+1.ParentID. Then I filtered the first instance for only the top level. You can then alias the NULLs, or create calculated fields to bring in the parent name instead.

             

            Anyone else tackled this problem before? How is it done with other technologies? Recursive SQL?

             

            Would be interested to see what other options there are out there.

            1 of 1 people found this helpful