PowerPivot: remove useless columns!

I teach to my students always to remove all useless columns from PowerPivot data models, because they simply waste precious memory. While I was working on a test model with 50 millions of rows, I did the naive mistake of loading the complete table in PowerPivot. Then I saved the file to check the compression of Vertipaq and widened my eyes when I saw a SQL table of 1.6Gb resulting in a workbook of 1.3Gb. It seemed to me that data compression was simply not working.

Checking better, I discovered that the fact table contained a PK INT IDENTITY, which I stupidly loaded inside the PowerPivot data model. Simply removing that column (which has 50 millions distinct values) resulted in a 490Mb workbook, i.e. 1/3 of the original size. Much faster to open, save and work with.

The lesson to remember is: SELECT * is the evil in the world of SQL and is still the evil in the world of PowerPivot. Always check for the presence of useless columns since, when present in a big fact table, they can really change the user experience with the workbook. Needless to say, publishing such a workbook on SharePoint and wasting 1Gb of RAM of the server is a crime that should be properly punished! Sorriso