I've been optimizing many cubes, that got a long time processing. Approximately 20 min per 10 mln rows. I've created partitions and processing became a short - about 4 min per 10 mln. Also I've create one aggregation for all partition with full processing molap and 100% aggregate (cube is not so big). Is there any reason to create aggregation for each partition? Will it work faster when user try to refresh pivot table based on olap cube?
Thanks.
Typically you have one aggregation design shared by all partitions in a measure group. On very large measure groups you might have a second lightweight aggregation design for very old rarely used partitions.
Adding lots of aggregation designs (like a separate one per partition) will likely slow down queries a tiny bit because of all the extra time it takes internally to figure out which aggregation to read from.
If you used the aggregation wizard don't bother. It knows nothing about how you query your cube and will create stupid useless aggs that waste processing time. Instead deploy your cube then go back in a few days after users have run some queries and do Usage Based Optimization instead.
Creating partitions is a good way to improve the cube processing time. Aggregations are useful if done on the correct fields. By correct I mean , the filter selections used most frequently by the users. Usage Based Optimization is good approach to achieve it. Also read through the below article to understand the approach used while checking the performance. https://mytechconnect.wordpress.com/2013/08/27/ssas-performance-best-practices-and-performance-optimization/
