Power Pivot: "Refresh All" in Data model very slow and stuck after "Success"

[magemaester]

Hi excel gurus,

I'm using Power Pivot at the moment to load a lot of data from 6 Access databases into the Data Model, which are all linked to each other via a sequence of database relationships that I have established. (According to the definition of a Star Schema of database design, of the 6 databases: 5 contain different types of data, whereas the 6th one contains the unique keys of each record that links everything together). All in all there is about 4 million rows of data totalling 1.8gb in size (in Access).

When I import the data it is fine (it is slow, but gets the job done eventually).

However, as I attempt to "Refresh All" in the Data Model, I get the following screen (see picture). The window displays a "Success" message after running for a long time indicating that all of the data has been successfully transferred from Access into Power Pivot, however, the status bar at the bottom of Excel gets stuck afterwards at "Excel PivotTables and related objects are being updated from the Data Model......." Initially my PC whirs alot (which I presume its working on whatever it is working on), but after about 30 minutes of waiting at this "Success" screen the PC becomes quiet and Excel no longer responds. The UI freezes and nothing moves.

Does anyone know why this happens/suggestions to fix? (I'm hypothesizing that it might be updating the 90~ relationships that I have created between all the queries I upload.)

 

[Matt Allington]

Are you using 64 bit Excel? If not, you should. The data is loaded into memory and then compressed. Your load seems complex, and will struggle it you are using 32 bit

 

[magemaester]

Hi Matt,

I'm using a 2016, 64 bit version of Excel on a Lenovo x250 (8gb ram).

When the Data Model loads in PowerPivot, my memory in the Task Manager increases to 71% usage (i.e. reading the data from the hard drive into the RAM), and when I run the "Refresh All", this ramps up to 91-95%. My hard drive then goes into full throttle

My hypothesis is that there is a bottleneck somewhere, either in the Memory or Disk, but I'm not able to pinpoint which one it is. If it is memory related, I'm not sure if it's a RAM issue or a Cache issue (which I don't know how to diagnose).
​

I'm able to upgrade my machine, but would like to know exactly where is the bottleneck happening....

Any advice appreciated, thanks!

 

[Matt Allington]

OK. Chris Webb https://blog.crossjoin.co.uk/2018/0...of-csv-and-excel-data-sources-in-power-query/
and Imke Feldman https://www.thebiccountant.com/2018...ffer-your-tables-in-power-bi-and-power-query/

are the pros.