Cube writeback is used for budgeting, forecasting or any other scenario in which you need to enter data into your reporting structure. As an example you can imagine a budgeting scenario where actuals is provided by the source systems, while the budget amounts are entered in a front-end application such as Excel or Calumo.
The values entered manually are stored in a separate partition on the data warehouse and merged with the fact table. When using the Jet Data Manager, the write back partition is created and maintained automatically, even if the structure of the fact table changes.
When using writeback, Analysis Services will take the value entered and systematically prorate it across every dimension within the cube. For this reason, it is recommended that you only use writeback on cubes that have been designed specifically for this purpose. Using writeback on cubes with many dimensions will require a very powerful machine acting as the server. If your server does not have the resources to process writeback against a cube with too many dimensions, you will receive out of memory errors from the client end. For the best experience, design a cube that is primarily used for the writeback feature including only the dimensions necessary for the analysis you will be performing.
Step 1: Granting Writeback Permissions
Step 2: Enabling Writeback on a Cube
Step 3: Enabling Writeback within Excel
The first step to implementing writeback on your SSAS Multidimensional OLAP cubes is to grant the security permissions to an OLAP Security Role within the Jet Data Manager. To do so, first navigate to the 'Cubes' tab of the Jet Data Manager.
From here, right-click on your OLAP server name and select 'Advanced' -> 'OLAP Server User Rights' as shown below.
In the OLAP Access Control window that opens, first click on 'Cubes' in the left-hand pane (1). Next, identify the role and cube that you want to grant writeback permissions to and click on the dropdown arrow to select which permission set to grant (2). Typically this will be either the 'Read/Write' option or the 'Write with Drillthrough' option.
Upon selecting the permission set for each role and cube required, click on 'Deploy Rights' at the bottom right hand side of the window to push the permissions out to your server. You can then click 'OK' on the notification window that pops up and then click 'Close' within the OLAP Access Control window.
Now that security permissions have been granted, the next step is to edit the cube in which you are implementing writeback capabilities. For this, right-click on desired cube and select 'Edit Cube'
In the 'Edit Cube' window that opens, simply check the box next to 'Enable WriteBack' as shown below and then click 'OK' to continue.
Once writeback is enabled on the cube, and the necessary OLAP security permissions have been granted to your user(s), you can begin writing back data to your cube.
To start, open Microsoft Excel. In your Jet Essentials ribbon, click on the 'PivotTable' button and select the 'Pivot Table' option.
In the window that opens up, select the data source and cube that you have enabled writeback on and then click 'OK' to proceed.
With your PivotTable built to your liking, first click anywhere in your PivotTable and then click on the 'Analyze' tab in Excel and select 'OLAP Tools' -> 'What-If Analysis' -> 'Enable What-If Analysis'.
Once you have enabled 'What-If Analysis' within Excel, click in any cell of your PivotTable and type in a value different than the original. Once a cell has been changed, you will see a small red triangle in the lower right corner of the cell. If you click on the cell, you will see a 'What-If Analysis Options' button available just to the right of the cell. To 'writeback' your changes to your cube, click on the 'What-If Analysis Options' and select 'Calculate PivotTable with Change'.