propagation de hiérarchie multiparentale dans SQL / DAX

Vous devrez aplatir la hiérarchie des rapports et la hiérarchie des rapports secondaires, en les chargeant dans des tableaux séparés dans le modèle tabulaire.

Voir DAX Patterns: Parent-Child Hierarchies pour savoir comment procéder complètement. dans DAX. Ou vous pouvez utiliser une requête SQL Server à l'aide d'une expression de table commune récursive pour aplatir les deux hiérarchies.

Dans les deux cas, ils deviennent deux tables séparées dans le modèle et deux relations séparées, que vous pouvez ensuite référencer dans vos filtres RLS.

Ceci est assez facilement résolu en utilisant les fonctions de hiérarchie parent-enfant dans DAX. Je ne pense pas que vous ayez besoin de créer des tables supplémentaires, respectez simplement les conditions suivantes dans vos règles RLS:

Pour l'employé N , il vous suffit de vérifier si

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"

ou

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

Notez que cela permet à l'employé N de se voir ainsi que ses subordonnés, mais vous pouvez ajouter une condition supplémentaire si vous ne le souhaitez pas.

Modifier: Lorsque votre structure n'est pas un arbre, le problème devient plus difficile. Voici une approche qui devrait fonctionner.

Pour chaque ID , trouvez les subordonnés pour obtenir Level1 , recherchez Level1 pour le niveau suivant de subordonnés, et ainsi de suite jusqu'à ce qu'aucun subordonné n'existe. (Si vous avez une boucle dans votre structure qui vous ramène à un niveau supérieur, alors vous serez coincé dans la récursivité.)

Dans ce cas, il y a trois niveaux en dessous du sommet donc nous avons besoin de trois étapes .

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"

Voici le code M pour faire cela dans l'éditeur Power Query:

| 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       |           |        |        |        |

Voici la fonction personnalisée utilisée plusieurs fois pour passer au niveau suivant:

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

Pour rendre cela général, j'ai évidemment besoin de mettre l'expansion et l'ajout dans une boucle récursive. J'y reviendrai dès que le temps me le permettra.

Modifier: Voici une version récursive de la requête qui utilise un pivotement au lieu de l'ajout.

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

Il continuera à augmenter les niveaux jusqu'à ce que l'expansion au niveau suivant produise toutes les valeurs nulles ou atteigne le nombre maximum de niveaux pour éviter une boucle infinie.

Pour obtenir le résultat souhaité en SQL, le moyen le plus simple d'y parvenir est d'utiliser un CTE récursif.

Dans l'exemple ci-dessous, je divise le travail en deux CTE. Le premier transforme l'ensemble en paires de managers et de subordonnés. Le deuxième CTE obtient tous les résultats du premier, puis se joint à lui-même en utilisant UNION ALL où le gestionnaire du premier CTE est un subordonné dans le CTE récursif. Cela continuera à se répéter jusqu'à ce qu'il n'y ait aucune correspondance qui puisse être faite.

Comme il est possible qu'un subordonné ait plus d'un gestionnaire, des lignes en double peuvent être retournées pour chaque ancêtre. Pour cette raison, DISTINCT est utilisé lors du renvoi des résultats du CTE récursif.

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

Si vous voulez la distance entre le manager et le subordonné, réécrivez le CTE récursif comme suit:

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

Un moyen simple de stocker, à mon humble avis. Tout int. Seul un point de jonction mais qui comblera tous les besoins que je peux voir exprimés avec place pour une grande flexibilité dans toutes les directions. Le projet peut être soit un petit projet, soit un regroupement de projets et même une hiérarchie de service / entreprise. On dirait que dynamique et adaptable sont une priorité ou des sortes.

+--+-------+---------+-------+--------+
|ID|project|over     |under  |level   |
|0 |14     |0        |9      |1       |
|1 |53     |4        |1      |2       |
|2 |4      |4        |4      |2       |
|3 |1      |4        |2      |3       |
|4 |1      |0        |7      |1       |
|5 |2      |4        |6      |1       |
|6 |4      |4        |8      |5       |
+--+-------+---------+-------+--------+

Un exemple d'utilisation d'un projet de manière étendue serait l'ajout d'un projet "Énoncé de mission" en cours pour un service / entreprise / établissement / bureau / room / vendor / position ou tout autre "groupement" auquel vous pouvez penser où une résolution de hiérarchie est souhaitée. Pourquoi rendre la vie plus compliquée? La pire chose que vous ayez à faire un jour est de décharger les entrées des projets terminés dans une archive quelconque si le besoin d'informations historiques est nécessaire.