i have some data and i cannot figure out how to get the rollup i want.
i have a measure that is at a coarser grain than my dimension data, and
i want to write an MDX query without resorting to something like a
weighting factor (see http://www.dbmsmag.com/9808d05.html for a
discussion about this)

i have agreements(id, aValue) and payments(agreementId, pYear,
pAmount). i have more payments than agreements. i want to be able
to have a measure on agreements and a measure on payments.

a sample bit of data that illustrates this:

Agreements
id aValue
1 $1,500
2 $900

Payments
id agreementId pYear pAmount
1 1 2002 $1,000
2 1 2003 $500
3 2 2002 $25
4 2 2002 $25

alternatively, i have have the data denormalized like this
if it helps:

AgreementPayments
id agreementId aValue pYear pAmount
1 1 $1,500 2002 $1,000
2 1 $1,500 2003 $500
3 2 $900 2002 $25
4 2 $900 2002 $25

i want to create a cube with the following:
measure: agreementValue
measure: paymentAmount
dimension: paymentYear

i want to be able to be able to issue the following query:

q) what is the total agreement value of agreements that have
payments in 2002? in this case, the result should be $2,400
($1,500 + $900). my problem is that by default i will get my
agreementValue measure rolled up as a sum and will see $1,500
(payment1) + $900 (payment3) + $900 (payment4).

note that i also need to be able to rollup paymentValues in the
same cube.

i have looked into trying to roll up agreementValue myself in MDX
by trying to get distinct descendants but i cannot get things to work.

is there some way for me to set up my schema such that i'll get the
correct answers (i took a quick look at many-to-many dimensions:
http://msdn.microsoft.com/library/de...mdiminas.asp)?
or, is there some MDX query that will basically do what i want?

any suggestions? i'm using the SSAS 2005 pre release.

thanks,
-jamie