'Analysis services: Dynamic Weighted Allocation on different writeback levels

I'm using an Update Cube Statement to Writeback Values from a frontend (XLCubed) to the Analysis Services Cube.

UPDATE CUBE [Planung] 
SET
([Measures].[Kg Anpassung]
,[Datentyp].[Datentyp].[All].[FJ Plan]
, [Produkt.[All].[Element from level 1])
= 77000
USE_WEIGHTED_ALLOCATION
BY
([Measures].[DB1], [Datentyp].[Datentyp].[All].[LJ], [Produkt].[All].currentmember) / ([Measures].[DB1],[Datentyp].[Datentyp].[All].[LJ], [Produkt].[All].currentmember.parent)

The Value (77000) is Allocated by the Values of the Actual Year (LJ) in a different measure. The Weightfactor is calculated by looking at the Product it's writing to divided by its parents value.

With the code above it's possible to Input Values on Base-Elements of the Product-Hierarchy or on level 1 of the product-hierarchy. But it is not possible to write to elements on the level 2 of the product-hierarchy, as you need the parent.parent element of the base element to calculate the weight-factor.

Base Element (Level 0) -> Written Value = Input (handled by Frontend)

Level 1 -> Written Value = Input * (Actual Year / (Actual Year, Product.Parent))

Level 2 -> ?

  • Is it somehow possible to write a formula which is valid for every possible level in the writeback hierarchy?
  • Alternative: Is it possible to read the level of the element my input is done on?

Best regards Paul



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source