The more complex transformations into the Power Pivot target model are sourced from these embedded Excel worksheets. The best overall performance with Salesforce source data, I get, when I load the initial source data with some minor data preparation into an Excel worksheet first. I’ve really haven’t seen these two so important buttons. Other people have run into the same problem in Excel and also in Power BI Desktop (see here and here), so it looks like this is an important property to change if you have a large number of queries in a single workbook or pbix file. In my case, after I had done this – and with a bit of other tuning using Table.Buffer() – the workbook refreshed very quickly indeed and there was no spike in CPU or memory after a Refresh All. …you will find that the background refresh of query previews stops.
If you then deselect “Allow data preview to download in the background”: …and then going to Current Workbook/Data Load. You can find this option in Excel by going to the Query Options dialog: I found that to prevent this happening I had to use an option that was introduced in Power BI in January 2016 and is also now present in Power Query/Get & Transform in Excel: Allow Data Preview To Download In The Background. So if it wasn’t the queries that were being loaded into the workbook, what was the problem? It turns out it was the queries that weren’t being loaded into the workbook.īoth Power Query and Power BI load previews of the data returned by a query for display in the Query Editor clicking Refresh All in the workbook was obviously triggering a refresh of these previews and this was what was using all the memory and CPU. On further investigation I found that if I individually refreshed the small number of queries that actually loaded data into the workbook, they all refreshed very quickly and with none of the ill-effects seen with a Refresh All. That said, there was clearly something going wrong with the refresh in this case. I’m a big fan of using references to split complex logic up into separate queries, and in this case it was absolutely the right thing to do because otherwise the workbook would have been unmaintainable. To give you an idea of the complexity here’s what the Query Dependencies view looked like: The queries that were being loaded into the workbook were referencing several other queries that in turn referenced several other queries, and indeed there were some reference chains that were over ten queries long. Only a small number of these fifty queries were being loaded into the workbook and none were being loaded into the Excel Data Model. Although all of the data used in the queries was coming from tables in the workbook itself and the data volumes were small, there were fifty Power Query queries and clicking Refresh All resulted in a large, prolonged spike in CPU and memory usage by Excel. Recently I was asked by a customer to do some tuning on an Excel workbook with a lot of Power Query queries in it.
0 kommentar(er)
