MS Analysis Services 2005/2008 Cube Partitioning Utility

Cube Partitioning Utility

A common approach to partitioning MS Analysis Services (SSAS) measure group is to partition by time periods (such as day). With this strategy, adding new partitions on a regular basis is required prior to processing new data. Also, older partitions may have to be removed when historical data is no longer needed. These operations can be automated by using Analysis Management Objects (AMO) or by running programmatically-generated XMLA scripts. Typically, the solution represents either a console application or MS Integration Services (SSIS) package which creates a new partition based off an existing partition.

Cube Partitioning Utility developed by AVM Dynamics Inc. represents a low-maintenance solution that provides completely automated creation and removal of SSAS 2005/2008 database partitions based on user-defined specifications. Functional capabilities of the utility go well beyond traditional "partition cloning" and cover many important yet time-consuming SSAS database maintenance needs:

1. Automated creation and removal of partitions.
In a typical partition creation scenario, most of the properties of a new partition would be inherited from a source partition defined by the user. At the same time, the program allows the user to change partitioning strategy or modify partition properties prior to creating a new partition, to support the most efficient partition configuration. For instance, the user can easily switch at any time from partitioning by month to partitioning by week or by day. The program will automatically recalculate slice value and generate a new binding query. Also, the program supports:
- partitioning by a variable length time period when a new partition may include any number of days (15, 10, 5, etc.), to support the most efficient configuration of the growing SSAS database;
- adding a set of partitions for the entire date range to a measure group in one step, to make a newly created SSAS database (or a measure group) structure up-to-date immediately;
- adding a set of partitions for a new time period where each partition gets a non-overlapping range of distinct count values, as it may be required to support efficient "distinct count" measure group partitioning strategy.

2. Modifying properties of existing partitions.
During SSAS database lifecycle it may be required to modify certain partition properties such as binding query, aggregation design, storage location, etc., without disrupting database access. Such need may occur, for instance, as a result of collecting cube usage statistics, which would allow designing more efficient aggregations and replacing the original ones without re-processing the entire SSAS database.

3. Automated measure group re-partitioning.
If the number of partitions is measured in hundreds, merging of older partitions may be needed to improve SSAS database manageability. Alternatively, it may be beneficial to split newer partitions to improve MDX query response time.

The proposed solution is highly scalable. It does not imply any limitations on the number of SSAS servers, databases, and database objects (cubes, measure groups) that have to be maintained. A single instance of the utility can be used to perform a centralized partition administration in a large multi-server/multi-database environment.

The utility can be easily deployed and quickly customized to a specific user's needs. Instead of developing from scratch entire SSAS database processing system, a relatively short-term work would be required to set-up configuration information.

For additional information about this product, please contact: .