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

If many clients perform temporary writebacks on a single cube and one of them commits
changes to the server, it might take a considerable amount of time to conclude the opera-
tion because the server has to update the writeback caches of all other affected sessions. In
addition, Analysis Services has to clean caches (discussed in Chapter 29, “Architecture of
Query Execution—Calculating MDX Expressions”) because the data stored there becomes
invalid.
Analysis Services 2008 enables you to store writeback data in multidimensional online
analytical processing (MOLAP) partitions in addition to relational online analytical
processing (ROLAP) writeback partitions supported by earlier versions of Analysis Services.
For more information about partition types, see Chapter 20, “Physical Data Model.” In
both cases, the writeback partition is based on a relational table, called a writeback table .
During execution of the COMMIT TRANSACTION statement, Analysis Services stores the delta
values for updated cells in this table. In case of the ROLAP writeback partition, Analysis
Services doesn't need to perform additional operations during the commit stage. However,
in case of the MOLAP writeback partition, in addition to storing data in the relational
table, Analysis Services processes data from the writeback cache and writes it into MOLAP
partition. (For more information about processing the MOLAP partition, see Chapter 21,
“Dimension and Partition Processing.”)
Because Analysis Services needs to process the MOLAP partition while executing the
COMMIT TRANSACTION statement, it takes longer to commit transactions to the MOLAP
writeback partition. However, storing writeback data in the MOLAP partition rather than
the ROLAP partition improves performance of queries that use writeback data, because
reading data from MOLAP partitions is much more efficient than reading data from the
ROLAP partition. In addition to this obvious optimization, storing writeback data in
MOLAP partition improves performance of the UPDATE CUBE statement. During execution
of each UPDATE CUBE statement, Analysis Services retrieves delta values stored in the write-
back partition and uses those values to calculate new deltas. Therefore, keeping writeback
data in MOLAP partition speeds up process of reading the data from the writeback parti-
tion and results in faster execution of the UPDATE CUBE operation.
Enabling Writeback
For writeback operations to succeed, you must enable writeback capabilities on the
measure group to which you are writing the data. To enable writeback, you have to
perform the following steps in BI Dev Studio:
1. In BI Dev Studio, open the FoodMart 2008 project.
2. In the Solution Explorer, double-click the Warehouse and Sales cube to open the
cube editor.
3. On the Partitions tab, expand the list of partitions for the measure group you are
writing to (in this example, we write into the Warehouse measure group), right-click
anywhere in the Partition Organizer, and then choose Writeback Settings from the
contextual menu, as shown in Figure 16.9.