Aggregation Design Wizard: functionality questions

Hi,
As an example, I use the Aggregation Design Wizard to specify MOLAP 20% for a partition - this is based on 20 products, 40 sub-products, 100 sub-sub-products. What if my product dimension table increases substantially in size so that I now have 200 products, 400 sub-products and 1000 sub-sub-products - do I need to rerun the aggregation wizard to maintain this "20% performance gain"? Or does MSAS know that I always want 20% performance gain, and design new aggregations when it sees new dimensions data? The main question is: do I have to keep running the aggregation wizard whenever new data arrives to be sure to maintain my desired level of performance increase?

The next question is what setting is recommended for performance gain: why wouldn't I want 100% performance gain? Is this just a matter of disk storage (which I have plenty of), or could too high a performance gain actually mean decreased performance? (then why call it 100% performance gain? :) )

Thanks for your help!
-cf

I can begin from the 2nd question. which solves your problem. Yes, performance costs disk space. So keep it %100 where it is possible.

If you use the Agg Des Wiz it saves the aggregation amounts. When you run the test it tells you how many aggregations it has designed. And these aggregations are saved and applied every time you process the cube or database.

|||"When you run the test it tells you how many aggregations it has designed" - correct, but what if the underlying data has changed? Won't the number of aggregations designed then vary based on data - so I need to run the Aggregation Design Wizard every time I have new data or it will just use the aggregations from the last time I ran the wizard?
Thanks for your help.
|||

Refer to the Books Online --> Index --> Aggregations --> overview.

Aggregation is precalculated structure. If is says "3 aggregations" This doesn't mean it calculated 3 data. It means it calculated 3 fields in a structure. So no matter if you add new data it will again be calculating that data too for the same aggregation as you process it.

Aggregate() vs. A Set in the WHERE caluse

Hi,

I recently wrote some VBA code for Excel, which allowed a user to specify a list of members from a particular dimension and then produce a report which aggregated some measures with the members of a different dimension on the rows. It did this by creating an MDX query with the listed members fed into the aggregate function and stored in a calculated member which was then added to the WHERE clause.

eg

WITH MEMBER [Dimension].[Selected_Members] As Aggregate({[Dimension].[Member1], [Dimension].[Member2],.....})
SELECT [Measures].[Measure1] ON COLUMNS,
NON EMPTY [AnotherDimension].Members ON ROWS
FROM [Cube]
WHERE ([Dimension].[Selected_Members])

We found that as the number of members specified increased, the report slowed down dramatically. So, remembering that AS2005 allows you to specify sets in the WHERE clause of your MDX we decided to change it to something like this:

SELECT [Measures].[Measure1] ON COLUMNS,
NON EMPTY [AnotherDimension].Members ON ROWS
FROM [Cube]
WHERE ({[Dimension].[Member1], [Dimension].[Member2],.....})

We were quite surprised to see a very significant improvement in speed and the same results.

So, it would appear that the end result is exactly the same, but I would like to understand what the difference is and why one is so much faster than the other.

Many thanks,

Stuart

|||

I am surprised to see performance difference between these two examples. I would think that the execution plans should be exactly the same. Perhaps there is something going on in the cube.

Please note, that Aggregate cannot always be exchanged to set in WHERE clause, the results could become different. But if the set to Aggregate over is as static as in your example, it should usually be a safe rewrite.