SAP IBP, Add-In for Microsoft Excel - Planning View Definition

General Performance Recommendations for Planning Views

Some formatting and certain other additions to planning views can have unexpectedly strong impacts
on performance.

Please check the SAP Note 2153455 for details and recommendations:
[Link]

Also, please check if there are any “big” images embedded, such as your company logo, in the
planning view. Some small images can lead to large workbooks and have a negative impact if they
have a very high resolution, etc.

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 1

SAP IBP, Add-In for Microsoft Excel - Planning View Definition
Data Volume / Planning View Size

Large planning views can become unresponsive and cumbersome to use for end users.
The SAP IBP, add-in for Microsoft Excel (Excel add-in) is built for interactive planning where
planners view and change numbers interactively and simulate the effect of these changes to the
overall plan. The Excel add-in planning views are often defined at an aggregated level and with
filters, representing aggregations or views into very large data sets of 100's of millions of data
points.
For good performance, we recommend a planning view of not more than 2000 rows per key
figure for a grid with a typical width up to 24 time periods (or equivalent data volumes in
other layouts). This is a (default) limit that the system checks against. Note that a Web User
Interface for planning typically would only be able handle a few 100 rows of data, and so the Excel
add-in does pose an advantage for planning larger datasets.
Again, this is a recommendation for performance and usability.
Please check the SAP Note 2108186 for details:
[Link]

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 2

SAP IBP, Add-In for Microsoft Excel - Planning View Definition
Filters

It is strongly recommended to use filters in planning views so that only a subset of the data
needs to be loaded which is at that point relevant for the end user.

- Apply permission filters and Read / Write restrictions to the users

- Force / ask users to use planning view filters

(global configuration parameter FORCE_PLANNING_VIEW_FILTER)

- Reduce opening times of templates by using the template admin filters (see next slide)

→ Best Practice: Filter templates rigorously. Templates with small amounts of data are small and
load faster for everyone, including the people who use them in the New View dialog.

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 3

SAP IBP, Add-In for Microsoft Excel - Planning View Definition
Template Settings to set Filter Criteria in Templates

Using the template admin filters has the potential to significantly improve the load times of templates from the server. It is a
recommended best practice to use these for all templates.

Example: You have a planning view template with multiple filters that you want the user to use, but these cover still a lot of data and you
experience long opening times for the template. You can set these filters to “suggestion” or “mandatory”.

In addition, you can define more filter criteria on a very low level, such as Product ID / Location ID /Customer ID and set them to “Don’t Copy”.
Filters for templates do not change the data which is shown when you open a planning view via "New View → From Template...", but it
decreases the time while opening as it limits the size of the template itself.
Opening the template via the “Template Admin” group in the SAP IBP ribbon would set these filters however. But usually, this path is open only
to Template Admins and users would go via “New View - From Template...",

On the Filter tab of the Planning View Definition screen, click “Edit Template Settings“:

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 4

 SAP IBP, Add-In for Microsoft Excel - Planning View Definition
Options (1)

Set option "Keep formulas static that reference planning

view cells“
This option will improve performance if your planning view is
slow to respond while increasing or shrinking in size.
Unless …
- the workbook contains formulas referencing the planning
view, or
- the worksheet has any content in the rows below the
planning view,

… you should always select this checkbox. There are few

cases where this could have a neutral or negative impact on
the performance.

Use the setting “Keep formula on data” cautiously and only

if needed, as this can have a negative impact on the
performance.

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 5

SAP IBP, Add-In for Microsoft Excel - Planning View Definition
Options (2)

It can be beneficial for the performance of your

planning views to enable the “User Microsoft
Excel cell references” setting when you want
to use the “Repeat row headers” option.
This can improve the performance of the Write
Data step when planning views are opened or
refreshed.

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 6

SAP IBP, Add-In for Microsoft Excel - Planning View Definition
Formatting via the SAP IBP Formatting Sheet

Custom formatting rules are a best practice when it comes to usability. For example, showing a user via
formatting if a cell is editable or displaying percentages as such with % sign, etc. To format cells of a planning
view dynamically, you need to use the SAP IBP Formatting Sheet, see SAP Help Portal at SAP IBP Formatting
Sheet for further details.

BUT applying the formatting can take a significant amount of time, especially if the formatting changes often from
row to row. Rules that are not used usually don’t have a significant effect, but please keep the number
reasonable. Most importantly, measure the performance impact of the rules you have defined.

You can find basic formatting rules set-up in the SAP IBP Formatting Sheet included in our Sample VBA
templates available in our GitHub for sample planning view templates.

