Analytics: Power BI
The Solution You Need for Dynamic Row Level Security: Semantic Model for User Access Table
Instantly Add Users to Your Power BI RLS Access Table Without Refreshing the Report
Recently, I encountered a unique challenge in Power BI report development that made me rethink how to design an efficient and dynamic Row-Level Security (RLS) architecture.
Problem Statement:
The report I was developing was expected to serve a large user base, so it needed an efficient and easy-to-manage access system. Typically, the go-to solution is Dynamic Row-Level Security (RLS) with a User Access Table maintained in SharePoint. This setup allows you to easily add or remove users without hassle, as long as you refresh the Power BI report to reflect the changes and update the user’s access in the Power BI Service (Security).
However, there was a twist: the report could only be refreshed on its scheduled dates, no exceptions. This meant that if a user was added to the User Access Table between refreshes, they wouldn’t gain access to the report until the next scheduled refresh.
Solution:
To address this challenge, we need to find a way to refresh the User Access Table in the Power BI report without refreshing the entire report. At first glance, this seems impossible since on-demand refreshes require refreshing the entire dataset, not just a single table. But is there really no way to achieve this?
Yes, it’s possible to refresh a specific table within a Power BI report independently of the full report refresh by switching that table’s ingestion mode to “Direct Query.”
When a table is connected to a data source using Direct Query mode, it updates instantly based on changes in the source table. However, Direct Query is only supported for a limited set of data sources in Power BI. Data sources with structured data models, such as Oracle DB, SQL Server, and Snowflake, are ideal candidates for this connection mode, allowing seamless integration with Power BI.
Unfortunately, SharePoint isn’t supported for Direct Query connections. The workaround is to write the data from SharePoint to a Snowflake table and then set up a Direct Query from Power BI to the Snowflake table. The solution involves automating the data transfer from SharePoint to Snowflake using an Azure Data Factory (ADF) pipeline, creating a flow that looks something like this:
While this solution might seem like a perfect fit for an enterprise-level architecture, it comes with several drawbacks. First, it’s relatively complex to develop and maintain. Additionally, it ties up multiple resources during its operation window. Fortunately, there’s a much simpler way to achieve the same functionality of reading the User Access Table using Direct Query.
Yes, relational databases are certainly a source that Power BI can query directly using Direct Query, but they’re not the only option. There’s another source that Power BI can connect to using Direct Query — a Power BI Semantic Model (Dataset).
A Power BI semantic model is a structured representation of your data, designed to make it easier to create reports and perform analyses. It serves as the foundation of a Power BI report, providing the necessary logic, relationships, and calculations to transform raw data into meaningful insights.
We can leverage a semantic model stored in a Power BI Service workspace as a data source for another Power BI report. Just a few years ago, if you used a semantic model as a source, you couldn’t add any other data sources to that report. However, with the continuous improvements in Power BI, you can now integrate multiple data sources into a single report, including tables sourced from another semantic model using Direct Query. This significantly enhances flexibility in data ingestion, especially when it comes to reusing transformed data.
In this process, instead of storing the User Access Table in Snowflake or other relational databases, you can store it in a Semantic Model using Import mode from SharePoint. Other Power BI reports can then use this User Access Table within the Semantic Model as a data source via Direct Query. This setup allows you to trigger on-demand refreshes for the Semantic Model. When the Semantic Model is updated, all downstream reports connected to it via Direct Query will instantly reflect the new user information.
To connect to the Semantic Model as a source from your existing Power BI reports, go to Get data and select Power BI semantic model.
Select the relevant Semantic Model from the list and click Connect.
Next, choose the specific table or tables you want to connect using Direct Query and click Submit. Note that when connecting from a Power BI report to a Semantic Model, Power BI automatically defaults to Direct Query mode and does not provide an option to select between Import and Direct Query.
With this approach the solution schematic looks something like this:
We’ve now addressed how to add new user IDs to our Power BI report without needing a refresh. But there’s one more step. To fully implement dynamic row-level security (RLS), we need to create a security role in our User Access table. This table filters the dimension tables in the data model to manage RLS. By using the DAX function USERPRINCIPALNAME() on the User ID column of our User Access Table, we ensure that users can only see data relevant to their ID. This is the key to finalizing our dynamic RLS setup!
The Final Problem
In our solution, we’ve sourced the User Access Table using DirectQuery from another Semantic Model. However, this introduces a tricky issue: Power BI doesn’t currently allow security roles to be applied to tables sourced via DirectQuery. You won’t even see the DirectQuery table listed in the “Manage Roles” section. So, what can we do? Don’t worry — there’s a simple workaround. We can “trick” Power BI by creating a calculated table that references the DirectQuery User Access Table. This lets us apply security roles as needed without the usual limitations!
// Let's call this calculated table "User Access Table New"
User Access Table New = 'User Access Table' // table using DirectQueryBy creating a calculated table that pulls data from the User Access Table via DirectQuery, we ensure it stays updated with any changes. At the same time, we “trick” Power BI into recognizing it as a valid table where security roles can be applied. This clever approach keeps everything current while bypassing Power BI’s DirectQuery limitations!
With this solution in place, you can now open “Manage Roles” and create a new security role by applying USERPRINCIPALNAME() to the User ID column of the calculated table. Just remember to use this table as the access restriction table, linking it to the dimension tables in your data model. This completes the setup for dynamic row-level security!
This is how leveraging Direct Query with a Power BI Semantic Model offers a powerful solution for dynamic data access and seamless integration. By storing the User Access Table in a Semantic Model and connecting other reports via Direct Query, you can efficiently manage user access and ensure that all your reports are consistently up-to-date. This approach simplifies data management and enhances flexibility.
Thank you for reading!
Please feel free to clap 👏 if you found this article helpful and leave a comment or share it with others. Follow me for more insights on analytics. You can find me on LinkedIn.
Don’t forget to subscribe to
and join our Power BI community
