Aggregations

Hi,

designed a cube anr run several queries on it. The cube itself works. Now, to gain the objected performance boost, i worked through aggregationdesigner for every single partition. It told me, that there will be a lot of aggregations with a huge amount of diskspace. After processing full, reports work as slow as before and there is no more space needed in the file system.

How can I find out, if aggregations where calculated and when SSAS uses the precalculated stuff. Where gets the aggregation data stored in filesystem?

How can I find out, if something went wrong while aggregating, so that aggregation wasn't finished?

Thanks in advance

T.

One technique is to view your aggregations is to open your cube project in BIDS (you may need to use an import project). In Solution Explorer, click the Show All button and then expand your cube to see the partition definition file. Right-click that file and select View Code. In the XML displayed, search for <AggregationDesigns> to see the aggregations.

If you have SP2, there is an Aggregation Utility that makes this a bit easier. Appendix C of the Performance Guide covers this in detail. You can get the utility as part of the SQL Server 2005 Samples (Feb 2007 release). A link to both these are below. (The utility is located in the \Analysis Services\Administrator\AggregationManager folder below the installation folder. Read the ReadMe file in this folder for instructions on compiling the utility.)

Good luck,

Bryan Smith

Performance Guide : http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SSAS2005PerfGuide.doc.

SQL 2005 SP2 Samples: http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en

|||

Hi, thanks for your reply.

There is <aggregationdesigns> part in partition file.

I bet the aggregation design wizard did his work to design the aggregations who should be best. But I'm not sure, due to lack of check possibilities and performance gain, that they get processed.

As there is no additional disk space used by AS, I'm really súre that the designed aggregations didn't go to work this morning.

Any hints?

|||

Hi, Bryan,

Are you saying the Aggregation Utility is used to improve the cube aggregation process? or how it works? Thanks.

With best regards,

Yours sincerely,

|||

The agg utility just helps you see and edit your aggregations. This should be a last resort. I recommend you use the wizards for this purpose. (Documentation on the agg utility is provided in the links in the previous posting.)

Please note, if you right-click a partition in SSMS and script it's definition, it will indicate the name of the aggregation design but will not script the design itself. This may incorrectly lead you to believe you do not have aggregations defined.

The easiest way to see the aggregation design is to follow the steps listed in the previous email. The BIDS interface provides fairly simple access to the structure. Alternatively, you can find the design XML file in your data directory folder and read it there. As an example, the agg design for my Adventure Works DW cube's Reseller Sales measure group is located at: C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Adventure Works DW.0.db\Adventure Works DW.0.cub\Fact Reseller Sales.0.det\AggregationDesign.0.agg.xml on my computer.

