"Parallel loading of tables" is not working correctly.
When disable "Parallel loading of tables" in section Data Load in Options, it is working correctly in Power BI Desktop (tables are loaded sequentially in correct order). But after Save as to Power BI Report Server (both in Jan 2021 release), and run scheduled refresh, this behavior not working correctly, tables are still loading parallelly.
This option is importat when is there many reports refreshing many tables, which can overload datasource, and in cases when is important order of loading.
This issue is also present in October 2020 relese (all three builds). Last worked correctly in May 2020 release.
I hate being the bearer of bad news, but I come with some today. This feature was never something that was explictly supported in PBIRS. It worked as sort of a byproduct of how models and refresh worked in the product. However, with the move to models with enhanced model metadata this feature no longer seems to work "for free" and would require some unknown (and more importantly unplanned) amount of dev and test work.
I have added it as a task in the backlog, but your best shot would be to add this to the https://ideas.powerbi.com/ideas/ list as we use this to help rank items in our backlog. Once again sorry to be the one to come with the bad news, but I wanted to be transparent with our findings.
I'm sad of your findings. I understand that due to new format, it needs some additional work and testing for this functionality. It is not priority issue for me, now, because we make some workarounds on source data servers (but it is limiting our performance), to override produced issues of this functional gap. But I hope that it will be solved in some future release, and we can return to performance of pre-october release.
Additionally, I don't think, that it worked byproduct of old model. "Parallel loading of tables" setting, which can be turned on or off, is officially presented in Power BI Desktop, and it works here in current same as pre-October versions. Data refresh on PBI Report Server (pre-October releases) works same way as on Desktop, with respect of "Parallel loading of tables" toggle. PBI Report Server data refresh have some limitations which are documented here https://docs.microsoft.com/en-us/power-bi/report-server/scheduled-refresh , and there is nothing about that "Parallel loading of tables" setting is not reflected on server. But in October release (and later) of PBI Report Server it stopped work, and in this release is not documented anything about removing this feature.
What's more, this feature is important especially on server side. On PBI Desktop, I can easy override, and run refresh selective manually in order as I want, and additionally on PBI Desktop is typically running on some laptop on speed limited connection. There is minimal risk of overload sources. But on PBI RS, there was only one way how to refresh in correct order, and how avoid datasource overload by massive parallel load, by "Parallel loading of tables" setting.
Please consider what next, if return this feature to PBI RS (I will be happy :)) . Or not, but in this case will be better (officially) remove this also from PBI Desktop (there no reason to maintain functionality which is not supported on both PBI RS and PBI Service). Or bring another alternative, e.g. PBI RS specific settings on report Data source or report schedule level.
BTW, Power BI Ideas is good for some public cool features with wow effect like "Option to use either light or dark interface.", and also is it only for mass market Power BI Desktop and Power BI Service. For anything else, especially for technical related features in Power BI RS in enterprise environment, it is black hole.
Maybe one truly "idea", instead of this non-fully functional feature, will be much better bring another better feature.
E.g. possibility to define (if you want) dependency settings between data sources in PowerQuery editor (like to dependency setting between data flows in SSIS). This would enable much more control and power over data refresh proces, include run in correct order, run sequentially or paralely, run conditionally (on success/completed predecessor or on variable) and much more.
Yes, it need little more develompment, but I think, it is good "idea".
@josef78 That is an important issue. If you have a Pro license please submit a Pro support ticket to Microsoft.
Support | Microsoft Power BI . If you cannot do that I'll see if I can reproduce your finding and will raise a ticket myself.
How are you proving that queries run in parallel? Do you use the gateway logs?
I have Power BI Pro, but it is not related to cloud Power BI Service or PBI Gateway. It is related to on-premise Power BI Report Server. Please can you raise ticket internally?
Simple repro steps:
-Use Power BI Desktop RS (eg. January 2021)
-Check "Enable parallel loading of tables" in Options in Data Load section is "Enabled"
-Get data from SQL Server database, and use SQL statement to load data which is loading for some time, you can use statement "waitfor delay '00:00:30';select getdate() timestamp;" to simulate query running for 30second, and also provide some timestamp
-Add second separated data source from same server, you can use same SQL statement
-To report layout add two independent visualization to show timestamp values from both sources
-Run Refresh in Power BI Desktop, tables are loaded parallelly and difference of both timestamps is less then 30s (typically about 1s) = It is OK
-Under Options change "Enable parallel loading of tables" to be "unchecked"
-Run Refresh again, tables are loaded sequentially and difference of both timestamps is greater or equal to 30s (depends on how long is first query running), also timestamp from second source is greater than from first (running in correct order) = it is OK
-Save this report to Power BI Report Server - January 2021 release (PBIRS)
-On report in PBIRS setup data source and scheduled refresh
-Run scheduled refresh, and after finish refresh open report in PBIRS
-Report shows time difference between both timestamp less than 30s, and it is mean it run parallelly, and it is wrong
Check out new user group experience and if you are a leader please create your group!
On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks
Mark your calendars and join us for our next Power BI Dev Camp!