multi-parent hierarchy propagation in SQL/DAX

Question

Suppose I have a table that describes primary and secondary reporting lines for each member of staff. Let's imagine an organisational structure where the CEO, employee 0, has 2 managers (1 and 2) reporting to him.

Manager 2 has 2 staff in her team (3 and 4), however staff member 4 actually works in Manager 1's timezone, so while he has 2 as his primary report, he also reports to Manager 1 as a Secondary report so that 1 can fullfil normal fiduciary managerial obligations (provide support, etc.).

In addition to taking a secondary management role for employee 4, Manager 2 also has a team member reporting to him (5).

Edit: To illustrate the multi-parent problem, let's give team member 4 an intern, staff member 6. Team member 6 is now the subordinate of both managers 1 and 2 - the latter being inherited through the secondary reporting line.

The organisational structure would look like this:

+--+-------+---------+
|ID|Primary|Secondary|
|0 |NULL   |NULL     |
|1 |0      |NULL     |
|2 |0      |NULL     |
|3 |1      |NULL     |
|4 |1      |2        |
|5 |2      |NULL     |
|6 |4      |NULL     |
+--+-------+---------+

Now I want to expand this into a SQL view that gives me a list of people below any given staff member, covering both primary and secondary reports. So for staff member 2 (the manager with a primary and secondary report), I would expect to see team members 4 and 5, and for the CEO (0) I'd expect to see ever staff member other than the CEO. Our new intern, 6, is the subordinate of the CEO, managers 1 and 2, as well as his direct manager, 4.

This would look like this:

+--+-----------+
|ID|Subordinate|
|0 |1          |
|0 |2          |
|0 |3          |
|0 |4          |
|0 |5          |
|0 |6          |
|1 |3          |
|1 |4          |
|1 |6          |
|2 |4          |
|2 |5          |
|2 |6          |
|4 |6          |
+--+-----------+

How would I achieve this in SQL? I'm thinking some kind of OUTER APPLY operation on the ID but I'm struggling to get my head around the reentrancy that would be required (I think) to solve this. My background is in procedural programming, which I think is part of the reason I'm struggling here.

NB: An obvious question that I'd like to anticipate here is "Surely this is an XY problem - why on earth would you want to do this?"

I want to use row-level security in PowerBI to give each staff member access to certain information about individuals below them in the organisational structure. Unfortunately RLS doesn't permit the execution of stored procedures per individual, so I'm stuck with doing this combinatorial expansion and then simply filtering the above table based on the login.

Having said that, I'm open to better ways of approaching this problem.

Answer 1

This is pretty easily solved using the Parent-Child Hierarchy functions in DAX. I don't think you need to build any extra tables, just stick the following conditions in your RLS rules:

For Employee N, you just need to check if

PATHCONTAINS(PATH('Hierarchy'[ID], 'Hierarchy'[Primary]), N)

or

PATHCONTAINS(PATH('Hierarchy'[ID], 'Hierarchy'[Secondary]), N)

Note that this allows Employee N to see themselves as well as their subordinates, but you can add an extra condition if you don't want that.

---

Edit: When your structure is not a tree, the problem becomes more difficult. Here's an approach that should work.

