cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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
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.

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
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors