SAP Integrated Business Planning, Add-In for

Microsoft Excel
Consulting Know-How and Best Practices – Performance Considerations
Product Management, SAP
May 2023
PUBLIC
Disclaimer

The information in this presentation is confidential and proprietary to SAP and may not be disclosed without the permission of SAP.
Except for your obligation to protect confidential information, this presentation is not subject to your license agreement or any other service
or subscription agreement with SAP. SAP has no obligation to pursue any course of business outlined in this presentation or any related
document, or to develop or release any functionality mentioned therein.
This presentation, or any related document and SAP's strategy and possible future developments, products and or platforms directions and
functionality are all subject to change and may be changed by SAP at any time for any reason without notice. The information in this
presentation is not a commitment, promise or legal obligation to deliver any material, code or functionality. This presentation is provided
without a warranty of any kind, either express or implied, including but not limited to, the implied warranties of merchantability, fitness for a
particular purpose, or non-infringement. This presentation is for informational purposes and may not be incorporated into a contract. SAP
assumes no responsibility for errors or omissions in this presentation, except if such damages were caused by SAP’s intentional or gross
negligence.
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, which speak only as of their dates,
and they should not be relied upon in making purchasing decisions.

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

Agenda

- Basic Information about Performance in the SAP IBP, Add-In for Microsoft Excel

- Performance Considerations: SAP IBP Backend Configuration

- Performance Considerations: SAP IBP, Add-In for Microsoft Excel & Microsoft Office

- Performance Considerations: Local User’s PC

- Performance Considerations: Network & Middleware

- Performance Test & Measurement

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

Performance of the SAP IBP, Add-In for Microsoft Excel

The SAP development team is dedicated to constantly improve the performance of their solutions,
including the SAP IBP and the SAP IBP, add-in for Microsoft Excel. You will usually see smaller or larger
performance improvements in every new release.
However, there are also certain aspects that are in the hand of the consultants that can have a
significant impact:
• The time the user waits for the system to respond to his request (e.g. opening a planning view,
saving data, creating a planning note, etc.) consists of both frontend and backend time. For example,
when you open a new planning view, the data has to be read and prepared by the SAP IBP backend,
sent through the network, and afterwards the UI can put formatting on top.
• The performance of the planning views in the SAP IBP, add-in for Microsoft Excel are therefore
impacted by multiple different factors that we will explain in this document. Many of them can be
positively influenced during implementation. So there is a lot you as a consultant can do to
optimize the performance.
The following slides should provide an overview around the most common influencing factors and
performance considerations and we provide best practices and guidance where applicable.

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

Step 1: Project Setup & Blueprinting

Considering the performance starts with the project set-up? Why?

The satisfaction of end users is usually based on 2 key factors:

- Performance (wait times)
- Usability (how easy is it to do the daily work)

Especially for the usability part, it is a best practice to involve the end users early on in the process when
defining the planning views, macros, formatting so that they are designed to their daily needs.

Unfortunately, it is not common to ask the end users about their performance expectations and consider
these in the design. Do they expect to see data on the UI in 10 seconds? Or 5 seconds? What is still
acceptable in which cases? Would they still be willing to adopt the solution?

Such questions and considerations mostly boil up rather late during end-user tests and can lead
to last-minute escalations and, in the worst case, to costly go-live delays.

So please make sure that you perform performance tests with the planning views as early as possible
before go-live. In case you are asked to improve on that end, you need to have time to work on the
planning views, the key figure definitions, etc.
© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 5
Performance Considerations:
General Overview

PUBLIC
 SAP IBP, Add-In for Microsoft Excel - Steps and Performance Considerations
Open a Template/Favorite – Parallel Thread
User opens
The bigger the file, the longer it takes.
Template / Favorite
Use the “Don't Copy“ option within the
“Template Settings“ to reduce the file
size! Excel UI: Download and save 6 1

