One thing I learnt in my early days of PowerPivot was that you should never delete an imported column from a table in PowerPivot. The rationale was that PowerPivot compresses the columns during import, so if you delete them after import, you will lose some of the compression benefits. However I am a sceptical type and I need proof before I believe such things. You see when I delete a column from PowerPivot, something seems to happen in the background. I wondered if this was PowerPivot running the compression process again in the background to re-optimise the data.
Enough said – time for a test. I set about testing this theory in both Excel 2013 and Excel 2010.
Methodology for the test
I used the Adventure Works Database from Microsoft and imported 2 tables that were logically joined (Products and Sales Detail). The sales detail table contains 121,000 rows of data.
- First I imported these 2 tables into a clean Excel workbook and saved the file.
- I then took 2 new copies of the file, 1 for the “deleted column” test and 1 for “Re-imported column” test.
- I opened the deleted column version and then used Scott’s version of Kasper’s VBA code to find the column taking up the most space – it was Sales[ROWGUID].
- I then manually deleted this column (opened the table in PowerPivot, right clicked on the column heading and selected “delete column”.
- I saved the file and closed.
- I then opened the version “re-imported column”, went to table properties to open the import wizard, and then “deselected” the same row, pressed OK, saved and closed.
Once finished, I then repeated the test a second time and instead of deleting the largest column, I deleted the second largest column to see if that had an impact.
And the results are in…
Test results – Excel 2013
As can be seen below, the original file size (reimport test 1.xlsx) was around 7.2MB. When I completed the “delete” and “deselect” tests, the file size was reduced to less than 3.1MB in both cases. In fact the deleted column version was 30KB smaller.
When I repeated the test with the second largest column in the model Sales[OrderID], the file size was reduced to 5.5MB in both cases.
Test results – Excel 2010
I repeated the test in Excel 2010, I got essentially the same result.
Conclusion
So in conclusion, I found no evidence that there is any problem with manually deleting a column in PowerPivot vs running the import wizard again.
