This chapter is from the book
This chapter is from the book
This chapter is from the book
World-Class Data Compression
When you are creating PowerPivot data sets, all of the data is loaded into computer memory. When you save a PowerPivot workbook, the data from the imported text files is stored in the workbook. Amir Netz from the PowerPivot team came up with an impressive methodology for compressing the data.
Figure 2.28 tells an impressive story. This chapter mashed up a text file that occupies 58MB. It also incorporated the StoreInfo.xlsx file which takes up 19K. All of that data is stored in the StoreMashup.xlsx file in an amazingly small 3.6MB file (see Figure 2.29).
){kind=link}
)
Figure 2.29 58MB of text file fits in a 3.6MB Excel file.
Storing Data Columnwise
When you are storing a database on a medium like a hard drive, it makes sense to keep each record together. Because hard drives are relatively slow, you want that data stored in a contiguous block as shown in Figure 2.30.
)
Figure 2.30 In a traditional disk-based database, all of the fields for one record are stored in close proximity to each other.
The problem with storing data in a row-wise fashion is that there is little opportunity for compression. In the entire 1.8 million rows of the text file, there are no records that match 100%.
Early on, the PowerPivot team decided that PowerPivot was only going to be dealing with data in memory. Thus, they don't need to store data in a row-wise fashion.
When you start to look at a single column of data, there are many columns with huge amounts of duplicates. For example, 1.8 million rows of category information really only has nine possible values. There are massive numbers of adjacent values that are exactly the same see Figure 2.31).
)
Figure 2.31 Look at one column of the data, and there are many possibilities for compression.
If you sort the data by category, you really end up with huge rainbow stripes of records that fit in each category (see Figure 2.32). The space to store the entire column of 1.8 million category values comes down to 9 pointers that say everything from this record to this records is "Jewelry."
)
Figure 2.32 Conceptually, PowerPivot is reducing the data to rainbow stripes of different values in each column.
Microsoft calls this VertiPaq compression. (That is kind of a catchy name, isn't it. Vertical packing of the data. Geeky.)
This is why PowerPivot does not do well with long text values in a column. If you have a comment field, remove that field as you are importing the data to allow PowerPivot to do its full compression.
I hate to extrapolate, but if the file size limit is 2GB on disk, and if you could somehow keep up the 16X compression, then that is a theoretical maximum of 32GB of data in a single pivot table. That would be 993 million rows of demo.txt type data. You would have to own a serious computer. 64-bit Windows and 64-bit Office to be sure.
Most of us were limited to 65,536 rows just a year or two ago. Now the simple demo file for this book is 27 times larger than that limit, with a theoretical upper limit that is 15,000 times larger than 65,536 rows. I would gladly personally shell out the full purchase price of the PowerPivot client to get access to that much analytical power.