Best way to filter a large dataset to embedded users

[MaxW]

I am using embedded reports in my web app, and need to filter reports based on the web app's logged in user. I currently pass the web app userId to USERNAME() when I embed the report and use row level security. The reports are all SQL queries with a 30 minute refresh cycle.

This works for small datasets, but I have 10,000 users, and some datasets for all users are several GB which is too big to refresh regularly and the visual interactions are too slow to use.

What is the recommended way to surface the report to the customer? Ideally the report would refresh on click with dynamic SQL by passing the userId to the query but I don't believe this is possible?

I had an idea to execute API calls on the report button click to update a dataset parameter and refresh the dataset before generating the embed token, but I have hit an API request limit.

[GilbertQ]

Hi there

Are your reports using Import mode?

Following on from that if you are using an embedded App, what about passing a filter via the URL? The users will not see the Power BI URL, so they will not be able to alter the filters?

[MaxW]

Hi, 

I am using import, but don't the URL filters just filter the dataset that is already cached? I cant refresh a multi GB dataset every 30 mins. The report might only be run a few times a day but needs to be current for whoever runs it.

[GilbertQ]

Hi @MaxW 

When using the URL Filter it will honour the filter for the report being consumed.

[MaxW]

But it wont re-run the SQL query with the filter as a parameter will it?

[GilbertQ]

If you are using Import mode, then it will run the query on the Imported data

[MaxW]

Right, but I need to filter the SQL, as my dataset is too big to import or refresh. Can Power BI do this?

[GilbertQ]

Hi there

I am not 100% sure with DirectQuery

When you say your dataset is too big, how big is it when designed as a star schema and put say a month or two's worth of data into a PBIX?

[MaxW]

If I run my SQL query without specifying a userId the result is hundreds of millions of rows and takes 40 minutes to execute. If I can include 'where userid ={userid} then the query is tiny and executes in less than two seconds. How can I get the report to run the query when the report is requested? 

[GilbertQ]

Hi there

You could either use DirectQuery or Composite Models, as long as you have got the UPN in the column which is the UserID?

[MaxW]

Hi,
I had tried that but couldn't figure out how to pass the USERNAME() as a parameter to the query? All the references to it use DAX after the SQL has already run.

[jcapezzuto]

Hi Max,

Please see the section in this article about Adding Parameters to Filter Data.  Basically, you just define your parameter, then modify the query to be like 'and userid = " & yourparam & "'

https://www.red-gate.com/simple-talk/sql/bi/power-bi-introduction-working-with-parameters-in-power-b...