Updating Your UDM Data - Professional SQL Server Analysis Services 2005 with MDX - page 544

2004 Q1 2004 Q2 2004 Q3

Varkey Chudukatil,Ranjit 707000 908000

316.9284468

Incremental Allocation

The third scenario is where a cell already has a value and you need to allocate
values to leaf-level cells so that they are incremented by the new value allocated
— based on equal allocation or weighted allocation. Consider an organization that
receives funding from multiple sources and these funds need to be allocated to
subdivisions one by one. Further, you do not want to overwrite the previous data.
Before you learn about incremental allocation, please delete the Writeback table in
the relational data source and reprocess the WriteBackExample database.

Lets say Amy Alberts obtained funding in the amount of $1,000 for Jae
Pak in support of the project he is working on. Amy allocates this budget
directly to Jae for quarter 3 of 2004. Send the following update state-
ment to allocate the budget to Jae.

UPDATE CUBE [Adventure Works DW]
SET (
[WB Employee].[Manager].&[290]
, [WB Period].[Period].&[20043]) = 1000

Now assume Amy gets funding in the amount of $1,000 for the entire group and
she wants to allocate this equally to all her direct reports. She obviously does
not want to overwrite the existing budget value allocated for Jae already. Ana-
lysis Services provides a way to allocate this new budget amount to the leaf-
level cells either through equal or through weighted allocation. The allocation
clause keyword that needs to be used is USE_EQUAL_INCREMENT or
USE_WEIGHTED_INCREMENT along with the weight as seen in the Weighted al-
location example.

Consider the scenario where Amy wants to allocate the amount equally. The MDX
query to do this allocation is: