How Bookmarks at Power BI can save performance at the underlying data source. The following article describes how it works: Bookmarks and Performance
Some thoughts/clarifications about the Power BI / Data Gateway (personal mode)
In the last time a customer has installed the Data Gateway in the personal mode and he was very confused about:
- The managing at the powerbi.com Portal
- … and the direct query / live connection possibilities
So, here some clarifications about the Gateway in the personal mode:
- You don’t see the Gateway under Data Gateways at the powerbi.com portal!
You can manage the connection and the credentials in the dataset from a report in den schedule refresh properties. The gateway is only at the enterprise mode to manage!
- Direct Query is not possible! You can only do a refresh for the import mode.
For more information please visit https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem
The following article will describe how to you the Power BI Composite Model. Also I will describe the filtering at Direct Query.
–> Article Power B Composite Model
The following article describes how to set dynamically and automatically the pricing tier at a Azure SQL Server Database. I use this scenario by Power BI productive or POC scenarios, where I cannot determine the really usage of the solution and don’t to spent so much money in the night 😉
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:
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.
A typically data model at a SQL Server Database:
A parent/Child table – this table connects the hierarchy level
And finally, a fact table
The ERM Model
The result of our fact table:
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:
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
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 “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.
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 …
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
Okay, lets import the data into Microsoft Power BI. After the import, you must correct/check the relationship between the Users and the Hierarchy:
Next, let us create a role based security to test what a manager will see:
To accomplish our goal, we must edit the relationship between the hierarchy and the user table (first you must remove the RLS Filter):
And now let’s test the role as a manager:
And if we view the data by “Manager A1”, he has Emp1 & 2 with a Sales Amount from $750, we get following result:
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:
- Visualize Parent Child in PBI
- SQL Server CTE