Report Portal

SQL Server Analysis Services Partitions

Like relational partitions, SSAS partitions can be very useful in easing operations and, sometimes, improving performance. Like any performance tuning, you really need to know the ramifications of the feature to get the best out of it.

Firstly, partitioning is an Enterprise version, so you will need to have SQL Server EE to make use of it. Secondly, performance doesn't just improve by virtue of having partitions. It depends on what is in the partitions, how many there are and how they are processed/queried. SSAS knows the low and high dimension key attributes for each partition. So if you have partitioned on date, and your query is looking for a particular date value (or range) SSAS will only query the partition (or partitions) relevant for your query. Will your query run faster with partitioning in this instance? Maybe, but not necessarily. I would generally not anticipate an improvement in this scenario, unless there is some caching effect. For example, if the current month (partition) is the "hottest" it will tend to stay in the data cache at the expense of the less queried months. Note, this tends to happen in a large cube without partitions.

Read more...

Tags: design, partition, management

 

2007-2015 VidasSoft Systems Inc.