martedì 31 gennaio 2012 12:47
One of our customer whats to do What if analysis using SSAS and Excel 2010.
1.User what to do salary impact analysis
2. What would be the Impact at department level, if they modified a component for a department level?
3. What would be the Impact at grade level, if they modified a component for a grade?
4. T would be the Impact at Organization level, if they modified a component.
Basis of my search in web I did the following for what if,
I have connected excel with SSAS cube and Created Pivot table (enabled Write back at cube level) and enabled what if at cell level.(after dragging the values in pivot table)
Then Modified the Higher level value in pivot (selected automatically calculate option), hence the values which I entered are distributed at lower level element.
The above type of what if not helpful to my scenario.
pls advice me how to do what if analysis with custom formula.
provide me any link if you know it..
and also let me know it is possible to edit excel pivot cell in web browser/Sharepoint page/PPS dashboard?
Tutte le risposte
venerdì 3 febbraio 2012 08:53Moderatore
It appears to me that there is no build-in feature to achieve what if analysis with custom formula currently,
However here I still would recommend you to submit a feedback to Microsoft Connect at this link https://connect.microsoft.com/SQLServer/Feedback This connect site is a connection point between you and Microsoft. Your feedback enables Microsoft to make software and services the best that they can be, Microsoft might consider to add this feature in the following release after official confirmation.
TechNet Community Support
- Contrassegnato come risposta Challen FuModerator lunedì 13 febbraio 2012 09:07
giovedì 5 luglio 2012 06:44
"Then Modified the Higher level value in pivot (selected automatically calculate option), hence the values which I entered are distributed at lower level element" - This is the 'spreading' functionality.
"What would be the Impact at department level, if they modified a component for a department level" - For this you have to define your SSAS cubes accordingly with proper dimensional modelling and hierarchy levels. Then you have to create calculated measure for 'salary' . In the calculation formula, you should use the 'component for a department level' - this will enable the 'Salary' to be recalculated on changing the 'component for a department level' . Now enable the write-back partion and connect this cube from Excel 2010 and enable 'What-if' analysis. On changing the 'component for a department level' in the Excel Pivto table, you will instantly see the impact on 'Salary'.
Hope this helps.
- Modificato JoseVargheseKonooran giovedì 5 luglio 2012 06:46 spell check