We are continuously working on improving the performance. So you may also check the time it takes to format a
planning view with the newest available version of the Excel add-in.

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 7

SAP IBP, Add-In for Microsoft Excel - Planning View Definition
SAP IBP Formatting – Alternating formats ("banding")

Good formatting sheets don‘t contain too many rules, and are defined by
“ranges“, as the handover of the rules to Microsoft Excel when opening the
planning view is faster when the formatting can be applied to a broader
range in the planning view.

Examples:
• If possible, arrange the key figures in the planning view in a way that key figures
with the same formatting rules applied are grouped together, such as, for
example, having all editable key figures at the top and the non-editable key
figures below.
• When you apply the same formatting to multiple key figures,
use the “Union“ flag or if using Excel add-in version 2302.2.0 and newer select
them in one rule as shown in the screenshot to the right. Also check the
following example at the SAP Help Portal: Combining Multiple Conditions in
One Formatting Rule

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 8

SAP IBP, Add-In for Microsoft Excel - Planning View Definition
SAP IBP Formatting – Cell Format

With versions prior to 2305.2.0 of the Excel add-in, we

recommended using “All” in the Cell Format dialog whenever
possible.
This rule can be revised: As of Excel add-in version 2305.2.0
and newer, we recommend only choosing the cell format that
is really needed instead of selecting “All” in the Cell Format
dialog.
For example, if you want to have percentage key figures
formatted as percentages you may want to have the default
formatting applied to the related data cells and just have the
“NumberFormat” adjusted/ overwritten or for totals you want to
only apply the bold format as shown below.

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 9

 SAP IBP, Add-In for Microsoft Excel - Planning View Definition
Excel Calculations

Local Member and custom formulas on planning views provide a flexible and powerful way to
enhance the information shown on the planning view. However, to optimize the performance by
speeding up the rendering of planning views, use formulas and Excel calculations only where
needed, and check if a key figure calculation in the backend could do the same job.

• Local members
Local members are formulas that need to be re-created and re-evaluated after each rendering of
the planning view (Open, Refresh, Save,…)

• Many / complex formulas referencing the planning view (directly or indirectly)

Excel will re-evaluate them after each rendering of the planning view (Open, Refresh, Save,…)

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 10

SAP IBP, Add-In for Microsoft Excel - Planning View Definition
Performance Impact of SAP IBP Formatting – Migration

With the 2005 version of the SAP IBP, add-in for Microsoft Excel (Excel add-in), your previously
created EPM formatting sheets that are contained in existing templates and favorites are migrated
on the fly when a user with Excel add-in version 2005 and later versions is opening these.

The ad-hoc migration of the EPM formatting sheet to an SAP IBP formatting sheet can impact the
performance depending on the size (the amount of formatting rules) in your EPM formatting sheet.
In most cases, this impact is way below one second (e.g. 300ms).

However, when optimizing performance, it makes sense to check this impact and update / save the
templates and favorites again with Excel add-in version 2005 or later in case the impact is high. By
updating or adding it again, the template or favorite are fully migrated and the SAP IBP formatting
sheet is saved. By this, the ad-hoc migration when opening a template or favorite is discontinued.

In the performance trace, you can find a new method called

Method [Migrate EPM Formatting Sheet] providing the seconds it took to migrate the EPM
formatting sheets on the fly.
© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 11
SAP IBP, Add-In for Microsoft Excel - Planning View Definition
Charts and other VBA Controls

Charts and other features (e.g. based on custom VBA code) are a usability addition to a planning view. To
optimize performance, check per planning view, if charts and other features are necessary, test the performance
and remove them if they are not used.
With newer versions of the VBA sample templates provided in our GitHub, it is sufficient to just hide/ collapse
the chart, as the underlying VBA code is only executed if the chart is expanded. If you have created your
templates based on older sample templates, it might be necessary to remove the code and the controls
completely.
→ Best Practice: Use formulas and macros wisely. While macros are usually faster than many huge formulas, it
can still slow down a template significantly if overused or if the macro has to run through thousands of rows.
→ Best Practice: Allow users to trigger complex macros manually
and for selected cells/ranges (e.g. via buttons on the planning
view) instead of running them automatically across the whole
worksheet each time a planning view is opened / refreshed.
→ Opening a .xlsm file (Workbook with VBA macros) vs a .xlsx file (workbook without VBA macros) can be
significantly slower due to additional checks from Microsoft Excel. The virus scanner can also have an
impact here.
© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 12
Performance Considerations:
Local User’s PC

PUBLIC
Local User’s PC
Hardware of the PC where the SAP IBP, Add-In for Microsoft Excel is installed

The hardware of the local user’s PC can have an impact on the performance of the SAP IBP
planning views. Factors such as more memory and CPUs, faster hard disks, etc. help to speed up
operations that are mainly driven by Microsoft Excel (e.g. rendering of planning views, apply
formatting, apply local calculations, …)
Please read SAP Note 2135948 ([Link] carefully and
make sure that all listed hardware requirements are at least met.

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 14

Local User’s PC
Software of the PC where the SAP IBP, Add-In for Microsoft Excel is installed

The operating system, Microsoft Office Version, and other software that is installed on the local
user’s PC can have an impact on the performance of the SAP IBP planning views. The same tests
on different Microsoft Office versions therefore can lead to different results when measuring the
performance of e.g. opening a Planning View via the SAP IBP, add-in for Microsoft Excel.

Please read SAP Note 2135948 ([Link] and make

sure that all listed prerequisites, such as the minimum .NET version are met.

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 15

Local User’s PC
Software of the PC - Microsoft Office 32-bit vs 64-bit

Both, the Microsoft Office 32-bit vs 64-bit versions are good to use with the SAP IBP, add-in for
Microsoft Excel.

However, the Microsoft Office 64-bit versions usually offer more usable memory.
So when your PC often runs into out-of-memory exceptions or if the PC often gets rather slow
because the memory limit is almost reached, please

1) check the Microsoft KBA „Large Address Aware capability change for Excel” for the 32-bit
versions which can help the Excel software to be able to consume double the memory:
[Link]
excel

2) test if a change to the 64-bit Excel could make sense.

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 16

Local User’s PC
Disable Microsoft Office Clipboard (1)

When installing the SAP IBP, add-in for Microsoft Excel 2005 version, you will see a new advanced set-up menu with 2
additional options to choose from.

The first one lets you choose if you want to disable the Microsoft Office Clipboard for
Microsoft Office applications on that PC. Within this clipboard, users can usually
find the history of their copy activities (e.g. when you copy text via CTRL + C).
Please note that the normal Copy and Paste actions are still possible also with
a disabled Microsoft Office Clipboard.

By default, it is set to “Disabled”. If you wish to continue to use the Microsoft Office
Clipboard, you can uncheck this option and it will be continued to be enabled.

It is recommended to disable it due to performance considerations. Please find further information in this Microsoft Support Note:
[Link] and
further information: [Link]
c2931e73ec8a

Please note that the Microsoft Office Clipboard can only be disabled during installation when no other Microsoft Office applications
(e.g. Word, Power Point, Excel, Outlook) is open. The Microsoft Office Clipboard deactivation required changes to the registry keys.
These can only be changed when all Microsoft Office are closed at the time of the installation of the SAP IBP, add-in for Microsoft
Excel. If any of the applications are running during installation, the Microsoft Office Clipboard remains enabled.
© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 17
Local User’s PC
Disable Microsoft Office Clipboard (2) - Troubleshooting

Please note that the Microsoft Office Clipboard

can only be disabled during installation when not
currently used by any of the Microsoft Office
applications (e.g. Word, Power Point, Excel,
Outlook).

You can easily check if it was indeed disabled by

opening an office application and then try
opening the Microsoft Office Clipboard under the
Home Tab.

In the Microsoft Office 365 the Microsoft Office

Clipboard still appears even when disabled,
however, if you copy some text you will see that it
stays empty.

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 18

Local User’s PC
SAP IBP, Add-In for Microsoft Excel Version installed

From SAP side, we are working on performance improvements in all kind of areas with every
release. The SAP IBP, add-in for Microsoft Excel (Excel add-in) is no exception and we are
continuously working not only on new features but also to improve the performance.

It is therefore a best practice to bring users to the latest Excel add-in version so that all recent
performance improvements can be leveraged.

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 19

Performance Considerations:
Network & Middleware

PUBLIC
Network & Middleware

The SAP IBP data that is shown on the planning views is retrieved from an SAP IBP server which is
located in an SAP Data Center. Same way around: Data that is saved from SAP IBP planning views
is transferred and saved on an SAP IBP server. Depending on the sizes of the views, massive
amounts of data need to be loaded and transferred to the local user’s PC.

Hence, the network and the middleware that stands between the Local User’s PC and the SAP IBP
server can have an impact on how long a user needs to wait for planning views to load or data to
save.

There are certain influencing factors to this:

1) General Network Speed / Internet Speed

2) Distance to SAP IBP server in SAP Data Center

3) Finetune middleware
© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 21
Network & Middleware
General Network Speed / Internet Speed

