Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
daxman
Helper II
Helper II

DirectQuery tables that are being used in RLS are only updating when the model refreshes

The .pbix model I built is using primarily Import tables, but there are two security tables that I'm connecting to via DirectQuery. And those security tables are being used by some dynamic RLS code.

 

The reason for this is that the model will only refresh once per month, but people are constantly added/removed from the DQ tables, so the thought is that even through the model's import data stays static throughout the month, the tables used by RLS would constantly be updating in real time.

 

However, it appears that the data does not update at all until the model is refreshed. In this case, the DQ tables are acting as static tables, so new users that get added to the security tables never get pulled into the model, and thus RLS cannot find them and filters out all data so that they see nothing.

 

Has anyone ever run into this issue, and if you have, what was the workaround/fix? 

 

Thank you!

3 REPLIES 3
Psebastianp
Regular Visitor

Hi @daxman,
we had exactly the same problem.

The problem is that the Power BI Service caches the query to improve performance. However, since it always seems to be the same RLS statment, Power BI does not load the DQ data directly from the database, but rather from the cache.

We could work around it by including the username () function in the RLS statement (filtering all rows that contain the corresponding user mail). This has two advantages: This means that only the data for the respective user is loaded into the authorization table, and a new query-stament is created for each user, since it's always different.

v-diye-msft
Community Support
Community Support

Hi @daxman 

 

How did you refresh the mixed data source? Once you added the new roles in the RLS, and run on-demand dataset refresh, the security table should be updated.

006.PNG

also, please confirm you've managed the schedule refresh for both of the Import dataset and DQ dataset under the gateway. 

007.PNG008.PNG

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

Thanks for the response @v-diye-msft.

 

We refresh the mixed dataset manually every month. The roles have been added in RLS. We are using the exact same data source, our DW, for both the Import tables and DQ tables.

 

The issue we are facing is that our DQ RLS security tables are only updating when we manually refresh the model, essentially acting as if we are using them in import mode.

 

Our requirement is to have a model that updates once per month, but the security tables get updated in real time, as people constantly change roles throughout the month.

 

Right now, based on our testing, the DQ RLS security tables never seem to update until the Import tables are refreshed.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors