Partitions in Analysis Services – SQLServerCentral

Posted by: Lava Kafle

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.

Partitions in Analysis Services – SQLServerCentral was last modified: February 12th, 2015 by Lava Kafle

Blog Comments

  1. Raj Kishore

    It is so grace of your write-up Mr. Lava Kafle. Your research on Partition mesmerized me; you look so near to perfection. You know for the fact partition never works, we have HISTORY speaking about the truth and the truth can not be malicious. Have a look at the HISTORY of partition took place in our neighborhood.

    Ek Din Bik Jayega Mati Ke Mol
    Jag Me Rah Jayenge Pyare Tere Bol (Only Soul is Immortal and nothing else)

Post Your Comments:

Your email address will not be published. Required fields are marked *