Please note, that the aggregation design is editable in the xml file listed above as well as the XML displayed in BIDS. However, it is STRONGLY recommended you DO NOT EDIT the xml. Instead, use the agg utility if you feel you must go this route. (Again, make sure you've fully explored the use of the wizards before doing even this.)

Regarding processing, I have seen a couple situations where processing failed and an error was not properly addressed by applications. In these situations, the cube was rolled back to its prior state. If you suspect this is the case with your cube, check the data to insure it is up-to-date. If it is, you probably processed successfully.

Good luck,
Bryan

|||

Hi, Bryan,

Thanks a lot for your very advanced and kind advices. It's very helpful.

With best regards,

Yours sincerely,

Aggregation Issue

I have a cube that I designed aggregation with 12% performance in MOLAP storage mode. However, when I ran query it read from partition not from aggregation.

How can I change so that the query read from aggregation?

Thanks in advance,
A. Imamuddin

Hi Ashari

1. Ensure that you have designed hierarchies on your dimensions even though they seem to be unnecessary. I found that aggregations are created when these exist

2. I find that creating aggregations manually by editing the XMLA for the measure group works better. You do this by scripting the measure group in the SQL Server Man Studio and add/edit your aggregations.

Let me know if this helps?

Thanks

John

|||Hi John,

Thanks for your replay. I would like to inform you, I do not applied point 1 because I have already had hierarchies. I have applied point 2, but after processing the partition, the query still read from partition. FYI, I also design aggregation using Usage Based Optimization Wizard.

Thanks,
A. Imamuddin|||

It's likely that, even after usage-based optimisation, you still haven't build any aggregations useful for your query. Rather than change your query, to make sure you're building the right aggregations take a look at:
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!907.entry

HTH,

Chris

Aggregation Issue

I have a cube that I designed aggregation with 12% performance in MOLAP storage mode. However, when I ran query it read from partition not from aggregation.

How can I change so that the query read from aggregation?

Thanks in advance,
A. Imamuddin

Hi Ashari

1. Ensure that you have designed hierarchies on your dimensions even though they seem to be unnecessary. I found that aggregations are created when these exist

2. I find that creating aggregations manually by editing the XMLA for the measure group works better. You do this by scripting the measure group in the SQL Server Man Studio and add/edit your aggregations.

Let me know if this helps?

Thanks

John

|||Hi John,

Thanks for your replay. I would like to inform you, I do not applied point 1 because I have already had hierarchies. I have applied point 2, but after processing the partition, the query still read from partition. FYI, I also design aggregation using Usage Based Optimization Wizard.

Thanks,
A. Imamuddin|||

It's likely that, even after usage-based optimisation, you still haven't build any aggregations useful for your query. Rather than change your query, to make sure you're building the right aggregations take a look at:
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!907.entry

HTH,

Chris

aggregation design wizard does not design any aggregates

We have designed a fairly big cube - and now we want to add a view aggregates, but the aggregation design wizzard does not built aggregates.

after a view minutes of "Initializing..." the wizard returns with zero aggregates (even if i select 99% performance).

I have already checked some other posts - custom assemblies and so on... but did not find andy hint.

SSAS2005 SP2

Profiler does not show any error...

this may be related to the correct record counts (see post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1329458&SiteID=1)

Thanks for Help, HANNES

Could this be because of the huge amount of attributes in the cube?

- Cube 5 Dimensions

Time: 10 Attributes|||

Its really hard to say exactly why the Aggregation Design Wizard is not building any aggregations. It may be due to the record counts being wrong, the Aggregation Usage property on too many of the attributes being set incorrectly, etc.

I'd suggest you read through the SSAS performance guide as it discusses aggregation design quite extensively and you'll likely have a better idea of what you might need to change in order to get the wizard to consider building aggregations.

HTH,

Dave Fackler

PS. The SSAS performance guide can be found here:

http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SSAS2005PerfGuide.doc

|||

Hey Hannes,

i've got the same Problem since a few days, but without a Solution. I've also a huge Cube with 30 Dims and 23 MeasureGroup. It works fine so far, until this Problem appear. In my Cube there is a second Time Dim, all MeasureGroups without this Dim in Referenz, the Aggregation Design Wizard works.

If i've found a Solution for that Situation i'll write it down.

Kind Regard

Andy L?wen

|||

Hello!

30 dimensions and 23 measure groups, in one cube, seems to me as a design that you can split up into separate cubes in the same project.

And each attribute that you put into a dimension will increase the size of the cube. Count the members for each individual attribute in each dimension and multply them with all the number of members of each other attribute in the same dimension

I do not think that SSAS2005 is designed for this scenario, with all these dimensions and measure groups in one cube.

It would be interesting to know more about the business case behind this design.

Regards

Thomas Ivarsson

|||

Bug? I don't know, it may see your Dimension and see 150 attributes (is that even right?) and...die? That's huge and aggregation's would not even help at all for performance.

Edit: Second thought, that cube IMO really isn't aggregatable, maybe the wizard agrees with me. I suggest turning those attrributes into measures. Only structures should be in the Dimension.

|||

Thomas Ivarsson wrote:

Hello!

30 dimensions and 23 measure groups, in one cube, seems to me as a design that you can split up into separate cubes in the same project.

I do not think that SSAS2005 is designed for this scenario, with all these dimensions and measure groups in one cube.

It would be interesting to know more about the business case behind this design.

Regards

Thomas Ivarsson

I agree, anymore than 10-15 attributes for a dimension and i'd suggest your cube design is off, let alone 200 attributes. Can you give examples of what attributes you have?

|||

Even when you specify 99% the aggregation design wizard will not design 99% of all possible aggregations, it has some pretty smart (most of the time) algorithms which try to figure out how many aggregations would be "reasonable" for the wizard to consider and then it will design 99% of those. So in your case there must be something in your design that is causing the wizard to conclude that there are no "reasonable" aggregations.

The aggregation design wizard relies heavily on the estimated row counts and on the attribute relationships and other settings in your dimensions. The following are some of the factors that can contribute to aggregations not being designed by the wizard.

lack of or incorrectly defined attribute relationships|||

Hey Thomas,

there are many Dims, MG and so on, that's right. But only a few attributes are Aggregateable.

My Cube has a size of 3GB, and we did not have a Problem with Performance. So that is not the Problem.

My Problem is, that the Aggregation Design Wizard will not work this the last change on the Design of the Cube.

But give me some Hours, i think i'm on the right way.

Kind Regards

Andy L?wen

|||

If only a few attributes are aggregatable, then that means that any aggregations that could be built would be pretty large - they will always have to be at the leaf levels of the non-aggregatable attributes. This in turn means that AS is likely to have decided they are too large to be useful (see the AS Performance Guide, P37, on the 'one-third rule') so it hasn't designed any at all. If you do want to built aggregations then you should either build them manually or deliberately set the counts for your attributes to artificially small values.

HTH,

Chris

|||Andy could you please provide an example of attributes you have? I'm still very curious how it's possible for so many to exist.|||

Hi,

About setting the count to a lower value, dont set it too low, because

Another tip which I got from an microsoft insider is that,

the Aggregtion Wizard does not create aggregations if the count is somewhere near 1024 rows (if I remember correctly).

Regards

|||You're thinking of the fact that AS won't build aggregations or indexes on a partition with fewer than 4096 rows, by default.|||

Hi,

Oh! yes, you are right.

I also believe that this threshold value can be set somewhere in a configuration file.

But then how is the number of rows different from the count?

|||

To answer my question... we have selectivly defined some attributes with aggregation usage "non" (for those attributes which are not propably often used) - while we have set half of the attributes to aggregation usage non - the aggregation wizzard has begun to design aggregates...

Maybe there is a relation to the not correct set estimated rows... - as long as I do not find any code to proper set estimeded rows its impossible for me to say whats the reason... (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1329458&SiteID=1)

Best Regards, HANNES