Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I have PBI report with 10 000 000 of rows, 300GB. For tests (to exclude relations measures, conditional colmns etc), I left only one raw main table (DataSource) which fills by sql procedure (Source in Edit query -> exec procedure DataSource.
When I exec this procedure directly on SQL it takes abut 4 minutes (10mln of records).
When I refresh report in PBI service, using gateway it takes about 2 hrs (seen in history of refreshing).
When I refresh report in Embeded A1 - it takes 2hrs, the same like in pro space.
I know that in service will be slower (transfer data) but so many times?
Its strange because this rather big report seems to be unused in service as is (pro or A1 in embeded)?
I don't understand where I should to look for?
Any help?
Solved! Go to Solution.
Hi @MarcinSV ,
When the refresh is in fact slow or crash, it can be due to several reasons:
See more details: https://docs.microsoft.com/en-us/power-bi/whitepaper-powerbi-premium-deployment#why-are-refreshes-sl....
If you use DirectQuery mode to connect data, your report performance depends largely on the performance of the underlying data source.
You can optimize your data model using following tips:
For more information on optimizing data sources for DirectQuery, see DirectQuery in SQL Server 2016 Analysis Services.
To minimize the impact of network latency, strive to keep data sources, gateways, and your Power BI cluster as close as possible. If network latency is an issue, try locating gateways and data sources closer to your Power BI cluster by placing them on virtual machines.
To further improve network latency, consider using Azure ExpressRoute, which is able of creating faster, more reliable network connections between your clients and Azure datacenters.
You can learn more via the link: https://docs.microsoft.com/en-us/power-bi/power-bi-reports-performance#optimize-your-model.
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @MarcinSV ,
When the refresh is in fact slow or crash, it can be due to several reasons:
See more details: https://docs.microsoft.com/en-us/power-bi/whitepaper-powerbi-premium-deployment#why-are-refreshes-sl....
If you use DirectQuery mode to connect data, your report performance depends largely on the performance of the underlying data source.
You can optimize your data model using following tips:
For more information on optimizing data sources for DirectQuery, see DirectQuery in SQL Server 2016 Analysis Services.
To minimize the impact of network latency, strive to keep data sources, gateways, and your Power BI cluster as close as possible. If network latency is an issue, try locating gateways and data sources closer to your Power BI cluster by placing them on virtual machines.
To further improve network latency, consider using Azure ExpressRoute, which is able of creating faster, more reliable network connections between your clients and Azure datacenters.
You can learn more via the link: https://docs.microsoft.com/en-us/power-bi/power-bi-reports-performance#optimize-your-model.
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you very much for detailed information!
I have one more question you don't mind, which I don't understand. My laptop is in LAN with SQL from where I take data. Refreshing on my laptop (desktop PBI) takes 1-2hrs. During this I've checked performance my laptop - 50% using RAM, 20% CPU. When I exec procedure exactly on SQL server - it takes 2-4 min. I don't understand why in local there are so huge differents.
"I have PBI report with 10 000 000 of rows, 300GB." - sorry, should to be 300MB
Any ideas?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
50 | |
18 | |
16 | |
16 | |
8 |