Manjunath Ram Venkat

Welcome to my Blog… On PowerPivot, SQL Server 2012, Business Intelligence, MSBI

Excel Vs PowerPivot Difference in Sizes of Files

Leave a comment

This is the First Blog in my life.

Well I must be using Excel 97 since 1998, when I was in my 10th Grade, Since then I always wondered, why would Excel need so many columns, is there some one in the world who is really going to use so many columns.

Around 2002, when I first came across DBMS, and started creating tables, felt why should I Set Datatype for each field cant Database analyse the Data and assign the database itself.  However in this regard Excel was quite compromising for some extent.  Then I learnt the difference between file Database and Server database around 2004.

Now Especially given the fact that many Databases would themselves restrain providing on an average 1024 columns.

And our Gentleman Spreadsheet Excel 2010 Started giving 16,384 Columns.

Now in 2011, when I first saw the SuperMan PowerPivot, My god He started storing Millions of records with some Mega Bytes.

Then somewhere I happened to read that PowerPivot, Stores its Data in columnar fashion  which is why it takes lesser space than that of MS Excel.

Ok, I thought let me see would Excel also have that behavior, I prepared two Excel Files

1. Only One Row with Values Filled in each Cell, So I could fill from 1 to 16,384. And Saved it. (Saving the Values in Columnar Fashion)

2. Only One Column with Values 1 to 16,384. (Saving the Values in regular Fashion as Rows).

Guess what First Excel File

So there is a difference on 32 KB, for Just One Column with Limited text in it.

Now I got the secret behind PowerPivot File Storage Magic.

Advertisements

Author: manjunathrv

aah Just another Developer...

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s