As per SAP Note 2135948, the recommendation is to have at least a stabile 3Mbps internet
connection. General Rule: The faster, the better.

The SAP IBP, add-in for Microsoft Excel checks the connectivity to the server with any request to the
SAP IBP server.
A pop-up is shown in case the response time is above 300ms.

Usually, this can be a good indication of bad network connectivity if shown frequently. But the sole
fact that your connection speed is not optimal is no reason to be concerned. If the performance you
experience when working with SAP IBP is adequate, the pop-up may safely be ignored.

You can switch the Connection Speed Pop up on and off and also adjust the threshold in the User
settings and Default User settings.

Please also check SAP Note 2388627 for more details

([Link]
© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 22
Network & Middleware
Distance to SAP IBP server in SAP Data Center

The SAP Data Centers where SAP IBP systems are run are located in US, Germany, Russia, China,
and Australia. The customer decides where his systems are run from.

Globally operating customers usually have users accessing the SAP IBP via the SAP IBP, add-in for
Microsoft Excel from all parts of the world. The distance to the server as well as some country-
specific hindrances of the data flow can cause performance issues for these users.

Please make sure to test the performance of the SAP IBP planning views early on from all locations
where it will be used. It is normal that the performance is different in the different countries (e.g. also
influenced by the different internet connection speeds).

Please perform a more detailed analysis in case the users in a certain country experience an
unexpectedly bad performance.

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 23

Network & Middleware
Middleware

Usually, middleware such as firewalls stand in between the Local User’s PC and the SAP IBP Server
in the SAP Data Center. This middleware can also cause performance issues.

Example: A wrong set-up for example of the firewall can cause disconnect problems for end users
using the SAP IBP, add-in for Microsoft Excel.

It is recommended to upgrade the middleware frequently and finetune the settings to allow for an
unimpeded data flow between the customer network and SAP IBP.

Also, proxy time-outs can be caused by the customer’s load balancer / proxy set up. This set-up
should be checked in case time-outs are experienced frequently.

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 24

Performance Tests & Measurement

PUBLIC
Performance Tests

Performance tests are a good tool to prevent frustration for end users after go-live.

So please thoroughly test the performance of the SAP IBP planning views before you go-live. Don't
let your users do that for you in their daily work after go-live.

Also, leave enough time between the performance testing and the go-live so that you can take
actions from the tests.

In the best case, the performance should be tested and optimized before any training or end-user
acceptance tests happen. Users that are already frustrated in the test and training phase won‘t have
a positive attitude towards the software after go-live.

Please ask the users about their performance expectations beforehand to have a threshold against
which you can optimize the system.

Disclaimer: On the following pages the duration times are samples and for illustration purposes only.
© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 26
Performance Tests

1) Set realistic goals for the performance measurement. The individual perception of a „good“
performance usually differs from person to person.
→ What is generally considered acceptable, what is considered optimal? Do that before the testing.
2) Test each planning view template from different PCs and from different locations.
3) Let users perform the exact same operation (e.g. opening a planning view) in parallel to simulate
that you will in reality have more than one user working in the system at the same point in time.
4) Test each step of an end-to-end process in a realistic set-up
• How long does it take to open a template?
• How long does it take to save a realistic amount of data? E.g. depending on the use case, this can be 5
or 500+ data cells that a user wants to change at once.
• Don‘t forget to test the interactive simulation in case this is used in your project
5) Run the tests multiple times to get to an average value.

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 27

How to do Performance Testing for the SAP IBP, Add-In for Microsoft Excel

1) Switch on the performance tracing for the Excel add-in, see SAP Note 2477564 for a detailed
description how to do so.

2) Log on to the SAP IBP system and test different scenarios in the Excel add-in. Please note down
the time at which you started an operation, e.g. opened a favorite and so on. That makes it
easier later on to read the performance trace.

3) Log off and access the performance trace (by entering %temp%\log in Windows Explorer)

4) Disable the performance tracing again

5) Analyze the performance trace

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 28

 Analyze the Performance Trace
Basic Structure

Open the „PerfTrace_XXXXX.glf“ file in Notepad ++

Basic structure:

In the performance trace, you can find a long list of operations, methods and measures that were
taken during your last session when you were logged on.
In case of too many rows, it could be that the trace is split into multiple files.

Start time of the

operation. Makes start# marks the start
it easy to identify At some point, you will find
the point in time the same statement with an
when you „end“ incl. the duration how
performed an long the operation took, e.g.
action. 0.140 seconds.

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 29

Analyze the Performance Trace
General information

Dependent on the version of the Excel add-in that is in use the names of the methods you find in
the performance trace might slightly differ from the ones shown in the next slides.

If during your analysis you find any methods that are taking a long time and you do not know how
to improve the performance, please open an incident on component SCM-IBP-XLS-UI.

In the following slide we show an exemplary process flow which can be used to understand the
methods and what they are doing.

With version 2211.2.0 of the Excel add-in we changed some method names in preparation of new
features to come. You can find a list of changed names in slide xxx.

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 30

SAP IBP, Add-In for Microsoft Excel - Steps and Performance Considerations
Example Process Flow User opens
Template / Favorite

We will use this process flow to guide you through an Excel UI: Download and save 6 1
workbook file from
exemplary performance trace file step by step. SAP IBP backend server
SAP IBP Backend Query
Please note that the orange box on the right side with Excel UI: Open workbook from (collects the data from the
storage location / disk 5 database)
the SAP IBP backend query is not measured by the
performance trace that can be enabled for the Excel Excel UI: Trigger Refresh
Network, internet speed,
add-in. This is a black box from UI perspective and client network layer (.NET),
Excel UI: Wait for Query
requires additional traces in the SAP IBP backend and (in case not finished by then) security, etc
on database level to track the steps and analyze the
Excel UI: Write and render
details. the data (+ local members) 3
In case you see an indication that this takes Excel UI: Apply SAP IBP
2
Formatting
unexpectedly long (e.g. you see a very long „Excel UI
Wait for Query“ time), then a deeper performance Excel UI: Apply VBA Code 4
tracing on the backend side can make sense. It needs Excel UI: Alert KFs &
to be activated during a screensharing session with a Formatting for Fixing 7
user. The activation and the analysis can only be done Finished. User sees Planning
by SAP. Please open a ticket on component SCM-IBP- View
XLS-SRV.
© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC
… Areas with biggest performance impacts, sorted by priority 31
Analyze the Performance Trace
Open a planning view – User Clicks OK
User opens
Template / Favorite

Method [S&OP: Settings: overall run time after OK] start# Excel UI: Download and save
workbook file from
… SAP IBP backend server

Method [S&OP: Settings: overall run time Excel UI: Open workbook from
storage location / disk
after OK] end, duration = 38.559 s#
Excel UI: Trigger Refresh

Excel UI: Wait for Query

This is the time from the point where the user clicked on OK to open (in case not finished by then)
the planning view to the point where the system actually returned with Excel UI: Write and render
the data (+ local members)
the planning view. This is the time where the user waits for the system
and usually the part that should be analyzed further to optimize the Excel UI: Apply SAP IBP
Formatting
waiting time for the user.
Excel UI: Apply VBA Code
In case you also kept track on the time it took the system Excel UI: Alert KFs &
to return the planning view, you should have a noted down Formatting for Fixing
a similarly sized number. Finished. User sees Planning
View
© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 32
 Analyze the Performance Trace
Open a planning view – E2E Time
Opening a planning view is usually accompanied with the statement

Method [S&OP: RibbonOnAction: overall run time for control ID …] start#

…
Method [S&OP: RibbonOnAction: overall run time for control ID sopNewPlanningView] end, duration
= 75.829 s#

These 75.829 seconds are the overall time it took from clicking on “New View→ From Template” to
open the planning view. In between this statement, some sub-results are logged.

“control ID …“ could be e.g.

• „control ID sopNewPlanningView” → Usually used when a template was opened via “New View → From Template”
• “control ID sopTemplate_1” → Usually used when a template was opened via the Template Admin area in the ribbon
• control ID sopFavorite_0 → Usually used when a favorite was opened via the Favorites drop down in the ribbon
• …

Please check which ones apply for the time you started the operation.
© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 33
Analyze the Performance Trace
Open a planning view – Waiting for user interaction

Method [From S&OP: ShowDialog: Overall method run time (waiting for user interaction)] start#
…
Method [From S&OP: ShowDialog: Overall method run time (waiting for user interaction)] end,
duration = 35.946 s#

This is wait time until the user e.g. edited the planning view settings, went for a coffee, and finally
clicked on OK to start the planning view creation.

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 34

Analyze the Performance Trace
Open a planning view – User Clicks OK
User opens
Template / Favorite

Method [S&OP: Settings: overall run time after OK] start# Excel UI: Download and save
workbook file from
… SAP IBP backend server

Method [S&OP: Settings: overall run time Excel UI: Open workbook from
storage location / disk
after OK] end, duration = 38.559 s#
Excel UI: Trigger Refresh

Excel UI: Wait for Query

This is the time from the point where the user clicked on OK to open (in case not finished by then)
the planning view to the point where the system actually returned with Excel UI: Write and render
the data (+ local members)
the planning view. This is the time where the user waits for the system
and usually the part that should be analyzed further to optimize the Excel UI: Apply SAP IBP
Formatting
waiting time for the user.
Excel UI: Apply VBA Code
In case you also kept track on the time it took the system Excel UI: Alert KFs &
to return the planning view, you should have a noted down Formatting for Fixing
a similarly sized number. Finished. User sees Planning
View
© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 35
 Excel UI: Download and save
Analyze the Performance Trace workbook file from

Open a planning view – Load Workbook from server SAP IBP backend server

Method [S&OP: OpenWorkbook: load workbook from server] start#

…
Method [S&OP: OpenWorkbook: load workbook from server] end, duration = 2.614 s#

As mentioned in the beginning of the slide deck, the workbook is loaded from the SAP IBP backend
server to the local PC from where it is then opened.

In case you suspect this time to be too large, please check first the file size. You can find the path in
the settings. Also check if you can reduce the file size by using more specific template settings with
the „Don‘t Copy“ option (e.g. 1 LocationID, ProductID, CustomerID). That reduces the file size on the
server but the end users will not get these filters later on in their views.

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 36

Analyze the Performance Trace
Open a planning view – Open Workbook from Disk

Method [S&OP: OpenWorkbook: Open Workbook from Disk] start#

… Excel UI: Open workbook from
storage location / disk
Method [S&OP: OpenWorkbook: Open Workbook from Disk] end,
Excel UI: Trigger Refresh
duration = 35.752 s#
Excel UI: Wait for Query
(in case not finished by then)

This is the time it took to open the workbook from the Excel UI: Write and render
the data (+ local members)
disk / storage location up to the time the user can work with
Excel UI: Apply SAP IBP
the workbook. Formatting

Excel UI: Apply VBA Code

Excel UI: Alert KFs &

Formatting for Fixing

Finished. User sees Planning

View

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 37

 Excel UI: Open workbook from
Analyze the Performance Trace storage location / disk

Open a planning view – Net time to open the workbook (1)

There is a minimum time that Microsoft Excel needs to open the workbook from the disk. The time
depends on the local PC but also the file itself. However, you will not be able to reduce this time with
any SAP IBP related optimization.

Some areas that can impact the performance in this area:

- Opening a workbook from a shared drive can have a significantly negative
performance impact here. It is better to store it on the users hard disk.
Check the SAP IBP setting!
- Every PC is different. Different Windows & Office Versions, different RAM, CPU, etc. That has an impact on the time it
needs to open the workbook.
- Opening a .xlsm file (Workbook with VBA macros) vs a .xlsx file (workbook without VBA macros) can be significantly
slower due to additional checks from Microsoft Excel. Try with removing the macros and save as .xlsx file.
- Other programs that are open and that battle for system resources. How fast is the opening time when Outlook, Skype,
etc. are closed? We have seen performance increases of double-digit percentages in some cases.
- Also the virus scanner can have an impact when scanning files that are opened on the fly.

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 38

 Excel UI: Open workbook from
Analyze the Performance Trace storage location / disk

Open a planning view – Net time to open the workbook (2)

The time itself is not tracked in the performance trace, but you can easily measure it yourself:
- Log on to SAP IBP and open each sheet in the planning view so that all data is loaded
- Save the workbook locally to your PC
- Log Off from SAP IBP
- Disable the SAP IBP, add-in for Microsoft Excel (optional or as an additional test)
- Close all Excel workbooks, except for an empty one
- Open the saved workbook. Measure the time how long it takes to open the workbook via e.g. a mobile device. This is the
net time your PC needs to open the workbook (without any involvement of SAP IBP).
As a next step, you can also try to remove any pictures, VBA code, … from this workbook and track the time again. For
example: Workbooks with VBA macros are stored as .xlsm files, while normal workbooks would be stored as a .xlsx file.
Opening .xlsm files can take longer due to e.g. additional security checks of the virus scanner and so on…
You can also prepare a plain planning view without any formatting and try again. Use the
SAP IBP feature „Copy Current Workbook w/o formatting“ to create such a workbook.

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 39

 Excel UI: Wait for Query
Analyze the Performance Trace (in case not finished by then)

Open a planning view – Waiting for Backend Query (optional)

Method [S&OP: Waiting for asynchQueryRunning] start#

…
Method [S&OP: Waiting for asynchQueryRunning] end, duration = 11.677 s#

In the beginning of the slide deck, it was explained that there are two parallelized threads. The one
that opens the workbook, and the backend query execution.
In case you find the statement like this, it means that the UI is waiting for the backend query to return
the data. In this case, the UI was waiting another 11 seconds on top of opening the workbook, so any
tuning on the workbooks will have zero effect on the E2E waiting time for the user. The UI will still
wait as long as this query takes.
For us this is an indication to check the query in the backend and see if the performance can be
optimized with e.g. configuration changes, mandatory filter settings (reduce data volume and more
specific requests), etc. Please reach out to the SAP support team to get detailed traces and an
analysis of the backend performance.
© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 40
 Excel UI: Write and render
Analyze the Performance Trace the data (+ local members)

Open a planning view – Local Members

Measure [Insert Local Members] start#

…
Measure [Insert Local Members] end, duration = 0.028 s#

Inserting the rows and formulas for the local members on the planning view. The time taken here
highly depends on the amount of local members, the size of the planning combinations on the
planning view, and also the complexity of the local member calculation.

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 41

 Excel UI: Write and render
Analyze the Performance Trace the data (+ local members)

Open a planning view – Write and Render Data

Method [Basic Write] start#

…
Method [Basic Write] end, duration = 3.129 s#

Writing and rendering the data into the Excel Workbook sheet. The more data, the longer it can take.

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 42

 Excel UI: Apply SAP IBP
Analyze the Performance Trace Formatting

Open a planning view –Hierarchy Level Formatting

Measure [FormattingByLevel] start#

…
Measure [FormattingByLevel] end, duration = 0.463 s#

This is where the SAP IBP Formatting rules are applied to the data.
Formatting by Level refers to the “Hierarchy Level Formatting”

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 43

 Excel UI: Apply SAP IBP
Analyze the Performance Trace Formatting

Open a planning view – EPM Formatting Sheet Migration

Valid for SAP IBP, add-in for Microsoft Excel versions 2005 and later versions.

Method [Migrate EPM Formatting Sheet] start#

…
Method [Migrate EPM Formatting Sheet] end, duration = 0.963 s#

This is the time spent on copying and migrating the older EPM formatting sheets to SAP IBP
formatting sheets. In cases where this step has a significant impact on the performance, please
update or add the template /favorite again. By updating or adding it, the migration step is obsolete.
However, please note that users using an older version of the SAP IBP, add-in for Microsoft Excel
(2002 and older) will not be able to open such migrated templates and favorites.

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 44

 Excel UI: Apply SAP IBP
Analyze the Performance Trace Formatting

Open a planning view – Dimension Member / Property Formatting

Measure [FormattingByMember] start#

…
Measure [FormattingByMember] end, duration = 3.641 s#

This is where the SAP IBP formatting rules are applied to the data.
Formatting by Member refers to the “Dimension Member / Property Formatting”

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 45

 Excel UI: Apply SAP IBP
Analyze the Performance Trace Formatting

Open a planning view –Dimension Member / Property Formatting Detail

In between the statement “Measure [FormattingByMember] start# & Measure [FormattingByMember]

end, duration = 3.641 s#”, you will find the duration for each and every formatting rule that was
applied.
e.g.
Measure [FormattingOnSelectMemberDetailProcess selection at cell $D$194 in the formatting sheet]
end, duration = 0.001 s#
Measure [FormattingOnSelectMemberDetailProcess selection at cell $D$146 in the formatting sheet]
end, duration = 0.087 s#.
In the formatting sheet, check for Cell D146 to see which formatting rule is applied.

With that, you can easily identify the most expensive formatting rules and simplify them (discussed in
previous chapter – Union flag, Conditional Formatting, etc. )
© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 46
Analyze the Performance Trace Excel UI: Apply VBA Code

Open a planning view – VBA Code / Macros

Apply VBA code from VBA Hook „Before Refresh“

Method [BeforeRefresh VBA Caller] start#
…
Method [BeforeRefresh VBA Caller] end, duration = 0.001 s#

Apply VBA code from VBA Hook „After Refresh“, e.g. the VBA charts in the SAP Planning View
Sample
Method [AfterRefresh VBA Caller] start#
…
Method [AfterRefresh VBA Caller] end, duration = 0.050 s#

→ In case you are using custom VBA code, please check for any performance improvement
potential within the code.
© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 47
 Excel UI: Alert KFs &
Formatting for Fixing
Analyze the Performance Trace
Open a planning view – Alert Key Figure

Method [S&OP: around ColorAlertCells(Alerts)] start#

…
Method [S&OP: around ColorAlertCells(Alerts)] end, duration = 0.468 s#

