Tips
and Tricks for
Implementing
and
Optimizing
Formulas in
SAP
NetWeaver BI
Integrated
Planning
Marc
F. Bernard
Chief
SAP NetWeaver Specialist
SAP
NetWeaver RIG
2007-05-30
Prerequisites
Required
Knowledge:
General
understanding of BI Integrated Planning in
SAP
NetWeaver 7.0
Recommended
Knowledge:
Some
experience with implementing formulas in BI Integrated
Planning
or BW-BPS
SAP
AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard / 2
Learning
Objectives
Learn
about planning functions in BI Integrated Planning and how
to
decide which type of function you should use
Design
formulas the right way
Understand
how formulas are configured
Resolve
performance problems or avoid them in the first place
Hear
tips & tricks for implementing formulas
SAP
AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard / 3
Planning
Function Types
Designing
Planning Functions
Configuring
Formulas
Performance
Tuning of Formulas
SAP
NetWeaver BI Architecture for Business Planning
Enterprise
Portal
Enterprise
Reporting, Query and Analysis
BEx
Web
Pattern
Web
Analyzer
Web
Application Designer
Report
Designer
BEx
Analyzer
MS
Excel
Add-in
BEx
Query Designer
Business
Planning
Analytical
Engine
OLAP
services
• Drill
Down
• Currencies/Units
• Calculations/Formulas
• Exceptions/Conditions
• Variables
• Hierarchies
• Aggregation
• Sort
•…
&
Analytical Services
Planning
Modeler
Planning
specific
services
• Enqueue
• Validations
• Data
slices
• Characteristic
relations
Planning
Functions &
Sequences
• Distribute
• Formulas
(FOX)
• Copy
• Revaluate
• Repost
• Forecast
• Custom
defined
•…
Focus
for this webinar
Caching
services
Plan
data cache
Enterprise
Data Warehousing
Operational
Data
Store
SAP
AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard / 5
Architected
Data Marts
Data
Warehouse Layer
Master
Data
Information Broadcasting
Distribute
planned
revenue
to
different
products
Predefined
Types of
Planning
Functions
Delete
the data
Copy
data from
actual
version to
plan
version
revenue
by 10%
Convert
from Euro to
USD
Formulas
Quantity
times
Price
Calculation
Scheme for
Contribution
Margin
Calculate
Revenue
depending
on number of
workdays
in company
calendar
Customer
defined
Type
of Planning
Function
SAP
AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard / 6
Various Business Requirements and Their Solutions
In one plan
Version
Increase the planned
Predefined
Planning Function Types
Predefined
Planning Function Types
Copy
Delete
Repost
Revalue
Distribution
by Keys
Distribution
by Reference Data
Currency
Translation
Unit
Conversion
Generate
Combinations
Report
to Valid Combinations
Deletion
of Invalid Combinations
Tip
Try
to use one of these types or a
combination
of them first before
you
implement formulas.
SAP
AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard / 7
Formulas
(FOX)
Definition:
Formulas
define how the transaction data is to be processed in
order
to generate plan data. Formula functions enable you to use
extended
mathematical functions to calculate plan data.
In
addition to different calculation functions, which you can use for
value
assignment in formulas, there is also the possibility to model
complex
flow structures with the formula language FOX (FOrmula
eXtensions).
SAP
AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard / 8
Complexity
vs. Functionality
Predefined
Function
Types
Formulas
(FOX)
Customer
Defined
Function
Types
Complexity,
Functionality
SAP
AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard / 9
When
to Use Formulas (FOX)
Create
a formula if…
There
is no predefined planning function type that does the job
The
task cannot be done in one predefined planning function
type
but several planning functions/sequence are needed
The
customizing of a standard planning function type gets to
complicated
Performance
Many
times it is faster to have one formula doing the job than a
number
of planning functions
SAP
AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard /
10
When
to Use Customer Planning Function Types
Create
a custom planning function type if…
The
logic in FOX would be very complicated
You
need several complex FOX formulas for doing the job
You
need syntax elements that are not contained in FOX
You
need the same planning function in different levels. You can use the
same
function type in several places instead of copying a predefined or
formula
function to the different aggregation levels.
Disadvantages
You
need ABAP OO programming skills
Have
to make sure there is someone in the project that can maintain the
planning
function types (ABAP OO classes)
SAP
AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard /
11
Planning
Function Types
Designing
Planning Functions
Configuring
Formulas
Performance
Tuning of Formulas
How
to Design a Planning Function
The
following holds for EVERY planning function:
Define
the business scenario for the planning function.
Determine
the proper level – i.e. the level of aggregation in the
InfoProvider
that is needed for the business scenario. You have
to
identify the proper characteristics and key figures.
Write
down some sample records in that level.
Write
down how the data records should look like after executing
the
planning function.
Identify
the fields in the data records that are changed or used
for
calculation by the planning function. These are the “fields to
be
changed” in the planning function.
Identify
the type of planning function (predefined type, formula
(FOX),
exit) and configure the planning function.
SAP
AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard /
13
Example
for a Planning Function
Business
Scenario
You
want to copy the amount for each product from the current year to
the
next year.
Aggregation
Level
We
use an InfoProvider with 0PRODUCT and 0FISCYEAR as
characteristics
and 0AMOUNT as key figure.
0PRODUCT
0001
0002
0FISCYEAR
2007
2007
0AMOUNT
100
200
Before
0PRODUCT
0001
0001
0002
0002
0FISCYEAR
2007
2008
2007
2008
0AMOUNT
100
100
200
200
After
Field(s)
to be changed
SAP
AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard /
14
Planning
Functions – Subsets
To
make it easier to configure planning functions, the systems
packages
the plan data into smaller subsets.
Packaging
into subsets is based on what fields are changed by the
function
The
more fields that can be change, the lower the number of subsets
– All
fields can be changed = One subset with all data
– No
fields can be changed = One subset per record
The
plan data for each subset is processed separately
SAP
AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard /
15
Planning
Functions – Subsets: Example
Sample
data
records:
0PRODUCT
0001
0001
0002
0002
0FISCYEAR
2007
2008
2007
2008
0AMOUNT
100
100
200
200
Fields
to be changed
Empty
Year
Product
All
Fields
for grouping
All
Product
Year
Empty
Number
of calls/records
4
calls,
one
record per call
2
calls,
records
(1+2) and (3+4)
2
calls,
records
(1+3) and (2+4)
1
call,
all
records
SAP
AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard /
16
Flowchart
for Planning Functions
Start
of Execution
Data
Selection (based on Filter)
Build
Subsets from Selected Data
Phase
1
Reference
Data (based on Function)
Add
Subsets for Reference Data
FOREACH
Subset.
Phase
2
Execute
Function
ENDFOR.
End
of Execution
SAP
AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard /
17
How
to Test Planning Functions
Tips
for Testing Planning Functions
Create
filters for small packages of plan data (e.g. 2 records)
Create
a test query for your filter
Put
the fields to be changed in the rows, the key figure(s) in the
columns,
and the remaining characteristics in the header (free
characteristics).
As a result, the query output looks like the data
records
that are processed by the formula.
Execute
the planning function using the trace ("execute with
trace")
In
formulas you can send messages with the relevant values to
the
message log of the planning function
SAP
AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard /
18
Planning
Function Types
Designing
Planning Functions
Configuring
Formulas
Performance
Tuning of Formulas
Configuring
Formulas 1/3
1.
Start the Planning Model and go to the Planning Function tab
2.
Choose Create
3.
Select the type Formula,
enter
technical name and
description,
and pick the aggregation level
4.
Choose Transfer
SAP
AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard /
20
Configuring
Formulas 2/3
5.
Select which fields should
be
changed (mandatory) and
which
fields should be used
for
conditions (optional)
6.
Enter the conditions
SAP
AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard /
21
Configuring
Formulas 3/3
{SEMPS_003,
Mango Juice} = {SEMPS_003, Apple Juice} + {SEMPS_003,
Orange
Juice}.
7.
For each condition, enter
the
formula (FOX) in the
editor
Various
forms of input help and
documentation
of program
elements
are available
Control
display showing
which
fields can be changed
in
the formula.
SAP
AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard /
22
Formula
Syntax
Syntax
for formulas (FOX)
A
formula consists of one or many statements
Each
statement needs to be concluded by a period "."
Among
others a statement can contain…
one
or several operands for reading data
one
operand for changing data
various
functions for calculations and information retrieval
various
program elements
Choose
Check
button
in the editor to verify syntax of formula.
SAP
AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard /
23
Formula
Elements
Calculations
Access
Data Records
Information
Retrieval
(e.g.
Variable
Values)
String
Operations
Formulas
(FOX)
Call
External
Functions
Foreach
and Do
Loops
Conditional
Branching
Programming
Variables
Predefined
Mathematical
Functions
FOX
= Formula Extensions
SAP
AG 2007, Tips and Tricks for Formulas in BI-IP / Marc F. Bernard /
24