PowerPivot files loading very slowly

[DWig]

This one is a more theoretical question than my usual nuts-and-bolts questions.

I have noticed that the vast majority of my files which use PowerPivot load very slowly - sometimes up to several minutes for a 50 MB file. It is still a better financial forecasting engine than arrays/sumifs because the calculation time when data is refreshed using PP and cube functions is orders of magnitude faster than the refresh using an access query and arrays (a minute and a half versus an hour and a half). File size is also significantly smaller (40% or so).

The odd thing I've noticed is even with a smaller file size, the file takes a lot longer to load. Has anyone else noticed this? Am I perhaps structuring something incorrectly?

Just curious.

DWig

 

[DWig]

Bump. Has anyone seen this? Might it have something to do with versions of PowerPivot not being the same as versions of SQL Server? Our IT department thinks that might be a cause.

 

[StrafeXL]

This is really for file compression, but it's worth a shot: Download Workbook Size Optimizer from Official Microsoft Download Center

 

[miguel.escobar]

The upload process depends on the driver that you're using more than anything. I did a test like 8 months ago where I tried uploading the same table from an Oracle 11g database with an I dont remember what driver and the same table (from the same computer) but on a SQL Server and the SQL Server was around 20-30% faster.

If you're trying to upload a csv or a txt file then it must be something with your own computer resources (RAM).

Hope this helps but its one of those case-by-case scenarios.

 

[DWig]

Thanks, Miguel. What I'm really seeing is an issue where the file itself loads very slowly. Any communication with the server once the file is loaded is actually pretty speedy. I'm going to do some "surgery" on the file and bring it over into a SQL 2008 PowerPivot version (currently running PP for SQL 2012, and our server is SQL 2008). I'll post an update once this is finished.

 

[DWig]

Strafe, thanks for that suggestion. I'll do some research and might execute this. I'll let you know how it works.