PowerPivot error external data refresh

[Tom_Mc]

Hi!

I´m having issues with the PowerPivot data refresh which crasch or fails to refresh very often.
Using Excel 2010 32-bit and the latest PowerPivot add-in.

I have built a model which connects to 9 excel files and 4 text files (.CSV) which are all located on a network drive.
One of the text files are quite large, 60 MB and 800K rows.

Very often when I or my colleagues try to refresh we get the error
The operation failed because the source database does not exist, the source table does not exist, or because you do not have access to the data source
or Excel just crash and shuts down.

This error can happen >2-3 times after restarting Excel several times it will eventually be a successful refresh.
This randomness is really what is bothering me. Sometimes it´s sucessful on the first try and sometime I cannot get it refresh and I have to ask a colleague to try.

I´m starting to worry that the 32-bit version and RAM limitations is the problem since the model has grown a little bit during the last months. We did have this error before but not nearly on this high level as it is now. But the error msg don´t really support that theory. I have seen the Client out of memory error a few times but not very often.

It is starting to drive me crazy, any help is appreciated!

 

[olivierhbh]

Hi Tom,

I met a similar problem last week, sending a file with multiple connections (to sql servers however) to a colleague and she couldn't play with it because she was having the same error message you're mentioning, but other people could. One of our IT guys solved it by deducing the powerpivot plugin was corrupt, so he just reinstalled a clean version of it and everything worked perfectly. Costs nothing to try if you didn't already.

Olivier.

 

[butlerrbrian]

I am also struggling with a variant of this problem, but related to Power Query I think. While I see the same error message as above; the details bear out a different message:

OLE DB or ODBC error: The connection could not be refreshed. This problem could occur if the connection was copied from another workbook or the workbook was created in a newer version of Excel. An error occurred while processing table 'Liquidity'.

Setup - Excel 2010 32 bit, Powerpivot 11.2.5058.0, Power Query 2.22.4007.242.