Expected Compression of PowerPivot Data (From SQL Server)

[TerryHogarth21]

My setup is Windows 7 64 bit Machine and Office 2013 32bit. I have PowerPivot installed and have written a query against SQL Server 2014 that returns 100,000 records by 20 columns.

The joins are pretty complex so I just did it via a query rather than import the tables into PowerPivot and build the relationship there etc (Not sure if there is a benefit). In any case, when I get all the results and then create a PivotTable based on these 100,000 records - I was hoping the Excel file would not be too large based on stuff that I've read online for PowerPivot compression of data etc.

The Excel file with the connection and only 100,000 records is 20MB. If I extrapolate that and the query brings back 2 Million records, I would think that Excel would be unstable by then. Is this normal for compression or did I miss something in the process? Thanks

 

[James02]

I'm no pro at all, but that file size sounds about right because I deal with records around that size often. I know Excel can handle up to 2GB with 64 bit, but not sure about 32bit. My answer probably doesn't help you though, sorry.

 

[Matt Allington]

In Power Pivot, compression is all about 2 things. How many columns there are in a table and how unique the values are in those columns. If you have a table with 1 column that has 100 million rows and 2 possible values (eg 1 or 0), the file size will be microscopic. If you have the same table and instead of 1s and 0s, every value is unique (as with an ID key), the file will be massive. As you add addition columns, the file size will likely increase exponentially, so keep the number of columns in your large tables to a minimum

So it depends on your data. Generally you want a star schema structure and where possible you should use integers for your ID keys.

i have workbooks with 40 million rows of data that are 200MB.

 

[TerryHogarth21]

In Power Pivot, compression is all about 2 things. How many columns there are in a table and how unique the values are in those columns. If you have a table with 1 column that has 100 million rows and 2 possible values (eg 1 or 0), the file size will be microscopic. If you have the same table and instead of 1s and 0s, every value is unique (as with an ID key), the file will be massive. As you add addition columns, the file size will likely increase exponentially, so keep the number of columns in your large tables to a minimum

So it depends on your data. Generally you want a star schema structure and where possible you should use integers for your ID keys.

i have workbooks with 40 million rows of data that are 200MB.

Thanks for the write up on this. All the joins I have are based on integers rather than text strings - resulting in about 20 columns.

By chance do you know how many columns is in your 40M row 200MB file? Is the file stable at 200MB?

 

[Matt Allington]

My data file is about 8-10 columns, plus 4 lookup tables with many more columns than that. Wide lookup tables are not really a problem, as long as they are not too long. My longest lookup table is 17,000 rows. Yes the file is very stable. Your problem will not be millions of records, but it will be 32 bit Excel. For large files, you really need to make the switch to 64 bit.

Take a look at Scott's improved memory analysis tool. Run this over your file - I am sure it will be very insightful

What is Using My Power Pivot Memory? | Tiny Lizard

when I did this on my file, I immediately realised I had columns for sales inc tax, sales ex tax, and tax. Technically I only need any 2 of these, and the third can be extrapolated. Deleting sales incl tax saved about 40MB from memory

 

[TerryHogarth21]

My data file is about 8-10 columns, plus 4 lookup tables with many more columns than that. Wide lookup tables are not really a problem, as long as they are not too long. My longest lookup table is 17,000 rows. Yes the file is very stable. Your problem will not be millions of records, but it will be 32 bit Excel. For large files, you really need to make the switch to 64 bit.

Take a look at Scott's improved memory analysis tool. Run this over your file - I am sure it will be very insightful

What is Using My Power Pivot Memory? | Tiny Lizard

when I did this on my file, I immediately realised I had columns for sales inc tax, sales ex tax, and tax. Technically I only need any 2 of these, and the third can be extrapolated. Deleting sales incl tax saved about 40MB from memory

Thanks for the post - that was extremely helpful.
Interesting that you can utilize a query to send to the power pivot model and retrieve info back as a record set. (Good stuff)

I'm hoping to get a new computer and just utilize all 64 bit office applications soon. Would be interesting to see the difference in compression and size in 64 bit vs 32 bit Excel utilizing PowerPivot.

 

[Matt Allington]

There is no difference in compression between 64 and 32. What is different is the capacity to process the data on the fly. 32 bit Excel has a 2GB limit on access to memory. 64 has no limit - it can access all available memory. 64 sounds twice as good as 32, but in reality it is exponentially better.