RLS Tool: Check row-level security in Power BI models
Before I created the template for listing relationships in Power BI, I had one for validating row-level security. I updated it to work with XMLA endpoints in Premium and re-published to GitHub.
Row
This template file will get row-level security from a Power BI Desktop file or from a Power BI model in a Premium workspace. Then, it displays the impact of the RLS rules on the tables in the model. It can also be used to export RLS rules.
The first page (shown above) shows a row of cards with: number of roles, total tables, filtered tables, indirectly filtered, directly filtered, and unfiltered.
The first table shows the role name and how many tables are directly filtered. This is useful for telling if complex duplicated rules have similar setups, or if you missed a table for filtered. Roles having max number of rules are highlighted.
The second table shows the impact of rules on tables in the model. Unfiltered tables are highlighted in yellow, while blue highlighting shows whether tables are filtered directly or indirectly. There’s also a blue highlighted column showing number of roles the filtering is used by.
This model has 5 roles: one called No Restrictions and others for Northeast, Northwest, Southeast, and Southwest territories. ‘Sales Territory’ dimension table shows up in 4 roles. ‘Sales Territory’ is directly filtered, and the fact table, Sales, is indirectly filtered.
With files in Power BI Pro, this needs to be run from Power BI Desktop using DAX Studio.
If analyzing a model in Power BI Premium, you can take advantage of XMLA endpoints and publish it as a report which refreshes. To do this, you need to edit the data source for each query and replace serverClean and catalog references with the text value of the server and catalog. Then you would be able to setup refreshes to monitor the status of row-level security in real time. If the RLS is changed during updates, the report would show the changed impact.
power-bi-metadata/Row-level-security RLS.pbit at master · fpk3/power-bi-metadata (github.com)
For local files, get row-level security settings from a Power BI Desktop file using DAX Studio.
(1) Open a report in Power BI Desktop.
(2) Open DAX Studio and connect to the open report. PARAMETERS:
(a) localhost is displayed in the bottom frame of DAX Studio. Type number of localhost
(b) For local files, catalog_name is a long string of characters, which can be found by running this query in DAX Studio and using the result: SELECT [CATALOG_NAME] FROM $SYSTEM.DBSCHEMA_CATALOGS • DAX Studio is available at https://daxstudio.org/
For Premium files in the service, the workspace connection is found in workplace settings under the Premium tab and starts with powerbi://. The catalog_name is the name of the dataset.
• DAX Studio is available at https://daxstudio.org/
NOTE: Approve Native Queries when prompted.
IMPORTANT: Use Microsoft authentication for XMLA endpoint, and use Window authentication for localhost. If you use Microsoft authentication for XMLA, it will fail, and then you’ll have to figure out how to clear the credentials and start again. If you get a privacy error about rebuilding your query, adjust privacy settings and refresh.