You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Tips
How-to
News
Videos
Stories
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.
Comments: (24) Collapse
"No that is not by design, just a bug present in Technical Preview."
Well, I submitted a bug report for this problem way back on Auguts 18th. I just received the official resolution as "By Design."
Here's the explanation:
"Thanks for the bug submission. This behavior is by design since we do not want to get into infinite loops when using formulas for values to write-back in PivotTables. So automatically calculate does only work for non-formula (numeric) values in PivotTable value cells."
It's unfortunate that Excel doesn't at least generate an error message when you attempt to use an external reference.
Are there any improvements to Calculated fields and Calculated items in a Normal pivot Table.
Will we be able to use Range formulas, refer to cells out side the table etc
Anyone able to get the help file to work?
@ Colin
You can have external references (to other workbooks) in What-If Analysis value cells but Excel 2010 does not support references to external names.
@Sam
No improvements in this area for this release. If you want to send me something explaining what sort of improvement you would want in this area that would be great (diego@microsoft.com)
@Lum
What help file are you referring to ?
Diego, I don't mean to belabor the issue, but the scenario I described in my bug report was using a simple reference on the *same* worksheet as the PivotTable. For example, in the PivotTable cell entering something like =H5 instead of a fixed value like 435670. When the value in H5 changes, the cell in the PivotTable is updated, but the PivotTable is not recalculated (although set to automatic). It was this simple scenario that was indicated as not being supported. Was my report misinterpreted?
@Colin Banfield
Sorry about the confusion I misinterpreted your scenario. The answer you got is correct we do not allow automatic recalculation of formulas in What-if Analysis. The reasoning for this was to avoid infinite loop scenarios.
A couple of more scenarios not supported with What-if analysis are:
- Structured references in value cells (Ex: Structured references to tables).
- External names (names referencing another workbook)
- "Advanced formulas (ex: UNION, INTERSECT)
Are there file size limitations regarding using this with Access data or internal Excel data?
@ Billy Gee
This is an OLAP only feature. What this means is that only OLAP Engines like Microsoft SQL Analysis Services can be used as data sources for the What-if Analysis feature.
Here are this and some other articles on SSAS Write Back: <a href="ssas-wiki.com/.../Articles
Comments: (loading) Collapse