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

Comments (20)

  1. J Hasselgren says:

    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

  2. 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

  3. Jhon says:

    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  

  4. 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

  5. Key in data only on leaf member says:

    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

  6. 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

  7. Adnan says:

    Great Article. Hats off

  8. Thanks Adnan,

    Hope it helped.

  9. Ingo Hoefker says:

    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

  10. © 2014 Microsoft Corporation. Terms of Use Trademarks Privacy & Cookies Report Abuse 5.6.426.415 says:

    © 2014 Microsoft Corporation. Terms of Use Trademarks Privacy & Cookies Report Abuse 5.6.426.415

  11. Marcelo says:

    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

  12. PW says:

    Very cool article. Thanks.

    Does Excel support SSAS dimension writeback? We want to give our users one dimension that they can update.

  13. Simon Lidberg says:

    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.

  14. Simon Lidberg says:

    Hello Marcelo,

    No this is not possible you need to have SQL as the backend

  15. Simon Lidberg says:

    PW no that is not possible from Excel I would look at the capabilities of MDS

  16. Matthew says:

    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

  17. Matthew says:

    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.

  18. Ashish Pinjani says:

    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.

  19. Publish to many to many says:

    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?

  20. BiSEUM says:

    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?