Javier Guillen posted on September 28, 2010 21:48
The new SSAS engine (only currently available to PowerPivot, either as part of Excel or Sharepoint 2010, and shipped along with SQL Server 2008 R2) has a completely different architecture than the traditional disk based storage engine.
As a columnar database, its driving principle is that the variation of data across 1 column is usually low (for example, if a column stores “gender” it only needs to store two values for the entire column). This in contrast to the traditional storage in which values are stored for each row, no matter how much data variance there is (as you can see - when storing columns with high degree of variance, the columnar approach may lose its advantage).
Due to the reduced need to store discrete values (along with other data pattern algorithms), high levels of compression are possible which allows the Excel version of Vertipaq to run in-memory, all based on RAM storage. The huge advantage here over traditional disk based SSAS storage is data retrieval and calculations happen at a much faster rate, as disk I/O processing is omitted.
Given that current personal computers have much higher RAM specifications, it is finally possible to hold in-memory an entire database table representing millions of rows, thanks to the Vertipaq's architecture.
Another characteristic of PowerPivot's Vertipaq engine is that it is an in-process memory storage. That means it is bound to Excel, as they are both the same file. The advantages are it allows portability, as the file IS the database; however, the disadvantage is that any process failure compromises the whole system.
Vertipaq, in its PowerPivot for Sharepoint version, requires a dedicated SQL Server Analysis Server instance which only responds to the PowerPivot system, and as its Excel counterpart, does not store the data in pre-aggregated mode but instead computes aggregations on the fly. Other server functions like data unloading (removing data from cache at specified intervals, if users do not make requests) are also featured in this type of configuration.
Some of the features that were currently left out for version 1.0 of PowerPivot (like many-to-many relationships and dimension hierarchies) are available to the underlying engine, as it really is in fact, a repackaged SSAS instance. Those features will eventually be exposed, in order to make the Vertipaq/PowerPivot system even more robust.
At this point, it is not possible to access PowerPivot OLAP cubes via a programmatic interface (though you can access OLAP cube members via Excel cube functions). Let’s hope the next version will include a way to interact with it (and with Vertipaq) via managed code.