3 Replies Latest reply on Jan 14, 2019 4:24 AM by Tony Daw

    Designing/Handling Ragged Hierarchical Dimensions in Relational Data Source

    James Morse

      Hi All --


      I am leading up an organizational effort to use Tableau Desktop and Server as our primary source for reporting and analytics in the enterprise.


      Right now we are going through the process of creating data connections to ultimately be housed in Tableau Data Server.  From the finance/accounting function, there is a lot of concern about ragged hierarchical trees that are mastered in PeopleSoft, and how they will be used and keep in sync once we begin utilizing Tableau and the related data connections.


      The finance/accounting team had been using multi-dimensional data sources like Hyperion/Essbase previously, but we want to move away from cubes and utilize a standard relational data warehouse/mart in Oracle going forward.


      For example, we have a "Department" tree, which layers different levels of functional areas.  Here's a simplified ragged tree example below...


      + Sales & Marketing (Top Level)

        + Sales (Level 2)

          + Inbound Sales (Level 3)

          + Outbound Sales (Level 3)

          + Sales Support (Level 3)

          + Sales Operations (Level 3)

        + Marketing (Level 2)

      + Operations (Top Level)

        + Core Ops (Level 2)

        + Specialty Ops (Level 2)

      + Technology (Top Level)

      + Infrastructure (Level 2)

      + Application Development (Level 2)

      + HR

      + Benefits & Compensation

      + Recruitment

      + Development


      I've simplified this quite a bit, but Sales & Marketing has 3 levels while the others only have 2.


      The second part of the problem is ensuring that we have ways of handling changes to the hierarchy such that we could support historical views of the hierarchy as well as the current tree structure.


      My open-ended question is how we might structure our Oracle data warehouse SQL scripts that feed Tableau data connections such that we can drill, slice, and/or filter on ragged hierarchical trees.  My best thought so far would be to have a dimension in my data connection for the tree which is a delimited string representing the full path of the tree down to the lowest level of detail.  Then, I could create parsed, calculated fields for levels 1 through n, with a finite number of these calculated fields to handle the deepest realistic tree scenario.  For each hierarchical dimension, we'd need these level-based calculated fields for both the current state and the historical state.


      Does this seem like a sound approach?  What are some other possibilities?