I am pretty new to SSAS.
I have a cube (SSAS 2008 R2) that once had aggregations that has been created using the Aggregation Design wizard. When changing schema a few dimensions, i encountered an issue that indicated a field I had removed was being used in aggregations. Thus, my project would not longer build. I searched (even in View Code) for this field and could not find it. Then, perhaps a rookie mistake, I removed the aggregations, thinking I would create new ones once I was able to build the project. However, all attempts to create aggregations fail. The Agg design wizard either creates 0 aggregations for 0% optimization, or it creates some (if i make specific selections in the wizard instead of keeping default choices), but still with 0% optimization. I get the same behavior when running the wizard from Management Studio.
All of the same settings on attributes for OptimizedState, etc, are in place from then the original aggregations were created. I am just now getting back to this after many more changes so simply rolling back is not an easy option.
I even tried creating some manually that I was confident would help. But using Profiler has shown that those aggregations are ignored.
How can I determine why the Agg Wizard is behaving this way? And how can i get some aggregations created?
Thanks in advance.
Best Answer
Alrighty. So, I had inherited this cube from the original architect. I ended up adding a couple data dimensions, adding to the several that were already there. The is data with many different kind of dates so the Date and Time dimensions play a lot of roles. Apparently, i reach a tipping point. Today, after a co-worker created a smaller version of the cube using the measure group I was having issues with and was able to create aggregations without difficulty, i tried something i had not before. In the aggregation design wizard, i set the all but the most commonly used dimensions to None. Thus, I was asking the wizard to create fewer aggregations. I was able to create aggregations with solid optimization without any difficulty.