Physical Data Warehouse Design - Data Warehouse Systems: Design and Implementation - page 264

7.8.3 Column-Store Indexes
SQL Server provides column-store indexes, which store data by column. In
a sense, column-store indexes work like a vertical partitioning commented
above and can dramatically enhance performance for certain kinds of queries.
The same concepts that were explained for bitmap indexes and their use in
star-join evaluation also apply to column-store indexes. We will provide a
detailed study of this kind of indexes in Chap. 13 .
We now show how a column-store index is defined. For this, suppose there
is a materialized view Sales2012 that selects from the Sales fact table the
data pertaining to 2012. Suppose that many queries request the attributes
DueDateKey , EmployeeKey ,and SalesAmount . In order to speed up access to
the Sales2012 view, we can define a column-store index over it as follows:
CREATE NONCLUSTERED COLUMNSTORE INDEX CSI Sales2012
ON Sales2012 (DueDateKey, EmployeeKey, SalesAmount)
Column-store indexes have important limitations. One of them is that a table
over which a column-store index is defined cannot be updated. Thus, we
cannot define the index over the original Sales fact table since it is subject to
updates and create instead the index over a view.
Bitmap indexes are not supported in SQL Server. Instead, SQL Server
provides a so-called bitmap filter. A bitmap filter is a bitmap created at
execution time by the query processor to filter values on tables. Bitmap
filtering can be introduced in the query plan after optimization, or it can be
introduced dynamically by the query optimizer during the generation of the
query plan. The latter is called optimized bitmap filter and can significantly
improve the performance of data warehouse queries that use star schemas
by removing nonqualifying rows from the fact table early in the query plan.
Note however that this is completely different from defining a bitmap index
like we explained above and which is supported by other database systems
like Oracle and Informix.
7.8.4 Partitions in Analysis Services
In Analysis Services, a partition is a container for a portion of the data of a
measure group. Defining a partition requires to specify:
￿ Basic information, like name of the partition, the storage mode, and the
processing mode.
￿ Slicing definition, which is an MDX expression specifying a tuple or a set.
￿ Aggregation design, which is a collection of aggregation definitions that
can be shared across multiple partitions.