Creating Analysis Services Cubes - SQL Server Analysis Services 2012 Cube Development - page 108

Defining partition slice
As mentioned earlier, Analysis Services can intelligently decide which partitions to
check for each query. For example, if we submit a query requesting reseller sales
data for year 2005, SSAS should only have to check the Reseller Sales 2005 partition
and not spend any time examining datafiles for 2006 or any other partitions. To ex-
clude irrelevant partitions, Analysis Services checks the index files found within each
partition folder; these files contain the range of internally assigned key values. You
have already seen that we can partition measure groups by date; indeed, the major-
ity of SSAS projects are partitioned by one of the levels found in the date dimension:
day, month, quarter, or year. However, sometimes partitioning only at the date level
is insufficient—you may have large volumes of intraday data that represents only a
small portion of the total daily volume. In such case you can create partitions based
on multiple attributes, for example, we could partition reseller sales data by date and
product category. According to SSAS documentation, when using MOLAP storage,
SSAS should always exclude irrelevant partitions, even when data is partitioned by
multiple attributes. If you use ROLAP, on the other hand, data remains in the relation-
al tables; to help SSAS query only the relevant partitions, we must define the partition
slice property. Through practical implementation of SSAS projects on various versions
of the product, I also recommend setting the slice property on MOLAP partitions be-
cause it is essentially a query hint for the SSAS engine. Additionally, the algorithm
for detecting the correct partitions is particularly susceptible to errors when you par-
tition data by multiple attributes. Another advantage of setting partition slice is that it
prevents loading incorrect data into the partition—if you attempt to process a partition
with an incorrect query definition, processing will fail with an error stating that the cri-
teria specified by the partition slice have been violated.
How to do it...
You can set the partition slice through SSMS or SSDT. Keep in mind that you must
process the partitions after setting its slice. Even if you have previously processed a
partition and later need to update the slice, you will have to re-process it after the slice
property is set.
Refer to the following steps to set the partition slice:
1. Right-click on the partition and choose properties .