Another new paper from the SQLCat team, this time on techniques that can be used to optimise cubes that use many-to-many relationships:
I’ve only skimmed through it so far, but I thought I’d mention two other tricks to try with m2m dimensions. One is breaking up large dimensions if you don’t ever need to see them, which I blogged about in detail here: http://cwebbbi.spaces.live.com/Blog/cns!7B84B0F2C239489A!777.entry, and which can be very powerful. The other is introducing other dimensions to the intermediate measure group: for example, imagine you have a measure group showing the relationship between Products and the Components that make them up, you could try adding the Time dimension to that measure group; even though it will increase the size of the measure group because you are repeating the Product/Component combinations for each Time period, if all of your queries are sliced by a Time period and you know that the majority of your Products were only sold for a short space of time then it can be beneficial, although if neither of these conditions is true then doing this can have a negative impact on performance too.
