Aggregations...

Here is 2 different version's of practically the same aggregation.

See images below please.

Version 1.) Smaller aggregation file size.

Version 2.) Process Index is twice as fast.

Both version's queries take the same amount of time because it seems even though I create two versions of the aggregation, SSAS creates the same aggregation data set. Any idea whats going on here, there's plus and minus' to both versions...thanks.

Version1: http://img405.imageshack.us/img405/7673/vers1og4.jpg

Version2: http://img255.imageshack.us/img255/2488/vers2mo0.jpg

Hate to bump, but no one knows whats going on here? We are using version 2...it processes much faster so we can create more aggregations.

Aggregations in Analysis Services STANDARD edition

I have fields in my cube where SUM is not a good aggregation (rate fields for example). I thought I should just set them to No aggregation, but it tells me that semi-additive aggregations are not available in Standard Edition (?). So, two questions...

1) How do I avoid representing these fields incorrectly in Standard Edition? What aggregation should they be set to? I don't want users to point Excel to them and get this big meaningless number!

2) How/Where do I produce Averages, etc? (I'm thinking that I can only do this in the Report or Excel - that it can't be done in the cube, is that right?)

Thanks for you help...

- Jim

You're correct that Standard Edition does not support semi-additive aggregations. These functions include ByAccount, AverageOfChildren, LastChild, FirstChild, LastNonEmpty, and FirstNonEmpty. If these are the right aggregations to use, you might be able to set up a calculated member that gives you something similar by going to the leaf level and then controlling the aggregation to the level your interested in. If you do this, make the measure you are "replacing" not visible to your users.

Regarding averages, store a SUM and a COUNT measure and do a ratio in a calculated member.

Good luck,
Bryan

|||

If you don't have enterprise edition, then this workaround might help:

http://solidqualitylearning.com/Blogs/dejan/archive/2006/11/08/3439.aspx

or

http://lorentsnv.spaces.live.com/blog/cns!235C78448ABCFC07!134.entry

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.

Aggregations from Middle of Hierarchy

Hi everyone,

Does AS2005 aggregate based on a hierarchy if the lowest grain of the fact is in the middle of the hierarchy?

For example, I have a standard date dimension (with hierarchy of year, quarter, month, week, date). My sales data is at the lowest level (date). I have target sales data on a month level. If I map the target sales data to the dimension with the month ID and query at the year level, will the data return information from the pre-aggregations?

Thanks for any thoughts!

Jessica

If you've actually built the aggregations on your target sales measure group, and you've set up your attribute relationships correctly, then the fact that the granularity attribute (ie Month) is not the same as the key attribute (ie Date) shouldn't matter. But it all depends on whether you've built the right aggregations and set up your attribute relationships correctly... for more information on how to do this, take a look at:

http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!907.entry

and the AS Performance Guide:

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

HTH,

Chris

Aggregations for Max Performance

In the past, I blindly opted for MOLAP and set the percentage to 100% while designing the aggregations for my cubes. Of course, it rarely got to 100%, but I figured that was the best way to get the maximum performance for my cubes in terms of aggregations.

Now fast forward to today, I decided to go through the wizard again because I've recently added a few measure groups and they currently do not have any aggregations right now.

I went through each partition again, opted for MOLAP with 100%, and noticed that most of them went down in percentage. Why is that the case?

Perhaps more importantly, anything "wrong" with my approach of MOLAP 100%? I assume that the only concern I have with doing so is the processing time of the cube? If so, I'm not worried about that as we doing nightly.

Thanks!

Hello

You should take a look at this great post by Chris Webb:

http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!907.entry

More aggregations are not always better. In some cases, overdoing the number of aggregations can have a negative impact on performance.

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 Migration

Definitions of aggregations objects in AS2K5 are organized in AggregationsDesign groups so as to be able to assign a unique definition to several partitions within a Measure Group.

When Migration Wizard migrate aggregations, if two AS2K aggr sets from two different partitions of the same cube have exactly the same definition but are ordered in a different way, the Migration Wizard process will create two different AggregationsDesign with different name, duplicating xmla descriptions.

So It's better to order all the same aggr.definitions by the same criteria in every partition of a cube before migrating it.

It can be done by using the Partition Aggregation Utility.

Thanks.

I would not worry about arranging aggregations in AS2K.

AS2K5 gives you a lot of power and flexibility as it comes to editing objects.

I would migrate the cube in whatever state it is. Later I would clean up the AS2K5 database using scripted view of the database.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

