THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

john said:

viva la select *

October 17, 2013 8:56 AM

Leave a Comment

(required) 
(required) 
Submit

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.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement