# Excel 2010 PivotTable What-If Analysis (Writeback)

Thanks to Diego Oppenheimer for putting together this post.

When thinking of Excel as an OLAP analytical tool the first thing that usually comes to mind is the ability to quickly and easily analyze data from an OLAP data source. With the introduction of PivotTable What-If Analysis in Excel 2010 you can now easily modify this data as well. Put simply, PivotTable What-If Analysis is the ability to modify values in PivotTable cells, recalculate the PivotTable with those values and, if the results are satisfactory, publish the changes so that they are reflected in the OLAP data source for other people to see. This feature is also called writeback, though it really goes beyond just the ability to write values back to the cube. “What if our sales for New York increased by 10%?” “What if the total number of product orders fell by 25%?” These are examples of questions that PivotTable What-If Analysis is aimed at helping the user answer.

Let’s take a look at the following scenario:

Stephen, the sales manager at a small IT company, is trying to figure out what his optimal sales quotas should look like for 2010. Based on a previous analysis and forecasts for next year it seems like 2010 will have a large decrease in sales which would mean his quotas would be reduced by 10% from the 2009 quotas. With the new PivotTable What-If Analysis feature he can now modify the relevant values in his PivotTable report and calculate the PivotTable with the changes to see what the totals would look like without actually modifying the data source. (click to see larger image)

So here is what his PivotTable looks like after calculating with the changes:

If he wants to keep these numbers and share them with other people he can go ahead and publish the changes to the OLAP data source.

He can also just keep the changes locally without publishing back to the OLAP data source by simply saving the file. When the workbook is loaded again in Excel 2010 all the changes will be reapplied and the PivotTable recalculated.

With little knowledge of OLAP data sources the manager was able to change values and recalculate his PivotTables. The changes can be shared across the organization by simply sharing the workbook or publishing the changes back to the OLAP data source.

Advanced options for PivotTable What-If Analysis include being able to change the allocation method which is the methodology in which the OLAP data source will allocate the newly entered values in the cube. Excel 2010 also gives you the ability to define your own weight allocation definitions from within an MDX editor.

We on the Excel team are excited about this feature for the possibilities it brings to our users and as always your feedback is much appreciated and welcomed.

• Diego, it's good to see this OLAP feature surface in the Excel UI. In this example, I'd say that Stephen's 2010 forecast is doing pretty well, given that he started out with a forecast almost 50% less than 2009 actuals! (\$10,230,000 vs. \$4,536,000).

Joking aside, I think that its important to note the limitation of this feature i.e. it works only when simple sum aggregations are involved.

A couple of questions:

1) Could you do a follow up post to explain how some of the different allocation options are used?

2) Is it by "design" that you can't use an external worksheet reference for the values you want to change in the PivotTable, or is this a CTP bug?

• Hello,

To this point I have not been able to use this feature. I have been waiting for it since I first started using excel...a long time ago and now it has finally arrived. However, can someone show how ot use with "gemini" data? I am able to enable the feature...but when I start changing the data and want to save it I get an error message.

Thank you,

Lum

• Lum, Gemini doesn't support Writeback.

• So my only option is to create an olap cube? Do you know if the olap cube wizard is now available in excel 2010?

• One more question...I would really like to be able to writeback on pivot tables that are based on excel speadsheet data...is that a possibility?

• Lum, Writeback is available with SSAS cubes *only*.

• Will 3rd party OLAP providers be able to make use of this functionality?

• Interesting stuff - Am I right in thinking this needs the Enterprise version of SQL Server Analysis Services?

• Sorry, I should have read the comments closer...  I assume by "SSAS cubes *only*", that means no, 3rd party OLAP providers will not be supported.

• What about the time to commit the writeback values to the spreadsheet and  to the SSAS cube? This has been an issue since this feature appeared in OLAP Services, ten years ago.

• Casey, by SSAS cubes only I was comparing with other Microsoft PivotTable types. As for third party OLAP providers, I can't say. However, a few things should be noted:

1) The third party OLAP provider would have to support Excel PivotTables.

2) Writeback to the server is specific to SSAS architecture. SSAS creates a writeback partition to store the writeback information. A third party OLAP provider would have to support writeback and understand the XMLA commands sent to the OLAP server.

3) It isn't clear what checks Excel uses to enable/disable the feature. It's disabled for standard PivotTables but enabled for Gemini PivotTables, although the latter doesn't support the feature.

4) It's not clear why the feature couldn't be enabled for workbook storage only in cases where writeback isn't explicitly supported on the server (in the case of Gemini, for instance).

• Has anyone been able to get the gemini help file to work. I have downloaded it...but it seems like the URL that it needs to retrive the help items is bad. It looks like it is still pointing to a local microsoft machine. It would be great if anyone got it to work and could share.

• @Thomas: if you are basing SSAS writeback performance on 10 year old versions of SSAS you need to take a look at SQL 2008. Writeback performance is much improved. (Even SQL 2005 was a big improvement over SQL 2000)

(although to some degree, yes, I agree if you writeback a large volume of data you will need to sweep the writeback table back into the fact table to get back to the original performance)

@Colin: if the data source is OLAP I believe writeback is enabled. There's a number of things that make it hard to determine ahead of time if a given cell is writable (it's version dependent and not always accurate anyway). So I think Excel does the right thing by simply trying it and let SQL issue an error.

Also can't you still write to leaf levels if the aggregation isn't sum, or am I remembering that wrong off the top of my head?

• @ Colin Banfield

"Could you do a follow up post to explain how some of the different allocation options are used?"

-Although the next couple of blogs posts have been planned out I am sure this can be arranged in the future.

2) Is it by "design" that you can't use an external worksheet reference for the values you want to change in the PivotTable, or is this a CTP bug?

No that is not by design, just a bug present in Technical Preview.

• At this time writeback in Excel 2010 is just leveraging the Writeback feature of SSAS. Assuming a provider was written to mimic the architecture it would be possible to use other data providers.

"It isn't clear what checks Excel uses to enable/disable the feature. It's disabled for standard PivotTables but enabled for Gemini PivotTables, although the latter doesn't support the feature."

This is just a bug in Technical Preview.

@ Lum

The SSAS cube creation wizard is not available in Excel 2010.