Aggregation design wizard gives zero aggregations

Hi,

I know that there is already a post with this question. but I might have additional info and it might be realted to some other problem of mine.

specs:
ssas 2005 sp1,
backend database connects to a UDB(DB2) set of views,
these UDB views get data from many sybase database tables across different databases.

Cube has one partiton per day for one month
the current week partitions are in days,
and prev week partitions are merged to one single partition.
so as of now, for feb we would have the following partitions:

Feb 22 to 28 > 5 partitions (no weekends, 1 partition per day)
Feb 01 to 21 > 1 partition (merged from other days at the end of the week)

3 dimension> date, customer, product
customer and product having about 25 attributes each.
date is a stereotype time dimension with 2 hierarchies, attribute relationships defined
to form natural hierarchies. multiple pkeys for month, quarter.

the customer dimension has 2 natural hierarchies, one user defined un-natural hierarchy,
about 10 attribute relationships defined.

there are about 65 measures, some are derived as calculations from the DSV itself.

in the cube dimensions, the all hierarchy is set to "default" AggregationUsge
the key attributes, and other frequently used attributes including customer name, customer account no
are set to "unrestricted" AggregationUsage.

I processed the dimensions and one of partition,
dimensions contain about 400,000 and 700,000 rows.
the cube contains about 5,000,000 rows.

When I tried to count this using the "count" feature in agg design wizard ,
it gives a system full error after a long time.
so I did a count(*) from the database and gave appx counts.

when I click on create aggregations, after some time it just gives 0 aggregations.
I have tried 500MB of storage for aggregations, then 30% gain and "until I stop" option.
all give only 0 aggregations.

what could be the problem.
before I had designed attribute relationships,
I had tried this and had got about 9 aggregations,
should I not be gettig more now?

please help me not only to rectify this problem
but also to understand the details

Regards

When you run the agg design wizard, which attributes are bold? It only considers the bold ones.

Do you have any user defined hierarchies (as in multi-level hierarchies you built in the center pane of the dimension designer)? Are they natural hierarchies? (i.e. which the lowest level attribute is related to the next lowest all the way up)

The agg design wizard is simply looking at statistics to determine the most useful aggs. Have you considered looking at usage based optimization to use the query log to determine which aggs would be helpful?

Digest the following links and then post back if you still aren't having any luck:
http://www.sqlskills.com/blogs/liz/2006/07/03/InfluencingAggregationCandidates.aspx
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!907.entry

I doubt it will come to it, but the SQL Server Samples that came out with SP2 include a new sample called Aggregation Manager that helps you design your own aggregations. If we can't figure anything else out, that's a last resort.

|||

Hi ,

Elizebeth Vitt's blog helped me,

I was able to get about 8 aggregations with 48% optimization.

the aggregations take about 150MB per partition.

how should I achieve a balance between the two?

my requirement is to have maximum performance, the size of the aggregations do not matter. in this case how should I go about designing the aggregations?

Regards

|||

glad that helped

I can't answer that question other to say that you should just try different aggregation designs and test query performance. Designing too many aggs slows performance during cube processing, and it also might have a negative impact on query performance.

Review the performance guide (see link at top of this forum) for best practices in this area, too.

AggMerge_ .TMP files eat my harddisk

Hi champs;

This question has already been asked; but still no answer yet on this issue.

When I process the partition I can see that the aggregations are being created. When I look in the file system I see a bunch of .tmp files being created (e.g. AggMerge_5800_54_8s64d_28.tmp), all of them for about 1.0GB. I eventually run out of space on the drive and the processing fails.

All together these .tmp files take about 150GB. The source database is only about 200GB.

Any thoughts? Ideas? The aggregation design wizard tells me the aggregations will take up about 125kb, but the .tmp files tell me something different.

Thanks

kurlan

There are several points here.

One is. You need to take a look at the partition size. You might need to create smaller partitions.

Second. Aggregation design algorithm cannot calculate the size of your aggregations. It only makes size calculations based on the estimated counts on dimension attributes. You might want to create fewer aggregations.

Third. The reason you are seeing temporary files created is: All the aggregations you've designed cannot fit into memory and they are swapped to disc. By decreasing number of aggregations and by making sure you process fewer partitions in parallel, you should be able to achieve a point when aggregation processing does not require swapping.

Here is good source of information about best pracices for partitioning and aggregation creation: http://www.microsoft.com/technet/prodtechnol/sql/2005/realastd.mspx

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights