CELL WRITEBACK

Learn MSBI: DIMENSION WRITEBACK and CELL WRITEBACK in Analysis...: Like Dimension Writeback, there are limitations with Cell Writeback also. For enabling Cell Writeback, all measures should have aggregation as SUM in measure group. If measure group contains other aggregations like count, distinct count, max etc. then move them to separate measure group.

Cell Writeback is not possible using graphical user interface like we have for dimension Writeback using SQL Server Management Studio (SSMS) or Business intelligence studio (BIDS). It can only be done by writing MDX queries through SSMS. However you can built your own front end application and pass right XMLA script to analysis server.

Let’s understand “how can we implement cell Writeback?”

There are two major steps:

1. Enable Cell Writeback for measure group/partition
2. Execute MDX queries for changing cell/tuple values for same measure group/partition

1. Enable Cell Writeback for measure group/partition

Go to cube’ Partition tab, right click on measure group partition and select “Writeback Settings…”.

It will open new pop up window, where your changes will be recorded. By default, table name will be WriteTable_.  You can change table name and data source if you want to record in some other table or data source.

Once you click "OK", it will create one extra partition (Writeback Partition) for recording changes.  Once you deploy solution, same SQL Server table will be created in Specified data source.

So now Cell Writeback has been enabled. Now let’s change some data in cube.

2. Execute MDX queries for changing cell/tuple values values for same measure group/partition

MSDN Document provides syntax definition for updating cube data.

In example, we have data from 2001 to 2004 in our cube:

view plaincopy to clipboardprint?

1. select  [Dim Product Writeback].[Dim Product Writeback].MEMBERS on 1   
2.       ,[Order Date].[Calendar Year].members on 0   
3.   from  [Adventure Writeback Cube]   
4.    where ([Measures].[Sales Amount])  

select  [Dim Product Writeback].[Dim Product Writeback].MEMBERS on 1
      ,[Order Date].[Calendar Year].members on 0
  from  [Adventure Writeback Cube]
   where ([Measures].[Sales Amount])

If business user has to enter data for 2005 then it can be done by writing UPDATE CUBE statement in SSMS:

Update [Adventure Writeback Cube]      

   set ([Order Date].[Hierarchy].[Calendar Year].&[2005]       ,[Measures].[Sales Amount]) = 10,000,000  

Update [Adventure Writeback Cube]   
   set ([Order Date].[Hierarchy].[Calendar Year].&[2005]
        ,[Measures].[Sales Amount]) = 10,000,000

One more important aspect, whenever user execute UPDATE statement server internally start new session for user with New BEGIN TRANSACTION. These changes will be visible to same user. Only when user COMMIT TRANSACTION by executing COMMIT Statement, it will be visible to others. User can ROLLBACK transactions using ROLLBACK.