Dynamic hierarchically Role Level Security with Power BI / SSAS Tabular using SQL Server CTE

Posted

Introduction
If you have an organizational hierarchically view on your data, the need is often that every manager wants to view the data of his lower nodes and employees. This includes that the manager of a manager wants also to view the data of the next lower manager and his employees-

Our example hierarchy:

1

 

 

 

 

 

At this scenario, the fact data will be produced by the employees or any manager. If “Emp 1” will create a fact row, the need is that “Manager A1” and “Manager A” and the Top-Level Manager want to see the data in the hierarchy. The other employees and the Manager A2 does not able to see the data, because the hierarchy path is not to them connected.

Data Model

A typically data model at a SQL Server Database:

A Table with the unique users – every user has his unique entry with an ID and a email address.
2

 

 

 

A parent/Child table – this table connects the hierarchy level

3

 

 

 

And finally, a fact table

4

 

 

 

 

The ERM Model

5

 

 

 

 

 

 

 

The result of our fact table:

6

 

 

 

 

 

 

1
2
3
4
5
6

SELECT
UserName, Sum(Sales) As Sales
FROM
dbo.FactTable
INNER JOIN dbo.UserNames on FactTable.UserId = UserNames.id
GROUP BY UserName

 

If we query the data, we have only a direct way from the user to the facts as shown above. The data model gives us also a hierarchy table to get the next link. If we use a CTE to query the data, we get our organigram:

Query:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

;WITH cte (child, parent, LEVEL, Path, idPath)
AS (
	SELECT 
		T.child
		, T.parent
		, 1 As Level
		, CAST(U.UserName AS VARCHAR(1024)) AS Path
		, CAST(T.child AS VARCHAR(1024)) AS idPath
	FROM 
		dbo.Hirarchy As T
		INNER JOIN dbo.UserNames U ON U.id = T.child
	WHERE 1=1
		AND (parent Is Null)
	UNION ALL -- get the childrens
	SELECT 
		C.child
		, C.parent
		, cte.Level + 1 As Level
		, CAST(cte.Path + ' -> ' + CAST(U.eMail AS VARCHAR(1024)) AS VARCHAR(1024)) AS Path
		, CAST(cte.idPath + '|' + CAST(C.child AS VARCHAR(1024)) AS VARCHAR(1024)) AS idPath
	FROM
		dbo.Hirarchy As C
	INNER JOIN cte ON cte.child = C.parent
	INNER JOIN dbo.UserNames U ON U.id = C.child
)
SELECT * FROM cte

Result:

7This query works, but we can only join this with the facts and see which manager hierarchy does stand behind the user in a string, but we cannot query the hierarchy or filter this. An option will be to multiplicated the rows in the fact data and create every row with the full hierarchy, but this will be only works with the “Adventure Works” or something else and this does not meet the reality. If we have an organization with 1000 employess and over 100.000.000 fact rows, we will get tons of rows and we get performance troubles… So we need another solution.

Solution

If we create a CTE that will create a unique row with every combination from the hierarchy that belongs to the tree, and we join the result with the facts, we don’t need to multiplicated the rows and we can query the hierarchy. Now we can implement a Row Level Security. Okay, let’s try this …

Query:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

CREATE VIEW hierarchy AS 
WITH Hierachy_cte (child, parent, LEVEL, HierarchyPath, HierachyIdPath)
AS (
    SELECT 
        T.child
        , T.child AS parent
        , 1 AS Level
        , CAST(U.UserName AS VARCHAR(1024)) AS HierarchyPath
        , CAST(U.id AS VARCHAR(1024)) AS HierachyIdPath
    FROM 
        dbo.Hirarchy AS T
        INNER JOIN dbo.UserNames U ON U.id = T.child
    WHERE 1=1
    UNION ALL
    SELECT 
        cte.child
        , h.parent
        , cte.Level + 1 AS Level
        , CAST(cte.HierarchyPath + ' -> ' + CAST(U.UserName AS VARCHAR(1024)) AS VARCHAR(1024)) AS HierarchyPath
        , CAST(cte.HierachyIdPath + '|' + CAST(h.parent AS VARCHAR(1024)) AS VARCHAR(1024)) AS HierachyIdPath
        --, U.eMail
    FROM
        dbo.Hirarchy AS h
    INNER JOIN Hierachy_cte AS Cte ON cte.parent = h.child
    INNER JOIN dbo.UserNames U ON U.id = h.parent
)
SELECT C.*, U.eMail FROM Hierachy_cte C
LEFT JOIN dbo.UserNames U
ON U.id = C.parent
GO
SELECT * FROM dbo.hierarchy 
WHERE eMail IS NOT NULL
ORDER BY HierarchyPath

Result

8The query gives us every combination in the hierarchy tree. If we filter by “Manager A1”, we get his manager and his employees.

Visualization

Okay, lets import the data into Microsoft Power BI. After the import, you must correct/check the relationship between the Users and the Hierarchy:

9

 

 

 

 

 

Next, let us create a role based security to test what a manager will see:

10

 

 

 

Hm… we will only see the data from the current user and the manager does not see the data from the lower levels:
11

 

 

 

 

To accomplish our goal, we must edit the relationship between the hierarchy and the user table (first you must remove the RLS Filter):

12

 

 

 

 

 

 

 

 

 

Now let’s re-create the RLS on13 the hierarchy table:

 

 

 

 

 

And now let’s test the role as a manager:

14

 

 

 

 

 

And if we view the data by “Manager A1”, he has Emp1 & 2 with a Sales Amount from $750, we get following result:

15

 

 

 

 

Conclusion

If you have a hierarchy table, this is the easiest way to create a RLS und an a hierarchy filter. You will only create some more rows in a mapping table. The RLS is quiet simple and works with Power BI and SSAS Tabular.

Further Information and resources:

Posted
Categorized: BI, PowerBI

Schreibe einen Kommentar