With SQL Server 2012 we have a new way of working with Analysis Services. The Unified Data Model (UDM) in SQL Server 2005 and 2008 will be replaced by Business Intelligence Semantic Model (BISM) which is intended to bring together the relational and multidimensional models under a unified BI platform. BISM supports both traditional MULTIDIMENSIONAL models using the MULTIDIMENSIONAL ONLINE ANALYTICAL PROCESSING (MOLAP) storage engine and TABULAR models (relational data model consisting of tables and relations) using the Vertipaq engine.
The traditional MOLAP engine is optimized for OLAP using techniques such as pre-built aggregates, bitmap indexes and compression to deliver great performance and scalability. The Vertipaq engine is an in-memory column store engine that combines data compression and scanning algorithms to deliver fast performance. It does need any indexes or pre-build aggregations. Also, since all the aggreegations are done on the fly in memory, it also avoids costly I/O reads from disk storage.
Vertipaq engine (aka in-memory columnar database or column store engine) means that instead of considering rows of a table as a main unit of storage, it considers every column as a separate entity and data for every column in stored independently. Vertipaq has a very special storage architecture. For each column a dictionary of distinct values is created. The actual data in each column is stored as pointers to the dictionary. These pointers are bitmap indexes that reference the dictionary. Both the dictionary and bitmap indexes are highly compressed and are stored in memory and hard disk. When you are working with a tabular model, all the data is loaded into memory. The data on the hard disk is for backup purposes. The special storage architecture and data compression makes columnar storage ideal for ad-hoc reporting and analysis as data reads are blazing fast.
With all the benefits of columnar storage, there is an imporant catch. Columnar storage and its dictionary plus bitmap index architecture makes it very fast to query data for a single column. Querying multiple columns for a single row can be a computationally expensive affair. Also, columnar storage performs much better if the number of unique values in the dictionary is not too large. If you have large tables with high cardinality, reading such data could be very slow depening on the size of the tables, number of columns being read etc.
I highly recommend you to read an excellant article written by Marco Ruso
Optimizing High Cardinality Columns in Vertipaq
Hope this helps!
Republished from Simran Jindal's Blog [4 clicks].
Read the original version here [2 clicks].