Hi,

again with some aggregation questions...

I try to opimize my partitions.
in a cube I have some measure groups based on tables from 1.5 million of
rows to 150millions.

to opimize my bigest partitions, I have set the aggregationusage to FULL for
the attributes in 3 main dimensions.
This reduce the cold cache access from 33seconds to 5seconds.
good

but this optimization cause my 1.5million of rows partition to be evaluated
with 0 aggregation!
this measure group use a dimension which disallow aggregation (like a budget
or version dimension where the All member mean nothing)

But I found this result strange, why SSAS create no aggregation for this
partition?

The biggest measure group use the same dimensions accept the non-aggregated
one.
So I don't understand what's appends.

any guide?

Jerome.

What are the aggregation functions you are using in the two cubes?

Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi

Jeje wrote:

aggregation function???
I'm not sure if I understand the question.

All my measure group/partitions are MOLAP based
in my case everything is in the same cube
to simplify I have 2 measure groups,
to optimize my cube, I have 3 main attributes (1 in each main dimension)
where the aggregationusage is set to full. These dimensions are used in the
2 measure group
I have another dimension where the Isaggregate option is set to false (so
there is no "All member" for this dimension)
This non aggregatable dimension is associated to the 1.5 millions rows.

The result of this combination of options result in 0 aggregation designed
for the 1.5 millions rows measure group.
While the big measure group is correctly optimized.

If I change the aggregationusage from full to default, then all the
partitions are evaluated with aggregations, but the performance is bad for
my big partitions.

I hope I'm clear in my description :-)

"Marco Russo" <marco.russo (AT) loader (DOT) it> wrote

Sorry, I mean what is the "AggregateFunction" property of the involved
measures.
By default it is Sum, but you can have others (Min, Max, DistinctCount,
ByAccount, LastChild, ...).
If you have the default Sum, I would suggest you to open a case with
Microsoft with a specific sample of the problem (or use
http://connect.microsoft.com), it is hard to understand what's
happening without the real data.

Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi


Jeje wrote:

its sum and/or count aggregations in each measure group.
but the same aggregations are designed for my DCount measures (which are in
dedicated measure group)


"Marco Russo" <marco.russo (AT) loader (DOT) it> wrote

At this point I don't have a good answer.... sorry.

Marco
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi

Jéjé wrote:

I'm guessing that no aggregations get designed for the measure group
with the "dimension where the Isaggregate option is set to false"
because, with the 3 main attributes automatically included by Full
AggreationUsage, the aggregations would be too large

So, what if you set Aggregation Usage for the "3 main attributes (1 in
each main dimension)" to Unrestricted, rather than Full?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

using the unrestricted option don't create good aggregations and my cube is
too slow

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

Hi, Jerome

I also have the same problem. Read below the suggestions from Microst
Consulting Services (I posted incident on this case). I tested on my lab
enviroment, but unsuccesfull.

Ramunas

This looks like a known issue we have seen with databases being migrated to
AS2005 from AS2000. This is not fixed in sp2 there the solution is easy.

If you've made the changes in the BIDS and deployed you project to the
server you cannot design aggregations. Only if you were to delete original
database on the server and re-deploy, Aggregation Design will start
designing aggregations. Deploying over existing database will result in
Aggregation Design designing 0 aggregations.

"Jeje" <willgart (AT) hotmail (DOT) com> wrote