Assuming an SSAS solution that contains measures for Quantity, Revenue and Price, whereas Price is a calcualted measure (=Revenue/Quantity).
Overwriting either Quantity or Revenue causes a change in Price which in most cases is not what users expect to see. Usually they’d like to keep Price constant if Quantity changes and adapt Price only if Revenue is overwritten.
To achieve this I had to do some tricks
- Change Storage Model for Writeback Partiton to „ROLAP“
- Add INSERT-Trigger to Writeback Table and generate Revenue values based on Quantity Delta. Be careful to and query TRIGGER_NESTLEVEL() to prevent recursion when inserting additional records to Writeback via trigger.
- Excel won’t show generated Revenues as long as data isn’t published