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

[daxman]

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!

[Psebastianp]

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]

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.

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

[daxman]

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.