I have been working for the past couple of months with Analysis Services 2005 and trying to get a hold on the Many-to-Many relationships. It si a pretty simple concept when you get down to it. AS2005 takes care of the many-to-many relationship and all that is required of you is to provide it with a simple intermediate mapping table. The only problem seems to be that when you actually get the aggregations for the group. I cannot have a relationship as follows:
So we have this diagram in which there are Accounts that can have many clients and clients may have many accounts. However, we also have a Groups table in which you are able to group Accounts. However, these accounts can belong to many groups. When we proces such a heirarchy through AS then it does not seem to process the aggregations properly. What make it worse is that if I have another dimensional table attached to the fact table that tells me perhaps something like Manager. I query to give me something like ‘Give me all of the Groups and Accounts for this Manager person with their corresponding totals’ it kind of pukes and instead gives me the Manger with ALL of the Groups …even if the Manager is not assigned to any of the accounts contained within them???? Instead I have to match up subsets somehow to give me all the groups and correesponding accounts and then give me the Manager and all his/her corresponding accounts and then match these up. It seems that AS2005 should be able to interpret this in its aggregations. After all these mapping tables are treated as fact tables with a corresponding count aggregation. It is often said that this aggregation is never really used but what about this case? Wouldn’t you think that Analysis would search through the cube to find those places in which count<>0 for the corresponding dimension of Manager. Obviously, somewhere the idea of dimensionality is broken. If anyone ahs had similar experiences and has found a good solution then please feel free to drop a line.