Professional Microsoft® SQL Server® Analysis Services 2008 with MDX

8.6. Dimension Writeback

Dimension writeback is another enhancement to dimensions and is available through the Dimension Intelligence Wizard. This is an important though rarely used feature that allows you to create or modify members in your dimension without having to go to your relational data source. Once you enable your dimension for writeback, Analysis Services provides you the functionality to add or modify members through the Dimension Browser pane. Some business scenarios where dimension writeback can be used are:

• When employees of an organization move from one location to another and their reporting structure in the organization and attributes of employee such as address and phone number change.

• When an employee's status changes due to marriage or childbirth and these changes need to be updated in the dimension.

• When you have an Account dimension and new types of accounts are being introduced and you need to add members to the dimension.

When you writeback data through the dimension, Analysis Services propagates the change in the data to the relational data source and does an incremental process of the dimension, such that affected members are processed. During this time, the dimension and cube will be available for querying by other users. To understand dimension writeback we will use the official Adventure Works 2008 sample Analysis Services project. Because we will make changes to the sample project, we recommend you make a copy of the sample project. The following steps ...