Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Ben_71
Regular Visitor

Dynamic User related SQL WHERE clause

Hello friends:

 

I am sure this has been asked before but i could not find a solution. So here goes:

 

We have data in a SQL Indexed View that is grouped inside of the Query Editor. The requirement is to present the data with a dynamic WHERE clause which is also defined in a separate setup table. For e.g., the table has user email, and the WHERE statement.

 

I would like to setup RLS in a way that i can find out who the user is at runtime, determine the associated WHERE clause, append that to the main table (in a way SQL injection) and present the data. If the WHERE clause changes in the table, the data shown on the screen changes.

 

Also to keep in mind, the data is fetched from the main view using Import Method and not Direct Query because of the volume of unfiltered data and to help with performance.

 

Please help!!. I have spent a lot of hours trying to do all sorts of things, but cannot get past this hump.

 

Thanks a bunch.

Ben

4 REPLIES 4
AlexisOlson
Super User
Super User

If you're using Import, then the only option is to load all of the data (without user filtering) and then apply RLS as appropriate on that unfiltered data.

 

@lbendlin is correct. Import doesn't allow for dynamic querying, full stop. DirectQuery should work in this scenario, possibly even without needing to write explicit WHERE clauses if table relationships are set up correctly.

Thank you. Unfortunately, the setup table (UserNameQuery) that houses the WHERE Clause has a format like this:

EmailReportNameCriteria
ab.c@google.comWSUMWHERE Company IN ('A','B','C')
de.f@google.comWSUMWHERE Company = 'A' AND ReportingRegion LIKE 'XX%'

 

So, what i did was to import the main table using DirectQuery. Then i went into Advanced Editor:

 

let
CurrentUser = UserNameQuery, //UserNameQ
ListOfUsers = Table.ToList(CurrentUser),
StringOfUsers = Text.Combine(ListOfUsers,""),
Source1=Sql.Database("xxx-container.database.windows.net", "yy-container", [Query="SELECT [QUERYCRITERIA] FROM [dbo].[Report_Criteria] WHERE QRYNAME = 'WSUM' AND [PrincipalName] = 'ab.c@google.com'", CreateNavigationProperties=false]),
ListOfParts = Table.ToList(Source1),
StringOfParts=Text.Combine(ListOfParts,""),
Source = Sql.Database("xxx-container.database.windows.net", "yy-container", [Query="SELECT * FROM dbo.v_WIRESUM_Indexed WITH (NOEXPAND) " & StringOfParts, CreateNavigationProperties=false])
in
Source

 

When i come back to the main screen and refresh data, i get the following:

Failed to save modifications to the server. Error returned: 'DirectQuery partition 'Query2-2abf1210-e4eb-4ca5-8b0b-110bfdc665fd' has '2' datasource reference(s) in its expression which is not allowed. '.

 

Please advise.

 

thanks

Ben

"  what i did was to import the main table using DirectQuery"

 

Not possible. Use Import mode, not Direct Query.

lbendlin
Super User
Super User

the data is fetched from the main view using Import Method and not Direct Query

This right here is your show stopper. You cannot have dynamic query parameters in import mode. It is technically impossible. It only works in Direct Query mode.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors