Resolving a many-to-many requirement in SSAS2005

[eo]

Hi,

I will show the data table contents in the hope that someone will be able to help:

FactTable:
TRID LCID LTID Measure
0001 0001 00010001 100
0001 0001 00010001 -50
0001 0003 00010003 20
0001 0003 00010003 10

...note that the LTID is a combination of the TRID and the LCID...

DimTable
TRID LCID LTID Status CurrHist
0001 0001 00010001 NEW HIST
0001 0002 00010002 RENEW HIST
0001 0003 00010003 OTHER CURR

AsAtMonthEndSnapshot
TRID LCID LTID DATE
0001 0001 00010001 31/01/2008
0001 0003 00010003 28/02/2008
0001 0003 00010003 31/03/2008
0001 0003 00010003 30/04/2008

AsAtMonthEndFlag
DATE
31/01/2008
28/02/2008
31/03/2008
30/04/2008

Now the standard rules for the M2M relationships in SSAS2005 states that we link as follow:

Fact.LTID > DimTable.LTID
AsAtMonthEndSnapshot.LTID > DimTable.LTID
AsAtMonthEndSnapshot.DATE > AsAtMonthEndFlag.DATE

The Dimension usage is set up accordingly:

DimTable to Fact as a regular relationship type on LTID
DimTable to AsAtMonthEndSnapshot as a regular relationship type on LTID
DATE to Fact as a Many-to-many relationship using AsAtMonthEndSnapshot as the Intermediate measure group
DATE to AsAtMonthEndSnapshot as a regular relationship on DATE

The results in the browser is currently:

AsAtMonthEndFlag.DATE Measure
31/01/2008 50
28/02/2008 30
31/03/2008 30
30/04/2008 30
Total 80

But the aim of the many-to-many (history) solution is to provide a snapshot as at a month end date, so the results should look like this:

AsAtMonthEndFlag.DATE Measure
31/01/2008 50
28/02/2008 70
31/03/2008 80
30/04/2008 80
Total 80

Therefore accumilative in nature. In T-SQL this can be acheived by linking between Fact and DimTable on TRID and not on LTID, but this breaks the rule of a many-to-many solution as these tables to each other ALSO has a M2M relationship.

ANY IDEAS???

EO
Hertfordshire, England