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,