none
What IF Analysis with Custom Calculation

    Question

  • All,

    One of our customer whats to do What if analysis using SSAS and Excel 2010.

    Requirement is:

      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?

     

    Thanks.

    bas

     

    mardi 31 janvier 2012 12:47

Réponses

  • 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.

    Regards,


    Challen Fu

    TechNet Community Support

    vendredi 3 février 2012 08:53

Toutes les réponses

  • 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.

    Regards,


    Challen Fu

    TechNet Community Support

    vendredi 3 février 2012 08:53
  • Hi Baskar,

    "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.

    Jose Varghese

    josekonoor@yahoo.com


    jeudi 5 juillet 2012 06:44