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

After corrupted publisher

Hi All,
I am fairly new to MS SQL Replication. I have two servers one is setup
as the publisher and the other is setup as both the subscriber and the
distributor.
Due to server reboots, the publicher DB became corrupted therefore we
pointed all activity to the subscriber DB. Now we would like to bring
back the publisher online, but we do not have a backup.
My Question is, can we restore the publisher DB from the backup of the
subscriber and reinstate replication? if yes could someone please
explain the process as I am new to SQL and replication.
Question two, someone metioned while re-establishing replication I can
regenerate the DB by doing a snapshot. How can I do that on the
publisher.
If you have any other method that will help bring back the publisher
and copy the updated data from the suscriber to the publisher and
re-instate replication that would be great.
PS: the reason why we know the publisher is corrupted is due to a DBCC
CHECKDB(XXX) which gave us a lot of errors.
Thank you much,
Ron
For Question 1: prevent all access to the subscriber database and take a
backup of the publisher and subscriber's database.
Script out the replication setup off the publisher if it is accessible.
Restore on the publisher.
Run sp_removedbreplication on each database.
Obtain your replication script to recreate the publication and subscription
(do you have one?) and edit it to ensure the publication is set up as a
nosync one (for SQL 2000:
http://www.replicationanswers.com/No...lizations.asp, for SQL 2005:
http://www.replicationanswers.com/NoSyncOn2005.asp).
Initialize as per normal.
This means that any tables you had in the publisher's database and not in
the subscriber database will be replaced - is this OK? If not, restore the
publisher database as another name and transfer over the tables/objwects
selectively using DTS.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Ron - answers inline:

> 1: Can I set the subscriber DB to read only?
(1) yes, but when you restore it on the publisher you'll need to set to be
RW to add the publication

> 2: Why do I need a backup of the publisher as it is corrupted.
(2) In case there are tables which aren't in the publication as you'll need
to copy them over into the restored subscriber database later on, assuming
it is addressable in some form or other. Or more simply, just rename it
after scripting out the replication setup.

> 3: By Scripting out the replication off the publisher do you mean right
> click on the on the replication tree in the server where the publisher
> is located and click on Generate SQL Script than click on preview and
> copy the script?
(3) Yes.

> 4: By restore on the publisher do you mean I can restore the backup of
> the subscriber to the publisher?
(4) Yes. When restoring it, you'll give it the same name as the published
database you had previously (the corrupted one). After that, you'll need to
assign permissions to it and then copy in any extra tables.

> 5: You said to run sp_removedbreplication on each database, does that
> mean both publisher and subscriber? are there any other option I
> should use with the sp_removedbreplication?
Yes. No - it's pretty straightforward and just takes one parameter - the
database name.

> 6: Using the replication script how do I recreate the publication and
> subscription? How do I set it up as a nosync on SQL 2000 I did not
> really understand the article.
The replication script should be able to be used as it is with one slight
change: set the @.sync_type = none parameter value of sp_addsubscription ie
change 'Automatic' to 'None'. After that, run
sp_scriptpublicationcustomprocs 'publicationname' at the publisher and
execute the results on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

After corrupted publisher

Hi All,
I am fairly new to MS SQL Replication. I have two servers one is setup
as the publisher and the other is setup as both the subscriber and the
distributor.
Due to server reboots, the publicher DB became corrupted therefore we
pointed all activity to the subscriber DB. Now we would like to bring
back the publisher online, but we do not have a backup.
My Question is, can we restore the publisher DB from the backup of the
subscriber and reinstate replication? if yes could someone please
explain the process as I am new to SQL and replication.
Question two, someone metioned while re-establishing replication I can
regenerate the DB by doing a snapshot. How can I do that on the
publisher.
If you have any other method that will help bring back the publisher
and copy the updated data from the suscriber to the publisher and
re-instate replication that would be great.
PS: the reason why we know the publisher is corrupted is due to a DBCC
CHECKDB(XXX) which gave us a lot of errors.
Thank you much,
RonFor Question 1: prevent all access to the subscriber database and take a
backup of the publisher and subscriber's database.
Script out the replication setup off the publisher if it is accessible.
Restore on the publisher.
Run sp_removedbreplication on each database.
Obtain your replication script to recreate the publication and subscription
(do you have one?) and edit it to ensure the publication is set up as a
nosync one (for SQL 2000:
http://www.replicationanswers.com/N...alizations.asp, for SQL 2005:
http://www.replicationanswers.com/NoSyncOn2005.asp).
Initialize as per normal.
This means that any tables you had in the publisher's database and not in
the subscriber database will be replaced - is this OK? If not, restore the
publisher database as another name and transfer over the tables/objwects
selectively using DTS.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Hi Ron - answers inline:

> 1: Can I set the subscriber DB to read only?
(1) yes, but when you restore it on the publisher you'll need to set to be
RW to add the publication

> 2: Why do I need a backup of the publisher as it is corrupted.
(2) In case there are tables which aren't in the publication as you'll need
to copy them over into the restored subscriber database later on, assuming
it is addressable in some form or other. Or more simply, just rename it
after scripting out the replication setup.

> 3: By Scripting out the replication off the publisher do you mean right
> click on the on the replication tree in the server where the publisher
> is located and click on Generate SQL Script than click on preview and
> copy the script?
(3) Yes.

> 4: By restore on the publisher do you mean I can restore the backup of
> the subscriber to the publisher?
(4) Yes. When restoring it, you'll give it the same name as the published
database you had previously (the corrupted one). After that, you'll need to
assign permissions to it and then copy in any extra tables.

> 5: You said to run sp_removedbreplication on each database, does that
> mean both publisher and subscriber? are there any other option I
> should use with the sp_removedbreplication?
Yes. No - it's pretty straightforward and just takes one parameter - the
database name.

> 6: Using the replication script how do I recreate the publication and
> subscription? How do I set it up as a nosync on SQL 2000 I did not
> really understand the article.
The replication script should be able to be used as it is with one slight
change: set the @.sync_type = none parameter value of sp_addsubscription ie
change 'Automatic' to 'None'. After that, run
sp_scriptpublicationcustomprocs 'publicationname' at the publisher and
execute the results on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

After corrupted publisher

Hi All,
I am fairly new to MS SQL Replication. I have two servers one is setup
as the publisher and the other is setup as both the subscriber and the
distributor.
Due to server reboots, the publicher DB became corrupted therefore we
pointed all activity to the subscriber DB. Now we would like to bring
back the publisher online, but we do not have a backup.
My Question is, can we restore the publisher DB from the backup of the
subscriber and reinstate replication? if yes could someone please
explain the process as I am new to SQL and replication.
Question two, someone metioned while re-establishing replication I can
regenerate the DB by doing a snapshot. How can I do that on the
publisher.
If you have any other method that will help bring back the publisher
and copy the updated data from the suscriber to the publisher and
re-instate replication that would be great.
PS: the reason why we know the publisher is corrupted is due to a DBCC
CHECKDB(XXX) which gave us a lot of errors.
Thank you much,
RonFor Question 1: prevent all access to the subscriber database and take a
backup of the publisher and subscriber's database.
Script out the replication setup off the publisher if it is accessible.
Restore on the publisher.
Run sp_removedbreplication on each database.
Obtain your replication script to recreate the publication and subscription
(do you have one?) and edit it to ensure the publication is set up as a
nosync one (for SQL 2000:
http://www.replicationanswers.com/NoSyncInitializations.asp, for SQL 2005:
http://www.replicationanswers.com/NoSyncOn2005.asp).
Initialize as per normal.
This means that any tables you had in the publisher's database and not in
the subscriber database will be replaced - is this OK? If not, restore the
publisher database as another name and transfer over the tables/objwects
selectively using DTS.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Hi Paul,
Thank you for your response.
I have a few question and pardon my lack of understanding as I am new
to SQL and replication.
1: Can I set the subscriber DB to read only?
2: Why do I need a backup of the publisher as it is corrupted.
3: By Scripting out the replication off the publisher do you mean right
click on the on the replication tree in the server where the publisher
is located and click on Generate SQL Script than click on preview and
copy the script?
4: By restore on the publisher do you mean I can restore the backup of
the subscriber to the publisher?
5: You said to run sp_removedbreplication on each database, does that
mean both publisher and subscriber? are there any other option I
should use with the sp_removedbreplication?
6: Using the replication script how do I recreate the publication and
subscription? How do I set it up as a nosync on SQL 2000 I did not
really understand the article.
Thank you very much for you help.
Ron.
------
Paul Ibison wrote:
> For Question 1: prevent all access to the subscriber database and take a
> backup of the publisher and subscriber's database.
> Script out the replication setup off the publisher if it is accessible.
> Restore on the publisher.
> Run sp_removedbreplication on each database.
> Obtain your replication script to recreate the publication and subscription
> (do you have one?) and edit it to ensure the publication is set up as a
> nosync one (for SQL 2000:
> http://www.replicationanswers.com/NoSyncInitializations.asp, for SQL 2005:
> http://www.replicationanswers.com/NoSyncOn2005.asp).
> Initialize as per normal.
> This means that any tables you had in the publisher's database and not in
> the subscriber database will be replaced - is this OK? If not, restore the
> publisher database as another name and transfer over the tables/objwects
> selectively using DTS.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Hi Ron - answers inline:
> 1: Can I set the subscriber DB to read only?
(1) yes, but when you restore it on the publisher you'll need to set to be
RW to add the publication
> 2: Why do I need a backup of the publisher as it is corrupted.
(2) In case there are tables which aren't in the publication as you'll need
to copy them over into the restored subscriber database later on, assuming
it is addressable in some form or other. Or more simply, just rename it
after scripting out the replication setup.
> 3: By Scripting out the replication off the publisher do you mean right
> click on the on the replication tree in the server where the publisher
> is located and click on Generate SQL Script than click on preview and
> copy the script?
(3) Yes.
> 4: By restore on the publisher do you mean I can restore the backup of
> the subscriber to the publisher?
(4) Yes. When restoring it, you'll give it the same name as the published
database you had previously (the corrupted one). After that, you'll need to
assign permissions to it and then copy in any extra tables.
> 5: You said to run sp_removedbreplication on each database, does that
> mean both publisher and subscriber? are there any other option I
> should use with the sp_removedbreplication?
Yes. No - it's pretty straightforward and just takes one parameter - the
database name.
> 6: Using the replication script how do I recreate the publication and
> subscription? How do I set it up as a nosync on SQL 2000 I did not
> really understand the article.
The replication script should be able to be used as it is with one slight
change: set the @.sync_type = none parameter value of sp_addsubscription ie
change 'Automatic' to 'None'. After that, run
sp_scriptpublicationcustomprocs 'publicationname' at the publisher and
execute the results on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .