Many to Many - Performance & Aggregations

HI,

I am interested in knowledge about Many to Many and possible sideaffects on aggregations and performance - any recomandations? (links, helpfile, ...)

I am about to model a big cube with 10 measuregroups and 8 of this measuregorups have an many to many related dimension (the immidiate fact table for the many to many dimension has about 500.000 Records per day for 2++ Years finaly) and I want to use this for a imidiate measuregroup (the many to many is in relationship with two dimensions)

It is "article" published on a website per day - the website is the many to many dimension - because the article could be publised on this day on this 7 websites and on the next day on this 11 websites and we want to report how many articles are on this website with this possible revenue and much more.

Does analysis services design proper aggregations for the website if its a many to many relationship? Any sideeffect in performance because I have a huge amount of data? (and a lot of calcuated members)

I think it is better do model as three dimension (artice [100000 Members], time [1000 Members] and website [500 Members]) as MTM then one dimension with aprox. >100Mio members ?

The backgroud - I have setup an prototyp and it is not performing as expected....

THANKS, HANNES

This is from Christian Wade's Blog: "Note that many-to-many are not pre-aggregated across dimensions(analogous to a materialized reference dimension) and my understanding is that Microsoft curently has no plans to create a "materialized many-to-many dimension" "

Have a look at blogs.conchango.com/christanwade and "Many-to-Many Dimensions".

Regards

Thomas Ivarsson

|||

Doesn′t sound to good what you say. Is there any other place you know with information - because the website you mention seems to be offline now.

what design would you recommend? And how to best tune the many to many related queries ?

THANKS HANNES

|||

Hopefully the website will be online soon. This is the best guide I have found on this issue.

Regarding your data model you can try to have one measure group for each website to avoid the many-to-many problem with articles. I am not sure that this solution can solve all your problems. I think that you can find a simular discussion regarding(but in another business case) this problem on his blog when it goes online again.

Regards

Thomas Ivarsson

|||

Marco Russo is working on an extremely detailed paper on this subject, which will be published soon:

http://www.sqljunkies.com/WebLog/sqlbi/archive/2006/08/21/22495.aspx

Chris