Writeback to Analysis Services cubes from Excel 2010
Analysis Services have had write back functionality for a long time; however there has been no client support for writeback in the most used BI client on the market (Excel).
One of the new features in Excel 2010 is the ability to perform writeback to Analysis Services cubes. In order to write back to a cube you need to configure a write back partition. This blog post shows how to do this in a step-by-step fashion using the AdventureWorks samples found on http://msftasprodsamples.codeplex.com/
First thing that we need to do is to create a new Analysis services project in Business Intelligence Development Studio:
Once the project has been created you need to create a new data source.
Connect to the SQL Server Database that you want to create your cube against:
After the connection has been created you need to create a data source view
In my example I add the following tables to my data source view
For display purposes I create a new named calculation in the DimEmployee table
The syntax of the calculation is FirstName + ' ' + LastName
Next step is to create the dimensions. Start with the employee dimension first and choose the following properties:
After the dimension has been created right click on the Parent Employee and choose properties:
Specify that the FullName column should be the name column for the Parent Employee Hiearchy.
Create the date dimension; specify that the Name column should be the FullAlternateDateKey column.
Specify that the following attributes should be added to the dimension:
On the properties for Month
specify that the name column is the EnglishMonthName
Then configure the key column as the picture below:
Now the following hierarchy can be created:
The next step would be to create a cube
Specify the FactSalesQuota is your measure group table:
The only measure should be the Sales Amount Quota:
Click through the wizard so that the cube is created. The last step would be to create a writeback partition. Go to the partion tab and right click the existing partion. Now you can specify the writeback settings
Specify the table name for the writeback table
The next step necessary would be to set the impersonation account settings:
You need to provide an account that has the rights to create a table in the data source. In my case I use the service account.
Now the cube can be deployed and processed
Now you can start Excel 2010 and create a connection to the cube:
Create a PivotTable with the following characteristics
In order to enable writeback to the cube it has to be enabled on the PivotTable, this can be done on the What-if Analysis button on the PivotTable Options tab.
After this has been done you can write to the cells in the PivotTable
This will change the PivotTable but not write the data to the database. In order to write the changes to the database you need to publish your changes:
Now the data has been written to the writeback table including the time it was changed and by whom
Hope that you have enjoyed this introduction to writeback from Excel 2010 to SSAS cubes. The steps can be found recorded in a screen cast as well on the following link: https://www.youtube.com/watch?v=RVfmoRGlSu0