Top Stories

Lava Kafle


Partitions in Analysis Services – SQLServerCentral

You have to have Enterprise Edition to be able to create partitions. Standard edition is limited to one partition per measure group, which is the default behavior.

When creating multiple partitions, you need to make sure that for a measure group, the data in one partition is exclusive of the data in any other partition to avoid duplicate rows in the measure group which will produce wrong results. In short, data in each partition should be unique.

sqlserver creating patitions courtesy sqlserver central .com microsoft tool

To add partitions, you need to have administrative privileges on the Analysis Services server. If you are a local administrator on your machine, then you will be a member of the Analysis Services Administrators role by default. This role has absolute rights to the entire system.

A partition must have the same structure as the measure group; all partitions in a measure group must contain the same fields with the same data types as that of the measure group.

If you intend to merge partitions later (which is very common), be sure to have same storage mode and aggregations for the partitions that you intend to merge. For instance, let’s say you have a measure group to store sales data and in this measure group, you have created two partitions – One partition to store data for the current year and the other partition to store data for all the previous years. At the end of each year, you merge the current partition into previous year partitions and create a new partition for the coming new year. In this scenario you will need to have the same storage mode and aggregation for the two partitions. In short, partitions can be merged if and only if they have the same storage mode and aggregation design.

via Partitions in Analysis Services – SQLServerCentral.