Sql-server – Altering SSAS writeback behavior

excelmdxsql serverssas

I'm trying to implement a writeback measure group for required quantities by month and part number. I'm puzzled by the behavior I am seeing in Excel 2013, but I don't know if SSAS or Excel is responsible.

If the dimension selection in Excel is not the native granularity of the the underlying Measure Group, 'Publishing' the data distributes the data as equally as it can among the dimension members. Which is not necessarily wrong, but a bit weird and definitely unexpected. e.g.

If I publish a value at the year level, it will divide it equally into quarters and months.

If I don't include the part dimension it will divide the quantity equally into all part numbers.

I need to prevent data from being published anywhere but at the intersection of Part and Month. My only thought so far is to control write access through the Role with an expression that includes the Measure and Dimensions, but I'm not having much luck with that due to my limited MDX skills. Is there any other mechanism to modify writeback behavior?

Best Answer

Writeback is always written to the leaf level of your dimension, and has to be because of the structure of your writeback table (where the difference between the original value and the new value is stored).

Have a look at the writeback tables in your data source and you'll see it contains fields for all your measures, and the key of all your dimensions, so the change always needs to be distributed to that level.

From the documentation on write-enabled partitions

Changes to leaf and nonleaf cells are handled differently. A leaf cell represents an intersection of a measure and a leaf member from every dimension referenced by the measure group. The value of a leaf cell is taken directly from the fact table, and cannot be divided further by drilling down. If a cube or any partition is write-enabled, changes can be made to a leaf cell. Changes can be made to a nonleaf cell only if the client application provides a way of distributing the changes among the leaf cells that make up the nonleaf cell.

The only thing you can really influence is how the changes are distributed to the leaf level cells when you write back to another attribute. This is documented in the UPDATE CUBE

You basically have 4 options:

  • USE_EQUAL_ALLOCATION
  • USE_EQUAL_INCREMENT
  • USE_WEIGHTED_ALLOCATION [ BY Weight_Expression]
  • USE_WEIGHTED_INCREMENT [ BY Weight_Expression]

I'm not sure how well Excel allows you to pick an allocation method however.

Related Question