Aggregations Hits

Dear all,

I have a problem with aggregations.

I have created aggregations on my different partitions; on a particular partition the wizard is announcing a performance gain of 44%.

Despite my reports use the measure from that partition when I trace the MDX query execution using SQL Server Profiler I have no aggregations hits.

As I have modified the MDX queries many times I was thinking my MDX queries were poorly written and that would be the cause of the aggregations not being hit.

I have then created a blank report in BIDS and using the Query Builder I have created a simple query using the measure for which the aggregation designer wizard was announcing 44% of perf gain but just as in my reports the SQL Server Profiler shows that there is no aggregation hit at all.

What am I doing wrong? My cube is quite simple and very light, I have one main measure with about 1.3 million rows and the dimensions are quite small but performance is very poor.

Any help will be appreciated.

It could be a number of things. First of all, it sounds like the aggregations you've built aren't useful for the queries you're running - that's not the fault of the queries themselves, it's just you need to build the right aggregations. The term 'performance gain' is a bit misleading in BIDS because it doesn't mean that all of your queries will automatically run x% faster; only queries which can use the aggregations you've built to derive their data will run faster.

Have you read the following white papers?

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssas2005perfguide.mspx

http://www.microsoft.com/downloads/details.aspx?FamilyId=975C5BB2-8207-4B4E-BE7C-06AC86E24C13&displaylang=en

They should give you an idea of what you need to do. It's also possible (especially given the fact that your cube is so small) that the performance problems you've got won't be solved with aggregations. Have you got any calculations defined on your cube?

Chris

|||Ensure that youre aggregations are built, you have been running ProcessIndex?|||Thanks for your answer Chris I'll check these White papers.