Quantity/Revenue Planning using SSAS Writeback

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.

Limitations

  • Excel won’t show generated Revenues as long as data isn’t published

 

 

Advertisements

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden /  Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s