Setting up Dynamic RLS using a Direct Query

[Anonymous]

Hello everyone,

I am leading the transition from Tableau to Power BI at our company of ~12,000 employees and in the past I was specifically a report designer and did not do any of the security/administration as this was already in place.

Now, I am having to take on the admin role and set up security to filter down the data in our reports based on the user viewing them so please forgive my lack of knowledge, I am trying my best here haha

To give more context about how the company already does this in Tableau so that you guys may now how to replicate it in Power BI - or even a better way - in Tableau, we've used a SQL query that filtered the data based on the User's login. Which would only show the rows they had access to which returned their Region location, District Location, etc. which was used to filter the tables.

Here is the SQL code:

SELECT
AUTH.LOGIN as "User Login"
,AUTH.AUTHSITEID as "User Authorized SiteID"
,AUTH.SITEID as "User Actual SiteID"
,LOC.Company as "User Company"
,LOC.Region as "User Region"
,LOC.Area as "User District"
,LOC.Branch as "User Branch"
FROM dataBaseNameALL AUTH
LEFT OUTER JOIN dataBaseNameREF LOC
ON
   LOC.IBMiSiteID = AUTH.AUTHSITEID

UNION ALL
SELECT
DISTINCT
'TABLEAUSERVICE' as "User Login"
,AUTH.AUTHSITEID as "User Authorized SiteID"
,'0000000' as "User Actual SiteID"
,LOC.Company as "User Company"
,LOC.Region as "User Region"
,LOC.Area as "User District"
,LOC.Branch as "User Branch"
FROM dataBaseName AUTH
LEFT OUTER JOIN dataBaseNameREF LOC
ON
   LOC.IBMiSiteID = AUTH.AUTHSITEID
WHERE LOC.Company = '90'

So - I have added that query as a Direct Query in Power BI and got the data model built (I used many to many since both of the tables have repeating values), but when I look at the report in Power BI Service, it says "there is no gateway".

Any thoughts to fix this or a better solution?

The main reason they are using this query is because so many users have access to different Regions, Districts, etc. which makes for a datasource with 16 million rows so they'd rather put the strain on the data warehouse instead of the Tableau Service.

I feel like there has to be an easier solution, but this is the way they have always done it so they want to know if it's at least possible in Power BI - if not then I can try something else.

Much appreciated,

- Matt

[v-zhangti]

Hi, @Anonymous 

You use Direct Query to connect to the database and you want to view the reports in Power BI Service, you need to set up the gateway first. Please refer to the following steps.Manage your data source - SQL - Power BI | Microsoft Docs

Secondly about you want to set up hierarchical RLS, you can refer to the following link.

https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi 

Solved: Dynamic RLS - Microsoft Power BI Community

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[otravers]

Right now, you have a database connectivity problem, forget about RLS until you've successfully set up a data gateway to allow the Power BI service to connect to your SQL database:

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem

Once you have it working, here's an entry I wrote that covers the ins and outs of Power BI RLS:

https://www.oliviertravers.com/row-level-security-rls-business-intelligence/