Dinamic RLS performance problems from 5.7.2022

[Asum123]

Hi,

In the last few days (from Tuesday, July 5, 2022), users who have limited access to the database via dynamic RLS (row level security) have significant problems with the performance of reports.

Could you please check what it is about.

Thank you,

Best, Aleksandar

[v-yingjl]

You can consider describing it in details about the performance of dynamic RLS in Power BI Service, like whether users open the report cannot see the corresponding data, the report data loads slowly.

In addition, about the performance of Row-level Security, you can refer this viedo:

Measuring RLS Performance in Power BI 

Best Regards,
Community Support Team _ Yingjie Li

[Asum123]

Hi,

Hi. We are using all of their recommendation for best RLS practice. Users get to theirs data through USERPRINCIPALNAME(), and so far we did not have any issues with performance (we are using same RLS rools for 2 years now). Today for one user visual broke and we got info "Visual has exceeded the available recources". In major cases it is an issue of slow reports. This only hapens on service (online). Whan I test it on desktop everithing is working fine.
This weekend I plan to republish desktop to service and see if that helps. On 2.7.2022 i updated power bi desktop to jun 2022 update, maybe is some kind of issue from that step...

Aleksandar

[Asum123]

Hi.
I have tried to go back to May 2022 update and it didn't work, it still works realy slow (uninstall desktop on local machine, instal Maj 2022 version (2.105.1143.0 64-bit (maj 2022.)) and republish it to a the service). When I test locally one of the roles it still works fine. I will send you now more detail on RSL structure and its relationship with dimensional table that is filtering:

RLS Table (users): Korisnici_RLS

 RLS Table (what user will filter what in dimensional table😞 Klasa_2_do_6_RLS

Table (part of dimensional table that RLS is filtering): Artikli HQ - unique sifra

RLS code for filtering table „Artikli HQ - unique sifra“:

[Odjel iz kategorije (3)] =

LOOKUPVALUE(

    'Klasa_2_do_6_RLS'[Kategorija do klase 2 (3)],

    Korisnici_RLS[User principal name],

         USERPRINCIPALNAME(),

    'Klasa_2_do_6_RLS'[Kategorija do klase 2 (3)],

    'Artikli HQ - unique sifra'[Odjel iz kategorije (3)]

)

||

[Kategorija do klase 3 (5)]=

LOOKUPVALUE(

    'Klasa_2_do_6_RLS'[Kategorija do klase 3 (5)],

    Korisnici_RLS[User principal name],

         USERPRINCIPALNAME(),

    'Klasa_2_do_6_RLS'[Kategorija do klase 3 (5)],

    'Artikli HQ - unique sifra'[Kategorija do klase 3 (5)]

)

||

[Kategorija do klase 4 (7)]=

LOOKUPVALUE(

    'Klasa_2_do_6_RLS'[Kategorija do klase 4 (7)],

    Korisnici_RLS[User principal name],

         USERPRINCIPALNAME(),

    'Klasa_2_do_6_RLS'[Kategorija do klase 4 (7)],

    'Artikli HQ - unique sifra'[Kategorija do klase 4 (7)]

)

||

[Kategorija do klase 5 (9)]=

LOOKUPVALUE(

    'Klasa_2_do_6_RLS'[Kategorija do klase 5 (9)],

    Korisnici_RLS[User principal name],

         USERPRINCIPALNAME(),

    'Klasa_2_do_6_RLS'[Kategorija do klase 5 (9)],

    'Artikli HQ - unique sifra'[Kategorija do klase 5 (9)]

)

||

[Kategorija]=

LOOKUPVALUE(

    'Klasa_2_do_6_RLS'[Kategorija do klase 6],

    Korisnici_RLS[User principal name],

         USERPRINCIPALNAME(),

    'Klasa_2_do_6_RLS'[Kategorija do klase 6],

    'Artikli HQ - unique sifra'[Kategorija]

)

[Asum123]

Hi @v-yingjl , 

It took me some while but I manage to figure it out what it is affecting RLS performance in this case.

It is the case of how big was dimensional table that RLS is hiting ('Artikli HQ - unique sifra'). This is our product table and it had around 130k rows. In the proces of updating in week 5.7. we increased number of SKU-s for 200 rows more and this crashed RLS performance for 7-10x.

I have deleted part of this data (inactive SKU-s), around 30k rows and RLS performance went back to original.

It would be good that this is not hitting RLS performance (easier to maintain) bat this is some workaround.

Best,

Aleksandar