Partitioning is a powerful technique used to enhance performance, scalability, and ease administration of cubes in SQL Server Analysis Services. Despite its benefits, many developers are not familiar with this technique. In this article, we will explore partitions in Analysis Services and answer common questions about them.

What are partitions?

A partition is a physical unit of storage that contains a subset of the Analysis Services database data. By default, each measure group has a single partition that holds all the data. When multiple partitions are created, the measure group represents the combined data stored in all its partitions.

Why should I create partitions?

There are several advantages to creating partitions:

• Partitions can be spread across multiple physical drives, allowing parallel data access. This improves query response time by enabling Analysis Services to access data from multiple drives simultaneously.
• Each partition can use a different aggregation design and storage mode, even if they belong to the same measure group. This flexibility improves query performance by pre-calculating summaries of data instead of computing them at runtime.
• Partitions can be processed independently, saving processing time when data is added to the cube. This is especially useful for yearly partitions, where data in previous years generally does not change.
• Partitions can be processed in parallel, utilizing system resources efficiently. This means that multiple partitions can be processed simultaneously, constrained only by hardware and server workload.
• Analysis Services can extract data from multiple partitions in parallel when executing a query, resulting in efficient utilization of system resources. This significantly enhances query performance as long as there are sufficient processor resources, memory, and disk resources.
• Each partition in a measure group can use a different fact table, even from different data sources. This is helpful when dealing with large fact tables that are physically split into multiple tables.

What is stored in partitions?

Analysis Services stores both detailed (fact) and summarized (aggregated) data in each partition. It also stores aggregation design, proactive caching settings, and other related information.

Are there any restrictions when creating partitions?

Creating partitions requires the Enterprise Edition of SQL Server. The Standard Edition is limited to one partition per measure group. When creating multiple partitions, it is important to ensure that the data in each partition is exclusive to avoid duplicate rows in the measure group, which would produce incorrect results.

Administrative privileges on the Analysis Services server are required to add partitions. By default, local administrators on the machine are members of the Analysis Services Administrators role, which grants them absolute rights to the entire system.

A partition must have the same structure as the measure group, with the same fields and data types. When merging partitions, it is necessary to have the same storage mode and aggregations for the partitions being merged.

How do partitions affect end users?

Partitions are transparent to end users. Regardless of the number of partitions for each measure group in a cube, it appears as if there is a single partition for each measure group to the business user.

When should I create partitions?

As a general rule of thumb, if a measure group has a record count of less than 20 million and there are no performance issues, there is usually no need to create partitions. However, when the record count exceeds 20 million and end users experience slow query response time, it may be necessary to consider creating partitions.

How should I split the data in my partitions?

The most commonly used strategy is to partition by time, but the choice depends on the requirements and needs of the business users. For example, if users seldom request queries that span across multiple lines of service, it makes sense to create a partition for each line of service. Similarly, if certain programs contribute significantly to revenue, creating partitions based on those programs can improve performance.

Is there a wizard for creating partitions?

Yes, you can use the Partition Wizard in Business Intelligence Development Studio (BIDS) to create partitions. The wizard guides you through the process of creating partitions, specifying source information, restricting rows, and configuring processing and storage locations.

Conclusion

In this article, we discussed the concept of partitions in SQL Server Analysis Services. We explored the advantages of creating partitions, how to split data in partitions, and how to use the Partition Wizard to create partitions. By leveraging partitions, developers can enhance performance, scalability, and ease administration of cubes in Analysis Services.