Dimension Writeback

Dimension writeback is another enhancement to dimensions and is available through the Dimension Intelligence Wizard. This is an important 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 to the dimension, Analysis Services propagates the change in the data to the relational data source and does an incremental process of the dimension, so that affected members are processed. During this time the dimension and cube will be available for querying for other users. The following steps show how to enable writeback on the Employee dimension and will help you understand the dimension writeback behavior by performing writeback operations on certain employees:

1. Open the Employee dimension in the dimension editor.

2. Click the icon to launch the Business Intelligence Wizard or select Dimension→Add Business Intelligence from the menus. Select Enable Dimension Writeback in the Business Intelligence Wizard as shown in Figure 8-49 and click Next.

   
   Figure 8-49

3. On the Enable Dimension Writeback page make sure the checkbox is enabled, as shown in Figure 8-50, and click Finish.

   
   Figure 8-50

4. The wizard changes the dimension property WriteEnabled to True. Instead of using the wizard, you can just change the WriteEnabled property to be True for all the dimensions for which you might have to update the data. Deploy the changes to the server.

5. After deployment succeeds, BIDS automatically switches the view from the Dimension Structure pane to the Dimension Browser pane. Click the Member Properties icon and select all the member properties related to the Employees dimension. Assume the employee John Wood got married and you need to update his marital status to married. Click the Writeback toolbar button or from the top menus select Dimension→Writeback to enter writeback mode. Double click the member property Marital Status for John Wood. The Marital Status field for John is now editable, as shown in Figure 8-51.

   
   Figure 8-51

6. Change the Marital Status field to M and then move the cursor to a different line. At this time BIDS sends the following dimension writeback request to the Analysis Services instance. You can see that an Update statement for the cube dimension $ Dim Employee is sent to the Analysis Services instance. Note the key attribute is critical for Analysis Server to make the appropriate update:

        <Update xsi:type="Update" xmlns:xsd="http://www.w3.org/2001/XMLSchema"     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"     xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">         <Object>             <Database>AnalysisServicesTutorial</Database>             <Cube>$Dim Employee</Cube>             <Dimension>Dim Employee</Dimension>         </Object>         <Attributes>             <Attribute>                 <AttributeName>Marital Status</AttributeName>                 <Keys>                     <Key xsi:type="xsd:string">M</Key>                 </Keys>             </Attribute>         </Attributes>         <Where>             <Attribute>                 <AttributeName>Dim Employee</AttributeName>                 <Keys>                     <Key xsi:type="xsd:int">275</Key>                 </Keys>             </Attribute>         </Where>     </Update> 

7. If the operation was successful you will be able to make other operations. If there are errors you should get feedback from the Analysis Services with the appropriate error messages.

   You have successfully completed enabling a dimension for writeback in the Employee dimension. You can perform additional operations such as creating new members, moving a member along with descendants, and deleting members through dimension writeback. These are operations that update the members or properties of members on dimensions and you will learn these operations with examples in detail in Chapter 11. You should be aware that you cannot change the values of the existing key attributes in the dimension because the key attribute is used by the Analysis Services instance to perform the writeback operation.