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

4. The update statement is used to update the cell values in the cube. The
syntax of the update statement is shown below.
UPDATE CUBE <CubeName>
SET <Tuple Expression> = Numeric or String value
[ALLOCATION TYPE clause]
5. The UPDATE CUBE syntax is pretty straightforward where you specify
the coordinates in the cube to update the new value. The allocation type
clause is an optional clause by which you can specify the nature of the al-
location. You see examples of various allocation types later in this chapter
through examples.
6. In the update statement the coordinate pointed by the tuple ([WB Employ-
ee].[Manger].&[291], [WB Period].[Period].&[20043]) refers to the budget
value for Jae Pak. The statement updates the cell value to 1000.
7. If you send the following query to the cube you will see that the budget
value for Jae Pak is now 1000:
SELECT {
[WB Employee].[Manager].&[291] } ON 1 ,
[WB Period].[Period].&[20043] ON 0
FROM
[Adventure Works DW]
8. Send the same query in the second MDX query editor window. You will
see the original value null rather than the new value 1000. The update
statement and the value only take effect for the first MDX window, which
holds one connection (or session). Other connections (or sessions) are
not affected by the updated value. This is because Analysis Services up-
dates the new cell value only if you request it to make these changes per-
manently in the cube. In order to do that you need to send the commit
statement to the server as shown below.
COMMIT
9. COMMIT is a short form for COMMIT TRANSACTION statement. By de-
fault, when you start executing new queries within SQL Server Manage-
ment Studio, an implicit statement called BEGIN TRANSACTION is ex-
ecuted. Due to the changes, cell values will get updated only after you call
the COMMIT TRANSACTION or just the COMMIT statement. When the