Interesting M-M Relationship

I have a need to model an interesting M-N relationship within Analysis Services 2005. However, this M-N relationship is different from that given as the example in the AdventureWorksDW database. So, I'm wondering how best to go about it.

In the AdventureWorkDW database, the FactInternetSales table is related to the FactInternetSalesReasons fact table such that a sale in the first table can be related to many reasons in the second table. Also, the DimSalesReason table is related to the FactInternetSalesReasons fact table such that a reason in the dim table can be related to many facts in the second. Thus, the FactInternetSalesReasons fact table serves as a classic bridge table, creating a M-N relationship between FactInternetSales and DimSalesReason. This is then modeled in AS using an intermediate measure group and a many-to-many relationship.

Here's my situation. I have a standard fact table with a standard relationship to a dimension table (ie, M-1 between the fact table and dimension table). The dimension table is then related to another table, which contains possible parents for the dimension members. Thus, a single dimension member can be related to multiple parents. The fact records need to correctly rollup to each parent based on which members belong to each parent. Thus, a 1-M relationship exists between a dimension member and possible parents.

So, the question is how to model this. There doesn't seem to be an intermediate measure group to create given the fact table is correctly related to the dimension table. Its just that the fact table needs to be connected to the parent table as well, through the dimesion table.

Anyone know how to correctly model this? Thanks in advance!!

Dave Fackler
Hi Dave

Check this out. I think it is just what you are looking for.

http://www.sqlserveranalysisservices.com/OLAPPapers/DuplicateMembers.htmsql