RLS in PowerBI using Scripting

[lrao]

Hi All,

I am trying to apply a row level security for a model having Employee -> Manager relationship. The report is a directquery to Oracle db due to sensitive data and security concern. Since, we don't have Oracle 12 client, as a workaround connection to Oracle is via linked server within sql server 2012.

I was able to succeed in demonstrating RLS and test it on web by following the steps mentioned in below link
https://powerbi.microsoft.com/en-us/documentation/powerbi-admin-rls/

As per my understanding creation and updation of the RLS can be done only through Power BI Desktop and not through scripting.
Creating a role for each Manager in a bigger organization becomes tedious task. Increases maintenance cost due to manual work of handling the roles once it is moved to production.

Please help me if there is any way to handle this RLS maintenance using some script within power bi.

Thanks & Regards,
Lokesh

[ankitpatira · Accepted solution]

@lrao In addition to what smoupre said you can't script out RLS task in power bi desktop but you can achieve somewhat automation (less manual work) by just creating one role and passing UPN property of the logged in user via that role to the user security table where you will have Employee Manager relationship mapped. Check out below link on using Username() function of DAX for RLS.

https://powerbi.microsoft.com/en-us/blog/using-username-in-dax-with-row-level-security/

[lrao]

@Greg_Deckler - Thanks for the input.

@ankitpatira - Many Thanks for the solution. Other day I was trying this on SSAS Tabular model role and it didn't work there. But in PowerBI this solution has worked as expected. 

[Greg_Deckler]

To my knowledge, there is no ability to script or automate things in the Desktop.