patternsqlMinor
Altering SSAS writeback behavior?
Viewed 0 times
writebackbehaviorssasaltering
Problem
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?
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?
Solution
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:
I'm not sure how well Excel allows you to pick an allocation method however.
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.
Context
StackExchange Database Administrators Q#177129, answer score: 3
Revisions (0)
No revisions yet.