SSAS Writeback

If you want to write-enable a measure group, end users can change cube data while they browse it, where changes are saved in a separate table called a writeback table, not in the cube data or source data. End users who browse a write-enabled partition see the net effect of all changes in the writeback table for the partition.

e.g.: Users need to enter in projected or target sales for future months.

Writeback is supported for SQL Server relational databases and data marts only, and only for Analysis Services multidimensional models.

Writeback is not supported in linked measure groups.

How to setup writeback in SSAS cube?

Using BIDS/SSDT open the SSAS project, and open the cube. Just right click on the partition (in the Partitions tab of the cube editor) and select Writeback settings. You can just take the default settings.

Writeback is only supported for measures that use the SUM aggregation.

Using Excel 2010 you can write back the data to the cube.

Open Excel 2010, connect to the cube, and enable “What-If Analysis”. When you want to write these back simply click the “Publish Changes” button under the “What-If Analysis” button. All the updated data will now appear in the cube for browsing and comparing.  We can go back and look at the writeback table to see all of the updated data.

Now after a while this writeback table can get very large so depending on the data/requirement you will eventually want to integrate these targets back into the data warehouse using some kind of ETL process, or delete writeback data.

Deleting writeback data clears the writeback cache; as soon as that data is deleted, additional writeback work is performed on a clean slate. Disabling writeback for a cube partition simply turns off writeback for that partition.

Reference 1 – MSDN

Reference 2

Reference 3

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s