{"id":539,"date":"2017-11-19T00:47:12","date_gmt":"2017-11-18T22:47:12","guid":{"rendered":"http:\/\/www.flip-design.de\/?p=539"},"modified":"2017-11-19T00:56:47","modified_gmt":"2017-11-18T22:56:47","slug":"dynamic-hierarchically-role-level-security-with-power-bi-ssas-tabular-using-sql-server-cte","status":"publish","type":"post","link":"https:\/\/www.flip-design.de\/?p=539","title":{"rendered":"Dynamic hierarchically Role Level Security with Power BI \/ SSAS Tabular using SQL Server CTE"},"content":{"rendered":"

Introduction<\/strong>
\nIf 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-<\/p>\n

Our example hierarchy:<\/p>\n

\"1\"<\/a><\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

At this scenario, the fact data will be produced by the employees or any manager. If \u201cEmp 1\u201d will create a fact row, the need is that \u201cManager A1\u201d and \u201cManager A\u201d 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.<\/p>\n

Data Model<\/h1>\n

A typically data model at a SQL Server Database:<\/p>\n

A Table with the unique users \u2013 every user has his unique entry with an ID and a email address.
\n\"2\"<\/a><\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

A parent\/Child table \u2013 this table connects the hierarchy level<\/p>\n

\"3\"<\/a><\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

And finally, a fact table<\/p>\n

\"4\"<\/a><\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

The ERM Model<\/h2>\n

\"5\"<\/a><\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

The result of our fact table:<\/p>\n

\"6\"<\/a><\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

SELECT\r\nUserName, Sum(Sales) As Sales\r\nFROM\r\ndbo.FactTable\r\nINNER JOIN dbo.UserNames on FactTable.UserId = UserNames.id\r\nGROUP BY UserName<\/pre>\n
 <\/p>\n
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:<\/p>\n
Query:<\/p>\n
;WITH cte (child, parent, LEVEL, Path, idPath)\r\nAS (\r\n\tSELECT \r\n\t\tT.child\r\n\t\t, T.parent\r\n\t\t, 1 As Level\r\n\t\t, CAST(U.UserName AS VARCHAR(1024)) AS Path\r\n\t\t, CAST(T.child AS VARCHAR(1024)) AS idPath\r\n\tFROM \r\n\t\tdbo.Hirarchy As T\r\n\t\tINNER JOIN dbo.UserNames U ON U.id = T.child\r\n\tWHERE 1=1\r\n\t\tAND (parent Is Null)\r\n\tUNION ALL -- get the childrens\r\n\tSELECT \r\n\t\tC.child\r\n\t\t, C.parent\r\n\t\t, cte.Level + 1 As Level\r\n\t\t, CAST(cte.Path + ' -> ' + CAST(U.eMail AS VARCHAR(1024)) AS VARCHAR(1024)) AS Path\r\n\t\t, CAST(cte.idPath + '|' + CAST(C.child AS VARCHAR(1024)) AS VARCHAR(1024)) AS idPath\r\n\tFROM\r\n\t\tdbo.Hirarchy As C\r\n\tINNER JOIN cte ON cte.child = C.parent\r\n\tINNER JOIN dbo.UserNames U ON U.id = C.child\r\n)\r\nSELECT * FROM cte\r\n<\/pre>\n
Result:<\/p>\n
\"7\"<\/a>This 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 \u201cAdventure Works\u201d 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\u2026 So we need another solution.<\/p>\n
Solution<\/h1>\n
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\u2019t need to multiplicated the rows and we can query the hierarchy. Now we can implement a Row Level Security. Okay, let\u2019s try this \u2026<\/p>\n
Query:<\/p>\n
CREATE view hierarchy as \r\nWITH Hierachy_cte (child, parent, LEVEL, HierarchyPath, HierachyIdPath)\r\nAS (\r\n\u00a0\u00a0 \u00a0SELECT \r\n\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0T.child\r\n\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0, T.child As parent\r\n\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0, 1 As Level\r\n\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0, CAST(U.UserName AS VARCHAR(1024)) AS HierarchyPath\r\n\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0, CAST(U.id AS VARCHAR(1024)) AS HierachyIdPath\r\n\u00a0\u00a0 \u00a0FROM \r\n\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0dbo.Hirarchy As T\r\n\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0INNER JOIN dbo.UserNames U ON U.id = T.child\r\n\u00a0\u00a0 \u00a0WHERE 1=1\r\n\u00a0\u00a0 \u00a0UNION ALL\r\n\u00a0\u00a0 \u00a0SELECT \r\n\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0cte.child\r\n\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0, h.parent\r\n\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0, cte.Level + 1 As Level\r\n\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0, CAST(cte.HierarchyPath + ' -> ' + CAST(U.UserName AS VARCHAR(1024)) AS VARCHAR(1024)) AS HierarchyPath\r\n\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0, CAST(cte.HierachyIdPath + '|' + CAST(h.parent AS VARCHAR(1024)) AS VARCHAR(1024)) AS HierachyIdPath\r\n\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0--, U.eMail\r\n\u00a0\u00a0 \u00a0FROM\r\n\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0dbo.Hirarchy As h\r\n\u00a0\u00a0 \u00a0INNER JOIN Hierachy_cte As Cte ON cte.parent = h.child\r\n\u00a0\u00a0 \u00a0INNER JOIN dbo.UserNames U ON U.id = h.parent\r\n)\r\nSELECT C.*, U.eMail FROM Hierachy_cte C\r\nleft JOIN dbo.UserNames U\r\nON U.id = C.parent\r\nGO\r\nSELECT * FROM dbo.hierarchy \r\nWHERE eMail Is Not Null\r\norder by HierarchyPath\r\n<\/pre>\n
Result<\/p>\n
\"8\"<\/a>The query gives us every combination in the hierarchy tree. If we filter by \u201cManager A1\u201d, we get his manager and his employees.<\/p>\n
<\/h1>\n
Visualization<\/h1>\n
Okay, lets import the data into Microsoft Power BI. After the import, you must correct\/check the relationship between the Users and the Hierarchy:<\/p>\n
\"9\"<\/a><\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
Next, let us create a role based security to test what a manager will see:<\/p>\n
\"10\"<\/a><\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
Hm… we will only see the data from the current user and the manager does not see the data from the lower levels:
\n\"11\"<\/a><\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
To accomplish our goal, we must edit the relationship between the hierarchy and the user table (first you must remove the RLS Filter):<\/p>\n
\"12\"<\/a><\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
Now let\u2019s re-create the RLS on\"13\"<\/a> the hierarchy table:<\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
And now let\u2019s test the role as a manager:<\/p>\n
\"14\"<\/a><\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
And if we view the data by \u201cManager A1\u201d, he has Emp1 & 2 with a Sales Amount from $750, we get following result:<\/p>\n
\"15\"<\/a><\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
Conclusion<\/h1>\n
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.<\/p>\n
Further Information and resources:<\/h1>\n