PowerPivot import stops after 3600 seconds (or 1 hour)

Applies to:  SQL Server 2012 PowerPivot for Excel 2010 and PowerPivot add-in in Excel 2013.

To ensure that queries do not run on the server indefinitely, PowerPivot shuts down long-running processing queries after one hour (3600 seconds). The timeout is built into the application and cannot be changed; import stops even if there is more data to bring into the model.

Some data sources are more likely than others to be affected by the timeout. For example, when querying Analysis Services database, both query execution and data read operations must complete within the timeout period. For other data sources, such as querying a SQL Server relational database, only the query execution is subject to the time out. Reading the data into the model is treated as a separate step after the connection is closed.

Once PowerPivot begins adding rows to a data model, no errors are reported when a timeout occurs. However, if you are using SQL Server Profiler to monitor the import, you’ll see this message: “XML for Analysis parser: The XML for Analysis request timed out before it was completed” in the trace file.

If you suspect that import is not completing with a one-hour time-frame, you can work around this limit by importing individual tables and then relating them manually in the model after the import is finished.

Alternatively, apply a filter to import a subset of the rows, and then merge the rowsets later. To learn more, see http://www.contextures.com/PowerPivot-Identical-Excel-Files.html