Most people know that the ability to partition a measure group is a feature of Analysis Services Multidimensional Enterprise Edition, but that doesn’t mean that in Standard Edition you are limited to just having one partition per measure group. In fact it is possible to use up to three partitions per measure group in SSAS Multidimensional SE, with some limitations. For a long time I wasn’t sure whether this was legal, as opposed to possible, according to the terms of the SQL Server licence but since this page in Books Online (thanks to Rob Kerr for the link) states that you can have up to three partitions in SE then I assume it is:
http://msdn.microsoft.com/en-us/library/cc645993(v=SQL.110).aspx#BISemModel_multi
HOWEVER
If you do decide to use more than one partition in SSAS SE then you do need to understand the risks involved – and the reason I wanted to write this post is because I see a lot of people using more than one partition per measure group in SE without understanding those risks. Strictly speaking, SE is only designed to work with one partition per measure group. It needs those extra two partitions for two pieces of functionality:
- To support writeback, because using this feature requires SSAS to create a separate partition to hold writeback values
- To support incremental processing, because when you do incremental processing on a partition in the background SSAS needs to create a new partition, process it and then merge it with your existing partition
Therefore if you create more than one partition per measure group in SE you may find that writeback and/or incremental processing will break.
We looked into using the 3 partitions some time ago. Glad we didn’t as it could have caused problems with incremental processing. In the feature comparison across boxes I noticed that SE appears not to support “Linked Measures and Dimensions”. Is that a change from 2005/2008/R2?
No, I think linked objects have always been an Enterprise Edition only feature. They’re not all that useful anyway, so you’re not missing much if you only have Standard Edition!
I don’t think incremental processing is available on SE anyway. So partitioning measure group only add very limited benefit or to me “None at all”
Partitioning is extremely useful when you have larger data volumes (over 30-50 million rows) – it can make processing and query performance faster.