1 hour timeout for data imports in Powerpivot for Excel
This article describes an issue you may face when your data import in Powerpivot for Excel takes longer than 1 hour. Typically you should not run into this type of issue because Powerpivot for Excel was designed as an interactive tool with moderate amounts of data in mind. 1 hour should give you ample time to import all the required data from a given data source.
However, if you are using queries that take a long time for execution or queries that deliver very large result sets, possibly in combination with a relatively slow provider/driver, then you may run into this limit. Typically we have seen this type of problem when trying to import large amounts of data (> 10 million rows) from an instance of Analysis Services. The recommended solution would be to import leaf level data directly from the original data source (typically a relational database). Imports from relational databases will usually have a much better performance and you can avoid the 1 hour timeout.
The reason we have this timeout limit is easy to explain. Powerpivot for Excel makes use of an embedded SSAS instance . It comes with basically the same server configuration properties that you may already know from working with regular SSAS instances. One of these properties is called ExternalCommandTimeout and it has a default setting of 3600 sec = 1 hour.
From Books online documentation:
ExternalCommandTimeout
An integer property that defines the timeout, in seconds, for commands issued to external servers, including relational data sources and external Analysis Services servers.
The default value for this property is 3600 (seconds).
For regular SSAS Instances you can modify the SSAS instance properties (including timeout values) via the SSAS instance properties GUI in Management Studio or by editing the server configuration file "msmdsrv.ini" directly. In the Powerpivot for Excel scenario we do not expose the (embedded) SSAS instance properties for modification. And we do not support changing the default values. Thus Powerpivot for Excel has a fixed data import timeout of 1 hour.
Please be also aware that you may not even notice the failed data import when using an older version of the OLED provider for Analysis Services for data import. With the older provider versions the data import was also stopped after one hour, but it was reported as successful.
See KB 2761400 for details:
2761400 FIX: An incomplete data import is reported as successful after a time-out in SSAS 2008 R2 or in SSAS 2012
https://support.microsoft.com/kb/2761400/EN-US