Should I delete or re-import a column in PowerPivot? - Excelerator BI

Should I delete or re-import a column in PowerPivot?

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.

2014-11-21_134013

  1. First I imported these 2 tables into a clean Excel workbook and saved the file.
  2. I then took 2 new copies of the file, 1 for the “deleted column” test and 1 for “Re-imported column” test.
  3. 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].
  4. I then manually deleted this column (opened the table in PowerPivot, right clicked on the column heading and selected “delete column”.
  5. I saved the file and closed.
  6. 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.

image

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.

2014-11-21_134314

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.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top