Using Scripts to Simulate High-level Write-Back

Data write-back in Analysis Services has always been a bit frustrating because it's ultimately limited to leaf-level storage. (Not that leaf-level data write-back is not extremely useful--think of the millions of budgets that are input by using Excel spreadsheets--leaf-level data input without the benefits of an OLAP engine.) At any rate, Yukon write-back is still going to be ultimately leaf-level. One option for efficient high-level write-back is to "allocate" the values for each level to a single "dummy" member. But there's another option that we used occasionally in AS2K that would be even easier to implement in Yukon.

In AS2K, you can create Calculated Cell expressions that change the value of a single cell. So you can simulate data write-back by writing a calculated cell that assigns the target value to the specific single-cell subcube.

In Yukon, you get the same effect as a Calculated Cell by simply executing a script assignment. So, in a hypothetical cube with two non-measure attributes--account, and period, if the user enters the value 1000 for March Net Profit, you could "store" that value by using the simple assignment statement

([March], [Net Profit], [Amount]) = 1000;

Over the course of a session, your code collects these assignment statements, and at the "commit" portion, exports them to a process that parses, allocates and loads into a processable fact table.

-- Reed