E2E time the user waits until the planning view is ready to use
workbook file from UI is waiting for the backend in
Use a local file path instead of a SAP IBP backend server case the query is not finished
SAP IBP Backend
network share to store the workbook. yet. Optimize the calculation
Excel UI: Open workbook from (collects the data from the
See User Settings for path. 5 graph / configuration to speed
storage location / disk database)
Check for unused space in the planning up the backend.
view. Excel UI: Trigger Refresh Apply mandatory filters for end
Check image sizes in planning view Network, internet speed, users to reduce the data
(e.g. company logo). Excel UI: Wait for Query client network layer (.NET), volume.
(in case not finished by then) security, etc
Also check if VBA code is needed as
opening a .xlsm workbook is usually
slower than opening a .xlsx workbook. Excel UI: Write and render
the data (+ local members) 3
The bigger the view
Excel UI: Apply SAP IBP
(# rows & columns), the longer it takes.
Formatting 2 Fixing and alerting are meant for
Local members / calculations are also exception cases and not mass-fixing
rendered here, so the complexity of and alerting.
these calculations also play a role. Excel UI: Apply VBA Code 4
If this step takes a lot of time, then it is
Complex formatting, especially Excel UI: Alert KFs & a good practice to revisit the business
Conditional Formatting rules can have Formatting for Fixing 7 process and definition.
an impact here.
Finished. User sees Planning
Same for complex VBA code. View
© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC
… Areas with biggest performance impacts, sorted by priority 7
SAP IBP, Add-In for Microsoft Excel - Steps and Performance Considerations

Refresh Data – Single Thread Logon to SAP IBP via SAP IBP,
(e.g. click refresh or open new workbook sheet) Add-In for Microsoft Excel
User opens different User logs on to SAP IBP backend
workbook sheet and planning area

SAP IBP Backend Query

(collects the data from the Initial Load:
database)
Network, internet speed, # Templates
client network layer (.NET), # Favorites
security, etc Inital Load 2:
Master Data Information Application Service:
Excel UI: Write and render Alert Dashboard Number of attribute values
Planning View Information
the data S&OP Tasks
User Permissions
Excel UI: Apply SAP IBP
Formatting Application Job
Information
Excel UI: Apply VBA Code

Excel UI: Alert KFs &

Formatting for Fixing
Finished. User can start working
Finished. User sees Planning with the SAP IBP system
View

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

Logon to SAP IBP via SAP IBP, Add-In for Microsoft Excel Application
Service:
Application Service: Number of attribute values Number of
attribute values

Check these points if the logon to SAP IBP takes too long.

The values are loaded upfront into the local cache. The time spent on loading these attribute values at logon can be
influenced by an administrator / consultant.

- Well-defined Permission Filters for read access (aka visibility filters) limit the amount of data that needs to be
loaded, e.g. 2000 products instead of 200.000.

- Time Horizons: Check the selectable time horizon from the configuration UI. Check if it makes sense from a
business perspective if a user can select the past 10 years of data in daily buckets, or is maybe 2 years enough?

- Try using the Dynamic Selection to limit the number of attributes that get loaded initially and reduce the logon
times. Drawback: ID-Description linkage is not possible and selecting the filters can be slower as data has to be
dynamically read from the database instead of the local cache. It is a bit of a trade-off between faster logon and
slightly slower performance at certain user operations but can have a significant impact in case the local PC has
a small memory and the cache is consuming a big portion because of mass attributes that are pulled into the
cache at logon.

- Check ID-Description Linkage: This can have a bit of an effect if you have a lot from a certain attribute (e.g.
Product ID), and then all IDs and Descriptions have to be loaded separately. With the ID-Description linkage, the
combination has to be loaded only once.

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

SAP IBP, Add-In for Microsoft Excel
Key Performance Impacts

There are several areas in which the performance can be optimized:

SAP IBP Backend Configuration
• SAP IBP System Sizing & Housekeeping
• SAP IBP Calculation Graph (Expensive key figure definition, Change History enabled key figures)
• Aggregation & Disaggregation rules /UoM and Currency Conversions
• Change History Key Figures
SAP IBP, Add-In for Microsoft Excel & Microsoft Office
• Global Configuration Parameters
• SAP IBP Excel Planning View definition (Data Volume, Options, Template Settings, …)
• SAP IBP Excel Planning View – SAP IBP Formatting
• EPM Formatting Sheet Migration
• Microsoft Excel Local Calculations
Local User’s PC
• Hardware of the PC where the SAP IBP, Add-In for Microsoft Excel is installed (CPUs, Memory, speed of Hard Disk Drive, …)
• Software of the PC where the SAP IBP, Add-In for Microsoft Excel is installed (Microsoft Office Version, Virus scanner)
• SAP IBP, Add-In for Microsoft Excel Version installed
Network & Middleware
• General Network Speed / Internet Speed
• Distance to Server
• Finetune middleware

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

Performance Considerations:
SAP IBP Backend Configuration

PUBLIC
SAP IBP Backend Configuration
SAP IBP Planning Area Configuration & Performance Recommendations

SAP IBP is a highly configurable system and it is possible to configure it in a way that's not optimal
for performance. The SAP Note 2211255 describes some general guidelines to achieve good
performance for a planning area in SAP IBP both in terms of configuration and sizing.

[Link]

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

SAP IBP Backend Configuration
SAP IBP System Size and Housekeeping Rules

Please make sure that your SAP IBP system is reasonably sized. The size is dependent on the
processes that you run, the number of users, the amount of data in the system, etc. Usually, the
sizing is done in the beginning of an implementation project in cooperation with SAP
representatives. However, if your initial scope changes, please reach out to us to discuss a
potential need to up-size the system.

Housekeeping:
While operating the SAP IBP system, you are regularly creating and adding new data which can
lead to a tremendous growth of the records in the system for which it was not initially sized. Please
make sure that housekeeping jobs are run regularly (e.g. monthly) to remove unnecessary data.

Please also check Data Lifecycle Management at:

[Link]
US/[Link]

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

SAP IBP Backend Configuration
SAP IBP Key Figure Calculation Graph (1)

Whenever an interaction is started where the SAP IBP backend gets involved, e.g. data is read for a
planning view, key figure data is changed and saved, simulations are started, etc., the SAP IBP
Calculation Graphs are involved.

What is the Key Figure Calculation Graph?

It is basically the calculations that are involved to retrieve key figure data on a certain level of
aggregation. The more complex the key figures, the more calculations and dependencies are
involved to retrieve the data. And that can cost time, depending on the complexity of the tree
(see below table). The SAP IBP, add-in for Microsoft Excel always enters the calculation tree
at “@Request” level for the specific key figure.

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

SAP IBP Backend Configuration
SAP IBP Key Figure Calculation Graph (2)

Example: Simulation of
Consensus Demand Plan which is
calculated from multiple other key figures

Sales Forecast

50%
Marketing Demand Planner
Forecast Forecast

30% 20%

Consensus Demand Plan

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

SAP IBP Backend Configuration
SAP IBP Key Figure Calculation Graph (3)
Depending on the key figure definition in the SAP
IBP backend, the calculation graph for a key figure
can get rather complex and a lot of operations need
to happen on database level in order to read, save,
and simulate the dependent key figures.

So the size of the calculation graph plays a role

when it comes to the performance of SAP IBP
Planning Views when requesting, saving or
simulating data.

Simple Calculation Graph More Complex Calculation Graph

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 16
SAP IBP Backend Configuration
SAP IBP Calculation Graph (4) – Recommendations

During the configuration of the key figures, you have an influence on the performance for the end users.

In case you encounter planning views in your environment that have, compared to others, bad performance, e.g. while loading
or saving data, please check which key figures are involved. Then try to find out which ones may cause the performance loss
by looking at the backend calculations and by eliminating one by one with testing.

We had seen customer cases where backend calculation for 2 out of 10 key figures in a planning view were responsible for
60% of the overall time until the planning view was loaded. After the key figures were located and reworked under
performance considerations, the performance impact could be minimized.

Please follow the instructions on the Model Configuration Guide for SAP IBP and check whether using Simplified Key Figure
Calculations might help to reduce the complexity of calculated key figures.

Apart from these easier to detect recommendations, there are other options which could be discussed with a configuration
expert.

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

SAP IBP Backend Configuration
Aggregation and Disaggregation

On the fly aggregation and disaggregation of the planning data are one of the key strengths of SAP
IBP but they also have a performance impact. The SAP Development team is constantly working on
the performance of the aggregation and disaggregation.

As a consultant, you can make sure that the key figures are defined on the planning level where they
are needed in order to prevent unnecessary data and aggregation / disaggregation operations. If
data from a key figure is not needed on a daily level, then there is no need to store data in daily
buckets. Disaggregation Modes
Equal Prop/Equal Copy Prop/Copy

Also, correct aggregation and Sum Would usually not make

sense in this
combination
Would usually not make
sense in this
combination

disaggregation rules should be Avg Would usually not make Would usually not make

Aggregation Modes
sense in this sense in this
assigned to the key figures. combination combination

Min Would usually not make Would usually not make

sense in this sense in this

Especially the aggregation mode combination combination

„Custom“ can have a significant Max Would usually not make

sense in this
combination
Would usually not make
sense in this
combination

negative performance impact (see next slide). Custom

© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 18
SAP IBP Backend Configuration
Aggregation and Disaggregation - CUSTOM Mode

Important change with SAP IBP 1802

Before 1802, many key figures in the sample planning areas used aggregation mode CUSTOM due
to conversions. However, as of 1802, it is no longer necessary to use aggregation mode CUSTOM
just because of using a conversion in a key figure for unit of measure or currency conversions.

Not using aggregation mode CUSTOM can result in a significant performance improvement in
disaggregation.

Therefore, we recommend that you only use Custom aggregation mode in the following special
circumstances:
▪ When a key figure has a complex calculation at request level, for example, Unit Price, which has inputs at
request level.
▪ When the planning level used in the request level calculation is different from both the base planning level of
the key figure and from the planning level that is used in unit of measure or currency conversions.

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

SAP IBP Backend Configuration
UoM and Currency Conversions

Unit of Measures and Currency Conversions are used in most customer implementations.
However, they are not always mandatory for each and every key figure. What is optional or
mandatory is defined by the customers and their processes. However, it makes sense to remove
unnecessary conversion operations as this can have a positive effect on the performance for the key
figure.
So please check carefully if a UoM or Currency Conversion is required for a key figure.

Aggregation and Disaggregation – Planning on Base UoM

It can make sense to run your planning operators (e.g. statistical forecasting) based on the base unit
of measure of e.g. the product. During disaggregation, we have seen, in some cases, a 50% better
performance compared to the same example where another conversion factor is used in the
planning. The 50% improvement might no always be reached, but usually a performance increase is
measurable.
© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 20
SAP IBP Backend Configuration
Change History Enabled Key Figures

Only switch on change history for planning areas and individual key figures where it’s really required. The
general suggestion is to have this enabled for key figures where users change / enter data. It is not
recommended for key figures with calculations or where an operator is mass-changing the entries.

In addition, Change History Enabled key figures should to be set in a smart way. Example: When you have a
key figure that is calculated from a change history enabled key figure, the calculated key figure is also
automatically change history enabled and can result in massive amounts of data being saved. That can impact
the overall performance of SAP IBP and with that, also of the SAP IBP, add-in for Microsoft Excel.

Please check the definition of the key Key Figure Stored Calculated Change Change history
History visible in
figures and use the purge change history Enabled Excel?
data operator regularly (e.g. weekly / monthly)! KF1 X X Yes Yes

KF2 X No No

KF3 X No Yes
(KF3 = KF1 * KF3)

Key figure changes are tracked because the calculation

of the key figure contains a „Change
© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC
History Enabled“ key figure. 21
SAP IBP Backend Configuration
Application Jobs

Application Jobs, such as planning operators, are calculating, accessing, saving, deleting data when
they are run in the backend and are with that using system resources (e.g. memory and CPU) that
are then not free to be used by the end users.

Users can still work with the system and data while these jobs are running and should normally not
see an effect on the performance.
However, the application jobs can have an impact on the performance of the end users e.g. when
some „heavy“ jobs (system resource intensive jobs) are using large parts of the system.

In case you encounter varying performance such as unexpectedly bad performance during a certain
time of the week or day, please check if there are any application jobs running during that time and
check if those can be scheduled at „quieter“ times, e.g. night or weekend.

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

SAP IBP Backend Configuration
Interactive Simulation

Some operators, which are working on a whole network, of data instead of a smaller subset, for
example the Multi-stage Inventory Optimization or the Supply Optimizer, can run for a long time and
lead to time-out issues (usually after 10 minutes – depends on customer settings) during an
interactive simulation.

There are several options to prevent the time-out but also enable the user to work in parallel to the
simulation running.
→ Prevent Time- Out: Use the „planning scope“ for the IO and Supply operators which breaks
down the network / data set to a reasonable size.
→ Prevent Time-Out and enable user to work in parallel without the planning scope:
1. Create a new scenario
2. Run the IO or Supply Jobs as a backend job for the scenario only (both are enabled to run for specific scenarios)
3. Check results of the operator run and promote to the baseline

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

SAP IBP Backend Configuration
Interactive Simulation & Save

Adding attributes to a planning view that are key in their master data type and root in the base
planning level of the contained key figures can speed up simulation and save, as it allows the system
to process changes more efficiently.

If the attribute Product ID is for example key/ root in your data model and the product name is not-
key/ not-root attribute it can speed up simulation and save to add the Product ID to your planning
view.

For more information, please also refer to SAP Note 2153455:

[Link]

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

Performance Considerations:
SAP IBP, Add-In for Microsoft Excel &
Microsoft Office

PUBLIC
SAP IBP, Add-In for Microsoft Excel
Global Configuration Parameters

Global Configuration Parameters of Group PLAN_VIEW have a direct impact on the

behavior of the SAP IBP, add-in for Microsoft Excel and partly also on the performance. E.g. in this example a
planning view can only have a maximum of 1,000,000 cells. The administrator can set these parameters.
Details in the Model Configuration Guide

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

SAP IBP, Add-In for Microsoft Excel
Most Important Global Configuration Parameters (1)
Parameter Parameter Default Best Practice Parameter Description
Group Name Value
PLAN_VIEW MAX_RESULT 1,000,000 1,000,000 This global configuration parameter defines the maximum number of cells displayed in a
_CELL_SIZE planning view in the Excel add-in. Using this parameter, system performance issues due to
excessively large planning views can be prevented

PLAN_VIEW FORCE_PLAN NONE MANDATORY Controls whether users are forced to set up a filter when creating or editing a planning view.
NING_VIEW_ (or at least The parameter has the following values:
FILTER WARNING) • WARNING: The user receives a warning when they try to open a planning view without a
filter.
• MANDATORY: The user can't proceed without defining a filter for the planning view they
are trying to open.
PLAN_VIEW MAX_DIM_ME 100,000 100,000 You can use this global configuration parameter to activate the dynamic selection of master
MBERS data attribute values. If the number of values for an attribute is larger than the value you set for
this parameter, the SAP IBP, add-in for Microsoft Excel does not display all of the values in
filtering and planning view definition. Users are notified of this fact and can display all of the
values by explicitly searching for them.
PLAN_VIEW PV_COUNT_ 5 5 The maximum number of open Microsoft Excel workbooks that contain planning views.
MAX

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

SAP IBP, Add-In for Microsoft Excel
Most Important Global Configuration Parameters (2)
Parameter Parameter Default Best Practice Parameter Description
Group Name Value
PLAN_VIEW MAX_SUB_T 0 3-4 • You can use this global configuration parameter to enable attribute-based totals in the SAP
OTALS (but depends IBP, add-in for Microsoft Excel. The number you enter as the value of this parameter
on the view specifies the number of attribute-based totals that are allowed per worksheet
complexity)
PLAN_VIEW MAX_TIME_L 1 3-4 • The number of time levels that can be used in a planning view. You can use this parameter to
EVELS (but depends enable the use of flexible time axis, by setting the parameter value to a value greater than 1.
on the view • The number of time levels also includes the time totals. E.g. Period + Total (Monthly) and
complexity) Period (Quarter) = 3 time levels

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

SAP IBP, Add-In for Microsoft Excel - Global Configuration Parameters
Best Practice: Force Users to set Filters for Planning Views

PLAN_VIEW → FORCE_PLANNING_VIEW_FILTER (Default: None)

Controls whether users are forced to set up a filter when creating or editing a planning view. The parameter
has the following values:

WARNING: The user receives a warning when they try to open a planning view without a filter. However, a
good visibility filter can have a similar effect so that users are anyway just able to call the information that they
are allowed to see / want to see / need to see.
MANDATORY: The user can't proceed without defining a filter for the planning view they are trying to open.

A limitation at least to “warning” is

strongly recommended due to
performance considerations!

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

Planning View Filter vs Visibility Filter

Although a well defined planning view filter is the base for a fast and easy to consume planning
view, the administrator can already limit the amount of data a user is allowed to see in a previous
step, by defining visibility filters (Permission Filters).

These can be defined in a way that specific users can only see and request the data that they are
allowed to see. That from the start limits the amount of data a user can see on the planning view.

Hence, a good visibility filter for users is a key aspect of good performance and usability!

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

SAP IBP, Add-In for Microsoft Excel - Global Configuration Parameters
Best Practice: Limit the Size of the Planning Views

PLAN_VIEW → MAX_RESULT_CELL_SIZE (Default: 1,000,000)

This global configuration parameter defines the maximum number of cells displayed in a planning view in the Excel add-in. Using this
parameter, system performance issues due to excessively large planning views can be prevented.

The MAX_RESULT_CELL_SIZE parameter is used to limit the amount of data read from the database, if end users generate planning views
without applying enough planning filters. In such situations, the system cuts off the part of the data that exceeds the limit defined by this
parameter, to avoid a too high increase of the runtime and the memory used by the related queries. Users are notified of the phenomenon
through a system warning and advised to review their planning view definition with special attention to the filter criteria used to mark out the
scope of the executed query.

Please note: To provide an output/ planning view that is meaningful from a business perspective, the result set coming from the database
might be reduced or increased under certain conditions. For more information, see the Model Configuration Guide.

The default limitation is strongly recommended due to performance considerations!

Please remember: If end users constantly receive the warning that they have reached the limit, this is an indication for a not well defined
planning view. End users tend to ignore warnings after they start to constantly appear.

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

Limit the Size of the Planning Views
Downsides of this Approach

Following behavior of the system must be understood when using the MAX_RESULT_CELL_SIZE or the
MAX_RESULT_ROW_SIZE.
Example: The user wants to see data for 500 product Ids (e.g. P1-P500). He adds multiple key figures
and other attributes, and hence the planning view gets rather large. The threshold is reached from the
global configuration parameter MAX_RESULT_CELL_SIZE.

P1

P431

--------- Cell limit reached → End of planning view

… The last 68 products are not shown on the planning view. It is capped.
When doing calculations on top of the whole dataset, the results could be wrong because
P500
data is missing.
© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 32
SAP IBP, Add-In for Microsoft Excel - Global Configuration Parameters
Best Practice: Dynamic selection of master data attribute values

PLAN_VIEW → MAX_DIM_MEMBERS (Default: 100,000)

You can use this global configuration parameter to activate the
dynamic selection of master data attribute values. If the number of
values for an attribute is larger than the value you set for this
parameter, the Excel add-in does not display all of the values
in filtering and planning view definition. Users are notified of this
fact and can display all of the values by explicitly searching for them.
Dynamic dimensions can be used in the Excel add-in to improve the performance for
companies with master data scenarios using huge amount of values for some of the master
data attributes, e.g. product ID. Standard behavior is that all attributes values are read from
the database during Log on to the SAP IBP system (refer to page 8). With the dynamic
dimension logic, thresholds can be set. If the number of values for an attribute is below the
threshold, the standard logic is used. If it is above the threshold, all data for this attribute is
not read during log in but queried on the fly when needed.
Improves the performance during logon and also when using value helps. Noticeable
performance gain for customers with huge data set. The default limitation is recommended
due to performance considerations
© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 33
SAP IBP, Add-In for Microsoft Excel - Global Configuration Parameters
Best Practice: Dynamic selection of master data attribute values - Example

The dynamic dimension logic becomes visible e.g. when the user wants to set a filter on a
specific attribute when defining the planning view.

Standard Logic: The user sees all attributes in the list.

Dynamic Dimension Logic: The user does not get the whole list of attribute values but
searches for the attribute value. The data is then queried on the fly from the database.

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

SAP IBP, Add-In for Microsoft Excel - Global Configuration Parameters
Best Practice: Limit the Number of Open Workbooks per User

PLAN_VIEW → PV_COUNT_MAX (Default: 5)

The maximum number of open Microsoft Excel workbooks that contain planning views.

The default limitation is recommended to performance considerations. Reason:

- Reduce the PC‘s memory utilization which grows with the number of open workbooks and
transactions. The user should still be able to work in parallel with other applications.
- Reduce the SAP IBP backend load

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

SAP IBP, Add-In for Microsoft Excel - Global Configuration Parameters
Best Practice: Enable Attribute Based Totals

PLAN_VIEW → MAX_SUB_TOTALS (default: 0)

You can use this global configuration parameter to enable attribute-based totals in the SAP IBP,
add-in for Microsoft Excel. The number you enter as the value of this parameter specifies the
number of attribute-based totals that are allowed per worksheet.

A certain limitation is recommended due to performance considerations

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

SAP IBP, Add-In for Microsoft Excel - Global Configuration Parameters
Best Practice: Enable Flexible Time Axis

PLAN_VIEW → MAX_TIME_LEVELS (default: 3)

The number of time levels that can be used in a planning view. You can use this parameter to
enable the use of flexible time axis, by setting the parameter value to a value greater than 1.

A certain limitation is recommended due to performance considerations

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

SAP IBP, Add-In for Microsoft Excel
Storage Location of the Workbook

When a user opens a favorite or a template, the

workbook is loaded from the SAP IBP system and
then saved at the location that was previously
specified. From there, it is opened, then the data is
written to Microsoft Excel cells, the formatting applied,
etc.

If the file path used refers to a folder on a network

drive or a shared drive, it can have a negative impact
on performance when opening a workbook, compared
to using a file path that refers to a local folder.
So please ensure that the file path specified in the
User Settings or Default User Settings is set to a
local folder.

See SAP Help portal at Setting Up File Paths for

Workbooks for further details.
© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 38
SAP IBP, Add-In for Microsoft Excel
Load Times of a Workbook

Opening a workbook with Microsoft Excel usually takes few seconds. It does not matter if it is a
workbook containing SAP IBP planning views or just your usual Excel file. The time depends on the
size of the workbook, the PCs speed, memory and CPU, and if other programs such as Outlook,
Skype, Virus Scanner, etc. are running in parallel and competing for system resources.

You can easily track the minimum time that Microsoft Excel needs to open the respective SAP IBP
workbook by downloading it from the file path (screenshot), save it to your local PC and open it
from there. The seconds it takes for this offline version of the SAP IBP workbook to open up is the
minimum time your PC needs (on top comes the SAP IBP backend query, network time, etc. which
are described on page 7).
You can also try to shut down other
programs and then open it again.

The Excel add-in cannot be faster than

the PC and Microsoft Excel itself!
© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 39
SAP IBP, Add-In for Microsoft Excel - Planning View Definition
Planning View Templates

Template development is an important task during an SAP IBP implementation.

Performant, easy-to use, and good-looking templates will contribute significantly to end user
adoption and implementation project success.
Do not underestimate this task - it will take time and effort. Schedule enough time and include the
right persons. Look for these skills: Microsoft Excel, Excel Formulas, VBA, and - last but not least -
graphic design / user experience.

Please also involve the end users early on in the process so that the templates are designed to meet
their daily needs.

→ Best Practice: To be able to use the functions in the Template Admin group, the value PV_TMPLT
(Planning View Template) needs to be added to the restriction field Administration Functions in
the restrictions of your business role. Do not give template admin permission to everyone! Too
much work goes into a template to have it broken with a careless click on Update.
Note: Users have access to templates assigned to their roles in the New View > From Template…
dialog - nobody needs template admin permission to use a template.
© 2023 SAP SE or an SAP affiliate company. All rights reserved. ǀ PUBLIC 40
SAP IBP, Add-In for Microsoft Excel - Planning View Definition
SAP Sample Planning View Templates

1) SAP provides samples for planning view templates that contain some standard formatting,
functions and settings that can be generally useful. Do not rely too heavily on SAP sample
templates. They are meant as inspiration and and as a starting point. The code in the VBA based
templates is delivered as sample coding.

2) The templates can be downloaded via SAP Note 1790530

[Link]

3) We are constantly, but irregularly (not with every release) improving the templates. Please check
in regularly to get the updates.

Please find some best practices around templates, VBA code, formatting, and planning view
definition on the upcoming slides. More information can also be found in SAP Note 1790530.

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

SAP IBP, Add-In for Microsoft Excel - Planning View Definition
Replace the old formula-based SAP sample Templates

In case you are using the older, formula-based SAP templates (see sample below), please consider
a short-term switch to the newer VBA-based templates. They show a much better stability and
performance.
Old formula-based SAP sample template:

New VBA-based SAP sample template:

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

SAP IBP, Add-In for Microsoft Excel
Remove empty Rows and Columns from the Templates

In case you encounter templates / planning views with a huge amount of empty rows and/or
columns below and on the right to the data, please clean these up. That can have a performance
impact when opening the workbook .

Example:
You can just select the empty rows and columns and
delete them.
Afterwards, please update the template again.

Instead you can use the function called “Clean

Excess Cell Formatting” of the Microsoft Excel
COM add-in Inquire to do that with one click.

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