Time it took to track and color the alert key figures

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 48

 Excel UI: Alert KFs &
Formatting for Fixing
Analyze the Performance Trace
Open a planning view – Fixing of Key Figure Values

Method [S&OP: around FormatCells(Fixing)] start#

…
Method [S&OP: around FormatCells(Fixing)] end, duration = 0.056 s#

Time it took to apply the fixing indicator to fixed key figure values.

Rule of thumb: The more fixed cells, the longer this step takes. This is one reason why we don’t
recommend to use the fixing for mass- data (such as defining frozen horizons for key figures, etc.)

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 49

 Excel UI: Alert KFs &
Formatting for Fixing
Analyze the Performance Trace
Open a planning view – Planning Notes

Method [S&OP: around SetPlanningNotes(PlanningNotes)] start#

…
Method [S&OP: around SetPlanningNotes(PlanningNotes)] end, duration = 1.589 s#

Time it took to apply the planning notes the planning view.

Planning Notes can be costly from a performance standpoint

due to the rendering times. Please use them wisely and
leverage the user settings.
For planning views, please use the setting “Display
Planning Notes: Show from All Level” wisely.

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 50

Analyze the Performance Trace
Further Information and Recommendations

Please note that not all operations and methods are tracked by the performance trace. So there can
be gaps in between two methods.

e.g. method xyz ends 10:15:20 and next method starts 10:16:03. In between, there could have been
other methods running that are not tracked by the trace.

In case certain steps takes unexpectedly long, it is also a recommendation to check if other add-ins
for Microsoft Excel are installed and running. Check the impact by deactivating the other add-ins and
perform the test again.

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 51

Analyze the Performance Trace
Changed method names with version 2211.2.0
Find in the next two slides the changed method names in yellow:
Method [From SOP: OpenWorkbook: Supply Chain Planning: overall method run time]
→ Was changed to Method [Open favorite] or Method [Open template] to differentiate between
opening a template or a favorite.

Method [Receive body (headers already received)] → Method [Download response]

Method [Write Report] → Method [Write planning view]

Method [Basic Write] → Method [Write headers and data: Rows = xx; Columns = xx; Cells = xx]

Method [FormattingReport] → Method [Format planning view]

Method [From SOP: Query: around EPM Refresh]

→ Method [Refresh planning view: Workbook name = xx; Worksheet name = xx; Favorite name
= xx; Template name = xx; Report ID = xx; Key figure in planning view = xx]
© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 52
Analyze the Performance Trace
Changed names with version 2211.2.0

Method [From SOP: ShowDialog: Overall method run time (waiting for user interaction)]
→ Method [Show dialog (user input)]

[From SOP: Refresh: overall method run time] → Method [Refresh data in planning view]

Method [From SOP: Around Simulate] → Method [Simulate data in planning view]

Additionally new methods have been added:

New: Method [Save data in planning view]

New: Method [Log on]

New: Method [Excel opens workbook from disk]

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 53

Follow us

[Link]/contactsap

© 2021 SAP SE or an SAP affiliate company. All rights reserved.

No part of this publication may be reproduced or transmitted in any form or for any purpose without the express permission of
SAP SE or an SAP affiliate company.
The information contained herein may be changed without prior notice. Some software products marketed by SAP SE and its
distributors contain proprietary software components of other software vendors. National product specifications may vary.
These materials are provided by SAP SE or an SAP affiliate company for informational purposes only, without representation or
warranty of any kind, and SAP or its affiliated companies shall not be liable for errors or omissions with respect to the materials.
The only warranties for SAP or SAP affiliate company products and services are those that are set forth in the express warranty
statements accompanying such products and services, if any. Nothing herein should be construed as constituting an additional
warranty.
In particular, SAP SE or its affiliated companies have no obligation to pursue any course of business outlined in this document or
any related presentation, or to develop or release any functionality mentioned therein. This document, or any related presentation,
and SAP SE’s or its affiliated companies’ strategy and possible future developments, products, and/or platforms, directions, and
functionality are all subject to change and may be changed by SAP SE or its affiliated companies at any time for any reason
without notice. The information in this document is not a commitment, promise, or legal obligation to deliver any material, code, or
functionality. All forward-looking statements are subject to various risks and uncertainties that could cause actual results to differ
materially from expectations. Readers are cautioned not to place undue reliance on these forward-looking statements, and they
should not be relied upon in making purchasing decisions.
SAP and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered
trademarks of SAP SE (or an SAP affiliate company) in Germany and other countries. All other product and service names
mentioned are the trademarks of their respective companies.
See [Link]/trademark for additional trademark information and notices.