Writing Data into Analysis Services - Microsoft SQL Server 2008 Analysis Services - page 294

Figure 16.7 shows the result of the query execution.

1998

Treehouse Distribution Store 15 120

FIGURE 16.7

Last update within the UPDATE_CUBE statement wins.

When a single UPDATE CUBE statement contains multiple update clauses, Analysis Services
performs updates sequentially because execution of the first update can affect the result of
the subsequent updates. This consideration is important when the same cells are updated
by both updates (cell ([Warehouse].[Warehouses].[Warehouse].&[13], [Store].
[Stores].[Store].&[15]) in our example). However, when both updates are independent
from each other, Analysis Services can optimize the performance of the update by choos-
ing a better execution plan. To optimize the scenario where updates within UPDATE CUBE
statement update different set of cells, Analysis Services 2008 introduces a new connection
string property Update Isolation Level . When this property is set to 1 (isolated),
Analysis Services improves the performance by evaluating each update in parallel without
any consideration for the others. With the Update Isolation Level property set to 0 or 2
(default behavior, not isolated), Analysis Services performs updates sequentially.

NOTE
Although setting Update Isolation Level property to isolated can improve perfor-
mance of the update, we recommend that you do it with utmost care. You should use
this property when you are certain that updates are independent from each other; oth-
erwise, the writeback operation can lead to unexpected results.

Updatable and Non-Updatable Cells

The UPDATE CUBE statement enables you to update almost any cell in a cube. However,
there are exceptions. The following cells cannot be updated:

. Cells based on calculated members.

. Measures aggregated by any aggregation function other than Sum .

. Cells protected from a user by cell security. For more information about cell security,

see Chapter 37, “Securing Cell Values.”