For each ID, find the subordinates to get Level1, search Level1 for the next level of subordinates, and so forth until no subordinates exist. (If you have a loop in your structure that returns you to a higher level, then you'll get stuck in recursion.)

In this case, there are three levels below the top so we need three steps.

| ID | Primary | Secondary | Level1 | Level2 | Level3 |
|----|---------|-----------|--------|--------|--------|
| 0  |         |           | 1      | 4      | 6      |
| 0  |         |           | 2      | 4      | 6      |
| 0  |         |           | 2      | 5      |        |
| 0  |         |           | 3      |        |        |
| 1  | 0       |           | 4      | 6      |        |
| 2  | 0       |           | 4      | 6      |        |
| 2  | 0       |           | 5      |        |        |
| 3  | 0       |           |        |        |        |
| 4  | 1       | 2         | 6      |        |        |
| 5  | 2       |           |        |        |        |
| 6  | 4       |           |        |        |        |

Here's the M code to do this in the Power Query Editor:

let
    Source = Table.FromRows({{0,null,null},{1,0,null},{2,0,null},{3,0,null},{4,1,2},{5,2,null},{6,4,null}},{"ID", "Primary", "Secondary"}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Primary", Int64.Type}, {"Secondary", Int64.Type}}),
    SearchNextLevel = ExpandNext(ExpandNext(ExpandNext(#"Changed Type", "Level1", "ID"), "Level2", "Level1"), "Level3", "Level2"),
    #"Appended Query" =
        Table.Combine(
            {Table.RenameColumns(Table.SelectColumns(SearchNextLevel, {"ID", "Level1"}), {"Level1","Subordinate"}),
             Table.RenameColumns(Table.SelectColumns(SearchNextLevel, {"ID", "Level2"}), {"Level2","Subordinate"}),
             Table.RenameColumns(Table.SelectColumns(SearchNextLevel, {"ID", "Level3"}), {"Level3","Subordinate"})}
        ),
    #"Filtered Rows" = Table.SelectRows(#"Appended Query", each ([Subordinate] <> null)),
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows"),
    #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"ID", Order.Ascending}, {"Subordinate", Order.Ascending}})
in
    #"Sorted Rows"

Here's the custom function that's used multiple times to expand to the next level:

let
    ExpandToNextLevel = (T as table, NextLevel as text, ThisLevel as text) as table =>
    let
        SearchNextLevel =
        Table.AddColumn(T,
            NextLevel,
            (C) =>
                Table.SelectRows(
                    T, each Record.Field(C, ThisLevel) <> null and
                       ([Primary] = Record.Field(C, ThisLevel) or
                        [Secondary] = Record.Field(C, ThisLevel))
                    )[ID]
        ),
        ExpandColumn = Table.ExpandListColumn(SearchNextLevel, NextLevel)
    in
        ExpandColumn
in
    ExpandToNextLevel

To make this general, I obviously need to put the expanding and appending into a recursive loop. I'll come back to this as time permits.

---

Edit: Here's a recursive version of the query which uses unpivoting instead of appending.

let
    Source = Table.FromRows({{0,null,null},{1,0,null},{2,0,null},{3,0,null},{4,1,2},{5,2,null},{6,4,null}},{"ID", "Primary", "Secondary"}),
    #"Changed Types" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Primary", Int64.Type}, {"Secondary", Int64.Type}}),
    IDCount = List.Count(List.Distinct(#"Changed Types"[ID])),
    RecursiveExpand = List.Generate(
        () => [i=0, InputTable = #"Changed Types"],
        each [i] < IDCount and
             List.NonNullCount(List.Last(Table.ToColumns([InputTable]))) > 0,
        each [
             CurrentLevel = if [i] = 0 then "ID" else "Level" & Text.From([i]),
             NextLevel = if [i] = 0 then "Level1" else "Level" & Text.From([i]+1),
             InputTable = ExpandNext([InputTable], NextLevel, CurrentLevel),
             i = [i] + 1
        ]
    ),
    FinalTable = List.Last(RecursiveExpand)[InputTable],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(FinalTable, {"Secondary", "Primary", "ID"}, "Level", "Subordinate"),
    #"Removed Other Columns" = Table.SelectColumns(#"Unpivoted Other Columns",{"ID", "Subordinate"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"ID", Order.Ascending}, {"Subordinate", Order.Ascending}})
in
    #"Sorted Rows"

It will keep expanding levels until expanding to the next level produces all nulls or hits the maximum number of levels to prevent infinite looping.

Answer 2

To get the result you want in SQL, the easiest way to achieve this is to use a recursive CTE.

In the example below I divide the work into two CTEs. The first transforms the set into pairs of managers and subordinates. The second CTE gets all results from the first, and then joins to itself using UNION ALL where the manager from the first CTE is a subordinate in the recursive CTE. This will keep repeating until there are no matches that can be made.

Because it's possible that a subordinate has more than one manager, duplicate rows can be returned for each ancestor. Because of that DISTINCT is used when returning results from the recursive CTE.

WITH all_reports AS (
    SELECT [Primary] [ManagerID], ID [Subordinate]
    FROM tbl
    WHERE [Primary] IS NOT NULL
    UNION
    SELECT [Secondary], ID
    FROM tbl
    WHERE [Secondary] IS NOT NULL
)
, recursive_cte AS (
    SELECT ManagerID, Subordinate
    FROM all_reports
    UNION ALL
    SELECT ancestor.ManagerID, descendant.Subordinate
    FROM recursive_cte ancestor
    INNER JOIN all_reports descendant ON descendant.ManagerID = ancestor.Subordinate
)
SELECT DISTINCT ManagerID, Subordinate
FROM recursive_cte

If you want the distance between manager and subordinate then rewrite the recursive CTE as follows:

SELECT ManagerID, Subordinate, 1 [Distance]
FROM all_reports
UNION ALL
SELECT ancestor.ManagerID, descendant.Subordinate, ancestor.Distance + 1
FROM recursive_cte ancestor
INNER JOIN all_reports descendant ON descendant.ManagerID = ancestor.Subordinate