Newbie Question

[eldreams]

I have recently undertaken a project at a company with limited IT resources. The project is for measuring turnover for the company. I inherited the data that they used which was stored in an excel spreadsheet. 3 worksheets - one with active employees, terminated employees, and the last for reinstatements. Each of these tabs has a combined total of around 700,000.00 rows. Each sheet also has around 10-15 columns with several formulas. Searching around I stumbled upon PowerPivot and have been using the tool. I've converted all sheets into tables, made my PowerPivot connections, and created my relationships.

Because data is being imported into the spreadsheet on a weekly basis and the spreadsheet is growing, we are running into performance problems.

1. The spreadsheet is over 135MB
2. Users are complaining the spreadsheet takes forever to open.
3. I've noticed powerpivot freezing a lot during refresh/update
4. Its a 32bit install of office (2010) at our company

Does anyone have any suggestions on what I should do? I know very little about SQL server and as I had mentioned earlier our resource department is very limited to the point I'm on my own to figure this out. At this point I know I should probably transfer the data into some type of database structure, but it's a lot of data to migrate. There is also a lot of complex excel formulas they have in here. I would like to maintain the excel structure if at all possible, but if someone has any suggestions on how to improve performance or would like to point me to the right direction I would be very grateful.

 

[Rosen]

You might want to try exporting relavent data into another file for user access/reading. This only really works if the users do not need to manipulate the values in the spreadsheet though. Or if they do, setup a batch file system, they export data from a secondary source and the data is collected and uploaded into the spreadsheet overnight.

Hope this helps!

 

[MD610]

Have you tried making external connections to the excel data from PowerPivot as opposed to creating a PowerPivot workbook in the same file as the source data?

It sounds like you have just used the option to create linked excel table in PowerPivot. You could also try just opening a blank excel file and then making an external connection from PowerPivot to the original excel file that contains the data. From a PowerPivot standpoint it would work the same.

The PowerPivot window is kind of like a database within excel that stores its own copy of the data. I know for sure that this is the case when you connect to an external database like SQL Server or Access. A full copy of the data is actually imported into PowerPivot. While I don't know for sure, I would assume that the linked table connection from the excel worksheet is doing the same thing. In essence, you are getting 2 copies of your data in the file. One is the original data in the excel worksheets but then there is also the copies of data that are imported into the PowerPivot engine. If you start with a blank excel file and just import the data to PowerPivot from an external connection to the other excel file, you will only have a single copy of the data in the PowerPivot engine and your worksheets will be blank. It should greatly reduce the size of your file. If I am wrong on this and PowerPivot treats the linked excel tables differently from the externally connected data, someone on here with more technical knowledge about the inner workings of PowerPivot will have to correct me.

The other thing I might suggest is to not import fields into PowerPivot if you don't actually need them for your analysis.