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

Setting up Dynamic RLS using a Direct Query

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

MattSnyder_1-1658254373793.pngMattSnyder_2-1658254443536.png

 

MattSnyder_0-1658254326868.png

 

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

2 REPLIES 2
v-zhangti
Community Support
Community Support

Hi, @Matt-Snyder 

 

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
Super User
Super User

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/

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
More tips and guidance in my Power BI architecture and development blog

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.

Top Solution Authors
Top Kudoed Authors