How frequently should I partition the cube?

Question

Our application has a cube storing historical data from the year of 2000 to till date. Underlying OLTP datacase is update twice a month & the cube is also processed shortly after. Cube is partitioned for every year. Instead of yearly partitioning, if we opt for creating a partition every month will it improve query performance or too much partitioning will create a bottleneck? How much frequency of partition shall we consider to be optimal?

Answer 1

You should see a performance gain if you partition more frequently.

 

The Analysis Services 2008 Performance Guide which you can find here, says:

The partitioning strategy should be based on these factors:

• Increasing processing speed and flexibility
• Increasing manageability of bringing in new data
• Increasing query performance from partition elimination
• Support for different aggregation designs

Regards,

Jason

---

MCITP BI Developer - MCTS SQL Server (http://bichopsuey.wordpress.com/)

Answer 2

• Increasing query performance from partition elimination

How can we explicitely eliminate a partition in our query itself?

Answer 3

Hi Holmes,

Refer to this link which has extensive description on cube partitioning. HTH.

---

Phani Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.

Answer 4

How can we explicitely eliminate a partition in our query itself?

You can't do this explicitly, it is done implicitly by the engine. You can often improve the behaviour of this by setting the slice property on the partition, but you cant specify a particular partition to use in your query.

---

http://darren.gosbell.com - please mark correct answers

Answer 5

You can often improve the behaviour of this by setting the slice property on the partition....

How is this property used?

Answer 6

This post http://sqlblog.com/blogs/mosha/archive/2008/10/14/get-most-out-of-partition-slices.aspx covers off how this is used.

---

http://darren.gosbell.com - please mark correct answers

Answer 7

With regards: "You should see a performance gain if you partition more frequently."

That isn't entirely true. Aggregations can only be created within a partition meaning partitioning by month say means that a query for the year level must query at least 12 partitions/aggregations. Considering parallel querying by the storage engine this may not be noticeable on an underutilised AS server but as the load increases this can soon become a factor. Also note that AS can be overly aggressive when trying to identify the partitions to be included for a given query as the algorithm isn't perfect by any stretch of the imagination. This could be okay in some cases and not in others. Test, test, test...

Bottom line, you need to find the partitioning solution that suits your system's needs. This is generally based on query patterns but can include things like processing performance and system maintenance. If all your user queries are geared around monthly queries then partitioning by month may be more sensible. If users tend to query based on year then maybe that is more sensible. You may need to partition by product category and month or just customer group or...

With regards processing performance note the recommended maximum number of rows per partition. This isn't a hard and fast rule though, just a suggestion. The system I work on has between 4000 and 6000 partitions with some partitions with 80 million rows and some with less than a thousand due to the way we manage the flow of data through the system and performance is fine in the general case.

Also please note the following: http://bi-logger.blogspot.com/2011/02/analysis-services-slicing.html

 

---

http://bi-logger.blogspot.com/