BPC 10.1 NW EPM add-in, what best practice for static template version updates using EPMSaveData?

[lisa_savinovich]

Hello BPC Experts,

We are currently on BPC 10.1 NW with the EPM add-in SP 38 patch 14 (build 11819). We have been using BPC for 10 plus years and continue to use (with minor updates periodically) the same static input template with EPMSaveData and EPMSaveComment functions that was developed on an older version of Excel (our current version is MS Office 365 version 2307). Planners continue to bring up and use the same static input template for a WBS element or Order that they used previously as long as that WBS Element or Order continues to a viable planning object rather than start anew with a new blank static template making it difficult to get adoption to a new template. Is there a best practice for the maintenance of static input templates or any input template? Should input templates static or dynamic be completely rewritten every couple of years in the latest version of Excel? With our current practice, we do occaisionally run into file corruption issues and wanted to query the community to see how input template versions are managed.

Thank you,

Lisa S.

[N1kh1l]

lisa.savinovich

lisa.savinovichGeneral rules which should be followed

• Improve existing logic based on new EPM features introduced like new EPM formulas etc.
• Improve existing logic with new Excel functionalities introduced like new formulas which can add value over old logic. Like SORT(), UNIQUE(), FILTER() etc.
• When new EPM or Excel versions comes, a regression testing has to be done for feature parity with older version before rolling out. Open the old templates in new EPM/Excel version and save it to server again.
• Keep copies of templates specially if using VB macros are in use as file corruption has become a major challenge lately with new excel version.

Br.

Nikhil

[lisa_savinovich]

Hello Nikhil,

Thank you for taking the time to respond. With respect to new EPM features and new EPM formulas, have there been any since SAP made a direction change to SAC and what have you found to improve the logic? Our current static input template for planning up to 4 years/48 months uses 8 EPM Functions: EPMSaveData (29,520), EPMSaveComment (410), EPMRetrieveData (820 to get rates and used to save $'s back rather than have default logic), EPMMemberProperty (916 to fill in data intersection), EPMSelectMember (1,028), EPMMemberDesc (617), EPMRefreshTime (2), and EPMMemberOffset (282) which total 33,595 EPM Function formulas.

We do perform regression testing with each new version of the EPM add (via Analysis for Office) and have copies of the static or non-dynamic input template; however, we have not made it a habit to open and re save each of the templates in use during the testing of a new version. We have a significant number of input templates as there is one per WBS Element or Order that we plan or forecast against. Once the first forecast is in motion post the full adoption of the new version, this does occur.

And yes, we too see various issues with Excel and the MS Office updates.

Best regards,

Lisa

[N1kh1l]

lisa.savinovich

There are very few updates coming to EPM after SP 38. Excel updates are frequent with O365 as it keep giving new formulas.

Looks like your forms are static ( hardcoded dimension members) or based on member recognition as you are not using the BPC report editor but I see you are using lot of EPM saves and EPM retrieve (poor performance as it fires 1 query for each EPM save). See if you can move the report design to actual report editor as it will give you a significant performance benefits. You can still hardcode your dimension members in case of asymmetric layouts in EPMOLAPmemberO formula.

Br.

Nikhil