Enabling Writeback to SSAS in Excel 2010
04/05/2011 1 Comment
Main topic of last week’s community meeting in ExcelBI.dk was Writeback in Excel 2010.
With Excel 2010 it is finally possible to modify data in a SSAS cube, without using any macros or third party add-ins, and it works like a charm. This opens for a lot of common business scenarios like budgeting and planning directly from Excel 2010.
Setup is very easy. Here some few guidelines on how to enable Writeback to Adventure Works.
Writeback has to be enabled on the specific measure group on which you want to modify data. This can be done from either Business Intelligence Development Studio (BIDS) or SQL Server Management Studio (SSMS). Here I’ll focus on how to do it from BIDS:
On the partition tab of the Cube Designer choose the wanted measure group and right-click on one of the partitions. (Writeback can only be enable on measure groups where all measures has the Aggregation Type set to Sum)
Select Writeback Settings and choose the name for the Writeback table that SSAS will create in the selected Data Source.
After selecting OK, you need to deploy and process the cube, before you can go to the Data Source and review the new Writeback table in SSMS.
One last thing you need to do in BIDS is to setup security to allow Writeback to the end users. This is done on the Cubes tab in the Cube Role Editor, by setting the Access property to Read/Write.
Seen from a SSAS perspective Writeback is now possible from Pivot Tables in Excel 2010.
After connecting a Pivot Table in Excel 2010 to the modified SSAS cube one last thing needs to be done before it’s possible to modify the data from Excel.
What If-Analysis needs to be enabled. This is done by selecting What–If Analysis under Pivot Table Setting in the Ribbon.
Now you can modify the data in the Pivot Table.
A little triangle will appear in the right-bottom-corner of the cell, after data has been modified. When selecting the modified cell, a menu shows up beside the triangle, from which you can recalculate the Pivot Table to show the new totals.
To commit the changes back to the cube (before committing changes will only exist in the Pivot Table) you have to select Publish Changes under What If-Analysis in the Ribbon.
After data has been published the little triangle disappears from the cell, and data it can be found in the Writeback table in the Data Source.
Notice that entries have been allocated to the leaf level of the measure group.
In Excel it is possible to allocate entries on non-leaf level in some very interesting ways.
Because all entries will be allocated and written to leaf level of the measure group, choosing the right data model for you Writeback measure group, becomes very important to keep good performance.
Above are both interesting topics that I’ll maybe cover in a future post.