Creating Analysis Services Cubes - SQL Server Analysis Services 2012 Cube Development - page 123

Enabling write-back feature
The write-back feature allows data analysts to perform what-if analysis and is typically
used for budgeting applications. For example, an operations manager might want to
see what the profit margins would look like if her team were to achieve a cost reduc-
tion of 10 percent. Although not frequently used, write-back is a useful feature.
Analysis Services does not overwrite any values in the existing fact tables from which
it sources data. Rather, it creates a new partition for storing the write-back data. The
new partition is based on a table that could be stored in the same database as the
primary fact table or in a different relational database. SSAS derives requested values
at the query execution time by aggregating write-back and regular partition values.
Note that the write-back feature works only on measure groups that have measures
using the SUM aggregation function. If you have followed along with the examples in
this chapter, you must remove the Fact Reseller Sales Count measure before
enabling write-back.
How to do it...
To enable write-back on a measure group, perform the following steps:
1. Right-click on the measure group's Writeback folder in SSMS, and select En-
able Writeback to activate the dialog shown in the next screenshot.
2. Specify the data source where you want to store the write-back data; you may
use the existing data source or define a new one. Also specify the table name;
by default, the table will have the WriteTable_ prefix.
3. Specify the storage mode for the write-back partition, either MOLAP or
ROLAP. Once you click on OK , Analysis Services will create the write-back
partition. You need to process the write-back partition before SSAS creates
the relational table.
4. Any queries against the write-enabled measure group will query both regular
and write-back partitions; the SQL Profiler will display the following messages: