Analysis Services Partitions

[johnem]

Dear Listers,

Initial investigations show that our OLAP cube will source 300 million database rows initially, and incrementally add 2 million rows per week. We're considering the use of partitions to reduce the time required to build the cube, but have no experience in design recommendations or pitfalls of use.

Background:
Our approach is to save the historical data in partitions holding the data from previous *closed* financial years. ie a partitition for each of 1999-2000 , 2000-2001. Hopefully the aggregations for these partitions, although required for most queries, need to be calculated only once (as no new data for these financial years will be added).

There will be an *active* partition to hold the changing & newly created data. This represents data yet to be closed off - it will be larger than the other partititions, holding approx 18 months of data. On a six monthly basis, we'd like to subdivide this partition, creating another *closed* partition and re-create the active partition from the significantly reduced data set.

Questions :
* are partitions reliable?
* is the proposed strategy a reasonable way to approach the problem or is there a more effective way?
* the Analysis Services manual cautions of " incorrect handling of partitions can give the cube incorrect data". Are partitions difficult to use?
* is the cube performance (query response) hindered in any way by this strategy?
* can you build or rebuild a partition independantly of the others?
* merging partitions appears to be supported, is there any difficulty in subdividing partititions?

TIA

John

 

[MDXer]

John,

I would recommend the use of partitions in your case. Partitoins will benifit you both on the process and potentially on the query side. To gain query performance you need to apply a data slice to your partition. The slice tells analysis services what dimension and member the data in the partition represents.

For example you have 4 partitions Q1 02 - Q4 02 you apply no data slice. When you execute a query for Q3 02 Analysis Services will scan all 4 partitions for data. However, if you apply slicing telling that the data in the partition is of Q3 02 when the query is executed AS knows which partition to query.

The danger of this is that your partition can ONLY contain data for this member Q4 data in a Q3 slice will be lost.

You can process partitions independently of eachother or write custom apps to parallel process your partitions.

As for if your strategy is the best there is really no way of knowing without getting into real details of your cube. You may want to look at either Monthly partitions or Quarterly partitions. You may want to consider other dimesnions that you may be able to partiton by.


"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[johnherman]

Questions :
* are partitions reliable? YES
* is the proposed strategy a reasonable way to approach the problem or is there a more effective way? SEEMS REASONABLE
* the Analysis Services manual cautions of " incorrect handling of partitions can give the cube incorrect data". Are partitions difficult to use? NOT NECESSARILY. DO NOT USE AN ANALYSIS SERVICES "WHERE" STATEMENT TO CREATE YOUR PARTITION DIVISION; THAT IS HANDLED AUTOMATICALLY
* is the cube performance (query response) hindered in any way by this strategy? NO, IN FACT, PERFORMANCE CAN INCREASE IF THE DATA REQUESTED BY A QUERY CAN BE IDENTIFIED AS EITHER RESIDING OR NOT RESIDING IN ONE OF THE PARTITIONS.
* can you build or rebuild a partition independantly of the others? YES
* merging partitions appears to be supported, is there any difficulty in subdividing partititions? I HAVE NOT SEEN THIS LISTED AS A CAPABILITY. I SUSPECT YOU CAN'T DIVIDE.



Sometimes the grass is greener on the other side because there is more manure there - original.