Office 2010: Excel 2010, What-If Analysis aka Microsoft Finally has Built in Cube/OLAP Writeback!

Digging into the blog post from earlier this summer I wanted to see what was new and exciting in Excel 2010.

Recently I have been working on an cube and we want to be able to budget right from the cube. There are also many other cubes/scenarios where the ability to writeback to the cube would be awesome. Some BI tools have had this for many years! Microsoft had something similar with a Excel 2002/2003 add-in, but it has been removed. Also, there are many 3rd party tools to allow this. You could also write your own macros or VBA/.NET code to do this as well, but what was always missing was the ability to do writeback directly from an Excel (OLAP) PivotTable. With Excel 2010, this functionally finally shows up.

First, you need a cube, and you need to enable writeback on a partition. This will automatically create a table in your database where you have your data warehouse. Lets say you have a table FactBudgets, and you enable writeback, SSAS will create a table WriteTable_Budgets. This is a trivial example I went through to test this functionality, but I just wanted to exhibit the feature of the writeback.

01_writeback

Turn on writeback, deploy and process your SSAS cube, and then open Excel 2010. Connect to your cube, and then in the PivotTable ribbon menu, on options, there is a button to turn on “What-If Analysis”. Turn it on. :)
02_whatif

Once you turn on the setting, you can then begin writing back values to your cube, right from Excel. If you click on a cell in your writeback value, you can just change it. See on the screen shot below, the value I changed, the little purple triangle, tells me it has a changed value from what is in the data source.

03_valuechanged
04_valuechangedmenu

You can see its telling me the value changed, I can Discard the change and other options. After you have your values set, you want to publish them, which you do back on the PivotTable ribbon menu:

05_publishchanges

As you can see, Microsoft has finally created a viable solution for writing back values into your OLAP cubes without the need for 3rd party software or coding. Finally!

One thing to note, if you try to write back to a value that is in a partition that doesn’t have writeback enabled, you will get an error.

06_error

Now, think of the possibilities with SSAS OLAP writeback and Excel, now that we can actually use it out of the box!

About these ads

2 thoughts on “Office 2010: Excel 2010, What-If Analysis aka Microsoft Finally has Built in Cube/OLAP Writeback!”

  1. Hi,I saw this functionality when I try Office 2010. But I've got an issue using it with Sql Server 2008 R2. When I publish my changes, the changed values turned to 0 and I've got no way to retrieved the values I've wrote.Does anybody has the same behavior?Sebastien

    Like

  2. Do you have to have writeback enabled to use this functionality with a BI cube at all?  I.e. can you just have the pivot table recalulate without publishing the what-if value?  I am getting an internal error when I try this.  

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s