Partition Key Selection guidelines in MicroStrategy

Partition Key Selection guidelines MicroStrategy

The partition attribute is typically dictated by specific application needs. Below are some general guidelines for identifying a good partition attribute.

• Some of the largest fact tables in the application are typically good candidates for partitioning and thus influence the choice of the partition attribute. 
• Data should be partitioned in such a way that it allows for the most number of partitions to be involved in any question that is asked of the application. Attributes that are frequently used for filtering or selections do not make for good partition attributes.
• The partition attribute should allow for near uniform distribution of data across the partitions, so that the workload on each partition is evenly distributed.
• To support best dashboard execution and concurrency performance, MicroStrategy has chosen to limit the number of logical CPUs engaged for any single grid evaluation to 4.
• Columns on which some of the larger tables in the application are joined make for good partition attributes.
• Typically, the number of partitions should be equal to half the number of logical cores available to the PRIME server. This maximizes CPU usage to offer the best possible performance during cube publishing. 
• Each partition can hold a maximum of 2 billion rows. Define the number of partitions accordingly.
• The minimum number of partitions is dictated by the number of rows in the largest table divided by 2 billion, since each partition can hold up to 2 billion records. The maximum number of partitions is dictated by the number of cores on the box. The number of partitions should typically be between the minimum and maximum, and closer to half the number of logical cores.
• In some cases, it is possible that a single column does not meet these criteria, in which case either the dataset/application is not a good fit for partitioning or a new column needs to be added to the largest table.