PBI Embedded performance problem

Hi.

This was a bit of a headace to get it to work as it should. I have now managed to fins a solution that is working with a acceptable responsetime.

Case:
Get decent performance on a Power BI Embedded solution embedded through a frame on our company portal for about 100k external users.
PBI report uses a Hybrid model of aggregated data and some dimensions that is a imported dataset. Contains 2 table of about 15 million rows, and a few dimesions like date, merchant, transactiontype etc.
The problem was that from the aggredated data the users should be able to drill down to detailed transaction level. This drillthrough is to a Direct Query against a Azure SQL DB containing about 1 billion rows with fairly detailed information. And from there the users should be able to download the details to a file for import into their reconsiliation systems.

Problem:

I was not able to get the Embedded report to perform to acceptable performance levels. Everything worked nice and very responsive through Power BI Desktop and PBI Reporting Services. It was easy to get it to work through those solutions since the scripts sent to the DB was simple and PBI did what was exptected with query folding and sent limited amount of sqls down to the DB. Problem was when it was ran throught the frame in our company portal. This report also uses Row Level Sequrity to add another level of complexity.

There where several issues here. First it did not send the same queries as it did through the service and desktop. It sent several queries and returned insane amount of data that wasn't needed in the report.

I found that the main reason for this generation of unnessasary queries was due to the fact that in the list on the Direct Query report i had used a few dimensional fields. This causes the engine to create several queries to create the list. I created a massive matrix of all possible combinations of the dimension data which it pulled from the DB, even though it only needed 1 combination which was already defined in the drillthrough filters. It used this matrix to filter against the filters set in the report, but this killed the performance, and it was really hard to tune the indexes correctly due to the several combinations of scripts sent, bet the inmodel work was what killed performance.

Solution:
I ended up trimming the data in the Direct Query source. This is always a good solution, but i also had to add a few fields here.
The solution was to force PBI to send only 1 query and actually listen to the filters you have set in the drillthrough to be used. I managed to change the driect query report to ONLY use fields from the DQ source. By doing this i was able to get PBI to only send 1 query even when ran through embedded. Now i was able to tune the indexes perfectly. Now the users are able to serach through detailed transactions, produce lists connected to certain settlement sessions, or produce a list af all transactions for a given month. Merchants with only a few thousand rows gets instant response, and merchants with around 100k transactions gets responce on 2-3 sec.

So to be able to solve this issue, be sure to only use data from one datasource, and especially not mix with the imported model.

Anonymous suggested splitting into 2 reports. This would in theroy have fixed this problem since the DQ report would probably only had data from that datasource. But i was reluctant to do this since the report already had pased PEN test, and didn't want to hire in a new PEN test team due to implementation of a second report. And not sure the URL query string filter would have passed PEN test.
Hope this gives a few hints for others with similar issues to know what to look for in their solution.

Regards,
Kjetil Blomsøy