I have a parent-child dimension and a fact table that has measures specified for both the children and the parents. The last level of the dimension is also involved in M2M relationships.
If i use regular sum aggregations, the measure values for the parent members are double counted (as expected). I tried changing the Fact Aggregation property of the dimension to "Members Only" but this creates problems with the M2Ms. Is that expected or should I be able to use this property?
I can get the desired result using the unary operator but it seems that the parent's value is always ignored in its own calculation. For example, if my dimension looks like this:
ParentArea1
-- ChildArea1
-- ChildArea2
and my fact table has:
Area,Amount
ParenArea1, 10
ChildArea1, 6
ChildArea2, 4
then I can apply the UO + to ChildArea1 and ChildArea2 but the value of 10 assigned to ParentArea1 is never used in calculating its own Amount regardless of the UO I apply. The end result is correct, I just want to make sure I understand the logic.
Regarding the 'unary operator' as fact aggregation (doc). Leafs are calculated using the sum aggregation and the unary operator is used only for members with children. The 'fact' value of the parent is ignored; it would only make sense to use the parent value if the hierarchy defines 'members only' but it's not done (children value would be twice with members and descendants). It's relatively easy to improve and extend this rollup aggregation, feel free to contact if you need something special.
I've to look a bit more in detail, but I don't see why M2M creates any issue. A M2M makes possible for several member leaves to point to the same row, but this is all.
hope it helps