THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Alberto Ferrari

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

Published Thursday, April 14, 2011 11:58 PM by AlbertoFerrari



john said:

viva la select *

October 17, 2013 8:56 AM
New Comments to this post are disabled

About AlbertoFerrari

Alberto Ferrari is a Business Intelligence consultant. He his interests lie in two main areas: BI development lifecycle methodologies and performance tuning of ETL and SQL code. His main activities are with SSIS and SSAS for the banking, manufacturing and statistical sectors. He is also a speaker in international conferences like European PASS Conference and PASS Summit.
Privacy Statement