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 https://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

Comments

• Anonymous
  May 26, 2011
  Thanx again, It's etremely cool that it creates a separate table holding the writeback values. This will be very useful. Do you have any ideas of the scalability of this solution. Some ball park figures of the size of the cube or similar? /J Hasselgren , HNO3 BI AB

• Anonymous
  May 26, 2011
  Hi Jörgen, The write-back capabilities is very scalable. We have customers running it in very large environments, we also run it internally for some of our financial reporting; see technet.microsoft.com/.../gg191725.aspx One thing that you need to consider is that in order to get it to perform you typically need to specify a separate writeback cube. In this cube you will probably both change the grain as well as minimize the number of dimensions you use. There is also some guidance in the following document from the SQL CAT team: blogs.msdn.com/.../proper-partitioning-can-improve-dramatically-the-writeback-process-when-dealing-with-large-data-sets.aspx Hope that this helps, Simon

• Anonymous
  June 07, 2012
  Well I have created a simple cube but when I processed it I got error of something like key attribute was not found in " " table and attribute is " " where value is " ". do you have any suggestion for that  

• Anonymous
  June 11, 2012
  Hello Jhon, do you have any more information about the type of cube that you have created. This problem is something that you get when you have rows with values in your fact table that does not exist in your dimension. Simon

• Anonymous
  October 30, 2012
  Hi Simon The writeback feature on Excel 2010 is quite cool, is that possible to allow user to key in data only on the leaf member? that means no allocation happened. Implement this in SSAS or Excel VBA? Thanks Tom

• Anonymous
  October 31, 2012
  Hi Tom, You can probably achieve what you want through security in SSAS and through crafting your excel template to just show data on the lowest level. Simon

• Anonymous
  April 21, 2013
  Great Article. Hats off

• Anonymous
  April 22, 2013
  Thanks Adnan, Hope it helped.

• Anonymous
  January 25, 2014
  Hi Simon, thanks for the article. Imagine that the user wants to add a comment on every changed value in the table. How would you catch that added info and present it later in the reports? Best, Ingo

• Anonymous
  May 12, 2014
  The comment has been removed

• Anonymous
  August 19, 2014
  Hello Simon, I am not very familiar with databases but I do have a simple MS Access 2013 as source data for a pivot table and I would like to use the writeback functionality. However, the pivot table What-If button is greyed out. Is it possible to use this feature with MS Access database? Thanks, Marcelo

• Anonymous
  December 30, 2014
  Very cool article. Thanks. Does Excel support SSAS dimension writeback? We want to give our users one dimension that they can update.

• Anonymous
  December 31, 2014
  Hi Ingo, sorry that I have missed your comment, I would say that this is quite hard to do if that is a requirement then it is probably better to look at some of the ISV solutions that exist.

• Anonymous
  December 31, 2014
  Hello Marcelo, No this is not possible you need to have SQL as the backend

• Anonymous
  December 31, 2014
  PW no that is not possible from Excel I would look at the capabilities of MDS

• Anonymous
  April 01, 2015
  Hello Simon, I have developed some writeback cubes.  When I enter values and hit enter the cube always does some processing.  It's not publishing the data just refreshing the values.  I was wondering if there was a way to turn this off so I can enter a lot of values without the cube refreshing everytime i hit enter.  i tried setting the calculation option to manual but that doesn't do anything for this.  Any advice

• Anonymous
  April 01, 2015
  This seems to only be an issue when you use sets.  If I have no sets defined it works as I would want it to.  Where I can enter a value I want to publish.  Hit enter again and it will drop to the next cell without refreshing the cube.

• Anonymous
  April 06, 2015
  How do we delete values? As you set 20000 and Published changes these are written back to the cube. Now we have the option to overwrite it will 15000 or 25000 and that works well. However I dont want to set a number for this and want this to be blank, how do I delete it? Dont see an option to do that. PS setting it to 0 is not an option. 0 is still a number and it feels like a target for 0 is set for that customer. Thanks.

• Anonymous
  April 07, 2015
  Hi Simon.  Another question.  I have added a many to many rolling month dimension to a forecasting cube which will allow the sales team to pull only the recent 3 mo, 6 mos or 12 mos to pair down their reports before they start publishing data to the cube.  The issue is that ssas won't allow for writeback when a many to many reference has been defined against the measures table.  Is there way to set this up so as they publish that dimension will be excluded when ssas generates the update statement?

• Anonymous
  August 11, 2015
  Hello Simon Thank you for this post. I just have a question. Have you an idea about how to freeze cells (leaves or parents) so that they are not impacted by during the calculation of allocation using Excel and SSAS with write-back implementation?

• Anonymous
  February 10, 2017
  Hello Simon,I have a writeback excel file which publishes the data back to the database, but while the cube is processing the publish button freezes in the excel file for that period of time approx 6-7 min till cube is processing. It only publishes data after the cube has finished processing.Is there any way or option to reduce the freeze time or completely eliminate this problem so that even during cube processing i can writeback data to the table in the database.Thanks.Ranjan.