Forum Discussion
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
Hi KjetilAB ,
Thank you for reaching out to Microsoft Fabric Community Forum.
Since the report works fine in Power BI Desktop and Service, the embedded version generates extra queries, including a heavy matrix of all combinations, which slows down performance,
You can try the below workarounds which help to improve performance.
- Limit the number of visuals on the drill through page to only those necessary. Each visual can generate its own query, so reducing the number can help improve performance.
- Use aggregations to reduce the data being queried( I think you already implemented it).
- Optimize DAX measures for better Direct Query performance.
- Instead of page-level filters, consider applying filters directly to visuals where possible. This can help reduce the scope of data queried.
- Use Performance Analyzer to check the performance of the queries or visuals.
Optimization guide for Power BI - Power BI | Microsoft Learn
Monitor report performance in Power BI - Power BI | Microsoft Learn
Best practices for faster performance in Power BI embedded analytics - Power BI | Microsoft Learn
Troubleshoot DirectQuery models in Power BI Desktop - Power BI | Microsoft Learn
Use DirectQuery in Power BI Desktop - Power BI | Microsoft Learn
Similar issues:
Matrix Performance issue in Direct query - Microsoft Fabric Community
Solved: How drill through works with direct query - Microsoft Fabric Community
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! |
Regards,
B Manikanteswara Reddy
Hi. Thanks for providing some tips, but i have tested most of these.
-- Limit the number of visuals on the drill through page to only those necessary. Each visual can generate its own query, so reducing the number can help improve performance.
- The report only have 1 visual, and that is a table. - Use aggregations to reduce the data being queried( I think you already implemented it).
- Main report which drillthrough is initiated from is aggregated data and performing well since that part is imported data. - Instead of page-level filters, consider applying filters directly to visuals where possible. This can help reduce the scope of data queried.
- How can you on a drillthrough put the filters as visual filters? They are automatically assigned to Page level filters. This is the last trick i have tried to sort out, becaus i also think this will solve the problem. But i havent been able to assigned the inherited filters to the visual. - Use Performance Analyzer to check the performance of the queries or visuals.
-Have used it alot, and the problem is not the same as when report is ran through it's embedded frame on the web portal. And Embedded generates another set of SQL sent to the server if the report is ran throught web portal(not PBI Service)
So unless i am able to remove that generate a "matrix" result for data which is not relevant, i-m not sure how to solve this.
Hi KjetilAB ,
Yes, you're right,
- When a drill through is performed in Embedded, filters are applied at the page level, and the engine attempts to generate visual interactivity metadata upfront, leading to queries that include all potential values , this might explain the huge matrix query you're seeing.
- Desktop/Service can optimize better for interactive sessions, often skipping redundant metadata generation when the user context is clearer.
While drill through filters are automatically applied at the page level, you can try this workaround:
- Create a manual parameter or disconnected table that represents the drill through filter values (e.g., Customer ID or Transaction ID).
- Bind that field to your main table visual via a calculated table or DAX measure that uses SELECTEDVALUE() from the parameter table.
- When initiating the drill through, pass the filter into the parameter table instead of relying on page-level filters.
- Then manually filter the visual using a DAX measure that restricts data based on that parameter, avoiding page-level filtering altogether.
Like below:
Filtered Transactions =
FILTER (
'TransactionTable',
'TransactionTable'[CustomerID] = SELECTEDVALUE('DrillthroughParameter'[CustomerID])
)As a more architectural approach, consider breaking the drill through page into a separate report, and link to it through a URL with query string filters. This avoids embedding the filters in the page model and allows the separate report to start clean, filtering visuals directly. You can preload only what’s needed, improving performance drastically for Direct Query scenarios with large datasets.
Since this behaviour is specific to Embedded, and you’re observing material differences in SQL generation compared to Service/Desktop, this may be a case where Microsoft support should be looped in , especially if the query structure generated by Embedded disregards visual/page filter scope. They can assist with telemetry traces and possibly flag it for engineering.
https://learn.microsoft.com/en-us/power-bi/support/create-support-ticket
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Regards,
B Manikanteswara Reddy
Hi KjetilAB ,
We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Please don't forget to give a "Kudos " – I’d truly appreciate it!
Regards,
B Manikanteswara Reddy
- Limit the number of visuals on the drill through page to only those necessary. Each visual can generate its own query, so reducing the number can help improve performance.