Microsoft Office Standard Excel 2010 PivotTable Wh

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,Office 2007 Enterprise Key, PivotTable What-If Analysis is the ability to modify values in PivotTable cells,Office 2010 Pro Plus, recalculate the PivotTable with those values and, if the results are satisfactory,Windows 7 Code/, publish the changes so that they are reflected in the OLAP data source for other people to see. This feature is also called writeback,Office Ultimate 2007 Key, 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,Microsoft Office Standard, 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 OptionsAdvanced 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. <div