VertiPaq Column Store

In SQL 11, the VertiPaq column store that will power the new Business intelligence Semantic Model (BISM) will be delivered in three ways:

1. PowerPivot – in-process DLL with Excel 2010.

2. A second storage mode of Analysis Services that you will get by installing SSAS in VertiPaq mode.

3. A new column stored index in the SQL RDBMS.

The third option picked up my interest. I wanted to know if a custom application will be able to take advantage of these indexes outside VertiPaq. For example, this could be useful for standard reports that query directly the database. It turns out that this will be possible. The following paper discusses columnstore in more details. In a nutshell, SQL 11 will run the VertiPaq engine in-process. Here are some highlights of the VertiPaq columnstore:

• You create an index on a table.
• You cannot update the table after the index is created. Not a big limitation since a data warehouse is typically loaded on a schedule so the index can be dropped and rebuilt at the end of the ETL process.
• The relational engine scans the entire table and loads it into memory in a Vertipaq store. As you could imagine, the more RAM the better.
• Query performance will be great but probably slower than VertiPaq in SSAS.
• Creation of this index isn’t cheap, but the query performance gains should justify using it in common DW scenarios.
• From performance standpoint, it will be best to load data in VertiPaq (SSAS store) instead of using column store.