Scribd
Upload
2K views8 pages

SAP BPC Script Logic

Script logics in SAP BPC are used to automate complex data transformations and calculations during the planning process. They allow things like applying seasonality, reflecting external conditions, and calculating rebates to be integrated automatically into plan data. There are two main types of syntax for script logics - SQL-based for better performance and MDX-based for record-level calculations. Script logics provide flexibility to perform calculations at the model level and can be executed from various places like the data manager. They require considerations like using commits sparingly and choosing SQL over MDX for performance.

Uploaded by

sekhardatta
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
2K views8 pages

SAP BPC Script Logic

Script logics in SAP BPC are used to automate complex data transformations and calculations during the planning process. They allow things like applying seasonality, reflecting external conditions, and calculating rebates to be integrated automatically into plan data. There are two main types of syntax for script logics - SQL-based for better performance and MDX-based for record-level calculations. Script logics provide flexibility to perform calculations at the model level and can be executed from various places like the data manager. They require considerations like using commits sparingly and choosing SQL over MDX for performance.

Uploaded by

sekhardatta
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 8

SAP BPC Version 10 Script Logic

BACKGROUND:
1. Once the initial versions of dataset are chosen/prepared, there comes
the need of script logic in BPC, which are required to help in the copy
process, to apply various seasonality, to reflect external conditions to
the plan data, to calculate various rebates, discounts & offers etc,
Anything that your company participates in a normal business process
can be integrated into the plan data, automatically using script logics.
Script logics can also be used for on-the-fly-calculation (real-time)
nature and also for obvious/automatic data transformations such as
currency transformation, consolidation etc at a chosen/scheduled time.
2. Script Logics can be used for PLANNING; as well as for
CONSOLIDATION activities.
3. Script Logics, for most of the Consolidation activities are provided
through available SAP Standard scripts
4. Technically; Script Logic is always created against a model, for a
model. Example model: HR, Overheads, Exchange Rates, Project
systems.
5. Functional planning is done at each model, before they are merged
into management model, where financial statements are formed, such
as P/L Statement, Balance Sheet or a Cash flow statement.
6. And Custom built Script logics are used for COMPLEX transformation
during functional planning, during management model integration, for
consolidation, during transfer between the models.
7. Standard (SAP Delivered) script logics can also be used/edited before
they are used for balancing the Cash flow statement or working out
the Consolidation steps (Currency Translation, Intercompany
elimination etc)
TECHNICAL :
1. Administration tab in BPC > Rules > Logic Scripts > Select
Model > New/Edit/Delete/Copy > Editor screen > Add code
2. DEFAULT.LGF is the standard (SAP Delivered) script logic, which is
executed anyway during any planning process. In order to achieve
efficiency & easy maintenance; it is probably a good idea to
include/embed all other script logics into this DEFAULT script logic
using INCLUDE command.
3. During a SAVE process, data is first committed to the SELECTED model
and the DEFULT logic is run on the model data.
4. DEFAULT LOGIC can also be invoked from a DATA MANAGER, during
execution of a planning process.
5. Beauty is with the standard logic, SYSTEM_CONSTANTS.LGF which is
used to default the actual Dimension names in the BPC system.
Every time, dimension name changes, the constants in the file have to
change, manually.
STRUCTURE OF SCRIPT LOGIC:
1. Scoping (Restrict the dataset; using which the calculation will be
performed)
2. Body part / coding (Perform Calculation, Transformation,
Aggregation etc )
3. Commit (Save the calculated result to the database)
TYPES OF SYNTAX IN SCRIPT LOGIC:
1. SQL based syntax (Choose this option for better performance)
2. MDX based syntax (Choose this option for specific record level
calculations)
ADVANTAGES:
Helps in model level calculation, not at a member level of the
dimensions. It has multiple uses, it can be done using many different
ways and it can be executed from many different places and also it has
many different options, as far as syntax selection goes. Name of the
game is therefore; flexibility.
DISADVANTAGES:
Cant handle very complex calculation processing or bigger data
volume. That is when the ABAP-BADI option comes into picture. ABAP-
BADI codes can be executed from the script logics. Script Logics can
be executed/run using Data Manager. ABAP-BADIs can also be
executed directly from the DATA MANAGER.
TESTING / DEBUGGING:
1. Can be done using transaction UJKT. Script Logic code can be directly
inputted into the screen, data set can be selected, and Test is
executed.
2. The analysis will show various things such as: data selection,
performance, error analysis etc
PERFORMANCE CONSIDERATION:
1. Use *COMMIT statement as few times as possible (if possible once, at
the most) in a script logic.
2. Access the complexity of the requirement and choose whether a script
logic or ABAP-BADI is a better option.
3. Using MDX should be avoided as much as possible; due to lower
performance reasons.
4. Use memory carefully to load only the data records, which are required
in the script logic for calculation.
5. Make sure less numbers of nested statements, loops are used within
the script.
6. Script logic should be used only for real-time calculations only. All
other calculations for the existing data, actual data, market data; etc
are completed before they are used in the planning process or in the
script logic.
Two types of Syntax options in SAP BPC Script Logic. They are:
1. SQL based syntax (Choose this option for better performance)
2. MDX based syntax (Choose this option for specific record level
calculations)
SQL based syntax Simplification:
1. *ADD / *ENDADD = Value of set of members (accounts) are
added and included in another account.

2. *INCLUDE = Multiple Script Logic files with different purpose
can be added into a single fine using this command. During LGX
generation, all codes are combined into one big piece of code,
during runtime.

3. *REC = This statement generates a record / modifies a
record; to be posted in the database. Even if it is for the same
destination cell, a source record can generate as many
destination cell records. Multiple REC statements possible
between *END/*ENDWHEN statement

4. *XDIM_MEMBERSET = It restricts the data, filters the
source dataset to the required dataset for specific calculation,
intended in the script logic. For each restriction using a
dimension, there can be a separate XDIM statement. Generally it
is followed by the Dimension name & Member value/value set.
5. *XDIM_ADDMEMEBERSET = This statement is used to add
more member values to the scope of member value already
defined with the XDIM statement for conditional calculations.

6. *XDIM_FILTER = Filters the members of the scope of that
dimension. It does not filter the scope of data, rather filters from
available scope.

7. XDIM_MAXMEMBERS = Specifies the maximum number of
members that should be included in one query, per dimension
the syntax is associated with.

8. XDIM_PACKAGEBY = This syntax is used for parallel
processing. However configuration needs to be done in SPRO
need to be done before using this.

9. *SELECT / *ENDSELECT = Like any other SQL statement,
this syntax helps to retrieve a list of values from the master data
table (Dimension) and store them in a defined variable. Can be
written in any part of the logic and the resultant value can be
used in any part of the logic as well. Generally better to write in
the beginning of the logic structure.

10. *WHEN / *ENDWHEN = *WHEN/*ENDWHEN can be used
with property values of the dimension; to further create
conditions for data
processing/transformation/modification/manipulation, for the
REC statement. Ex: *WHEN ACCOUNT *IS XY00001234. Same
as SELECT/ENDSELECT statement, but this it used when
different transformation is needed for different type of field
values in a record. Hence multiple *WHEN/*ENDWHEN
statements possible within a script logic. It works as a loop
statement.

11. *DESTINATION_APP = Use to make the datasets of one
model available for other. It transfers the data from one model
to another. Between Dimension Category values. It is acts like
a MOVE-CORRESPONDING statement in traditional ABAP.

12. *LOOKUP / *ENDLOOKUP = Similar to
*DESTINATION_APP but this does not transfer the data
between the model, rather looks up for the required calculation.
It is acts like a READ statement in traditional ABAP. Example:
While doing currency translation of a value, it will be required to
look into the EXCHANGERATES model.

13. *TMVL = This is used to retrieve a time value after taking
into consideration an offset value from a given time period.
Similar concept existed in traditional ABAP. Example traditional
ABAP statement X = Y+0(4). If value of Y = 27.08.2013 or
20130827. X will return a value = 2013. This can be used
with WHEN, FOR, REC statements. Can be used with variables.
Multiple offset values with TMVL statements can be used with
XDIM statements.

14. *COMMIT = Saves/stores the calculated values in the
database. In case of two dependent calculations, resulting value
of the first calculation must be saved into the database with a
*COMMIT statement, before its value is used in another
calculation formula within the script logic.

15. *ADD_DIM = While copying data between two models, if
source model does not contain all the dimensions as the
destination model, missing dimensions in the source model can
be created/added using this syntax before data copy. Possible to
use this for multiple dimensions in one statement.

16. *RENAME_DIM = While copying data between two models,
if dimension names between the models do not match; then
mismatching dimension names can be renamed to be the same
before copy process. Possible to use this for multiple dimensions
in one statement.

17. *SKIP_DIM = While copying data between two models, if
destination model does not contain all the dimensions as the
source model, some of the destination models can be skipped
from copying process using this syntax. Possible to use this for
multiple dimensions in one statement.

18. *SELECTCASE / *ENDSELETC = This is select statement
with a CASE together to encourage conditions within. Works as
IF, ELSEIF & ENDIF statement in traditional ABAP.

19. *START_BADI / *ENDBADI =

20. *RUN_ALLOCATION =
21. *BEGIN / *END = These syntaxes are used with Boolean
Expressions (YES / NO). Similar concept, as may have seen in
BW-BEX formulas. The individual conditions will be enclosed in
parenthesis. If the condition is true, return value will be 1, else
0.

22. *IIF = These syntaxes work, just as IF, ELSEIF, ENDIF
statements in traditional ABAP. If the condition satisfies, then it
directs the record to undergo a calculation.

23. *DIMNAME_DIM =

24. *DIMNAME_SET =

25. *SUB/*ENDSUB =

26. *FOR / *NEXT = This syntax is used with a variable, which
returns/contains multiple values. This syntax then helps to
process individual variable values, once by one. It works like
FOR EACH statement in FOX formula.

27. *GET = Retrieves the value from the memory

28. *FUNCTION / *ENDFUNCTION =

29. *WHEN _REF_DATA = It sets the focus to either master
data or transactional data. When is set to master data, it loops
through all the master data values of the dimension. And when
set to transactional data, it loops through all transactional data,
which uses the dimension/values in its scope. Without an explicit
definition, transactional data mode is selected by default.
Notes:
* at the beginning of a statement, means it is an executable
statement.
There is no FULL STOP needed in any of the above statements.
DimensionName.Property = ACCOUNT.RATETYPE. In this example
Account is the Master data and Rate type is the attribute. If
Property is not defined or missing, then the Primary Key is assumed,
which in this case would be Account ID OR GL Account.
Constant values of Numeric form, should not be included in double
quotes.
Constant value with String form, should be included in double quotes.
While a comma between the member values reads/records
individual member values; a single member value after the = sign;
summarizes all children values of the members to one parent record.
Bas () syntax with a member value is used to read all children values
of a member defined. Generally used to copy values between time
periods at a summary level, without defining every single member.
A BAS () syntax can not be combined with another member value in a
single statement.
[# ] syntax with a member value is used as the target destination; to
hold value after calculation in the memory, before saving it into the
database with a *COMMIT statement.
Statement *REC(FACTOR=4, CATEGORY=FRCST) is used to multiply
the value of the record by 4 and to the result under the category
FRCST.
Statement *REC(EXPRESSION=%VALUE% + 750) is used to add the
original value of the record, which is represented by a variable with
750.
Variables are represented with notation %VARIABLE% (percentage
sign before and after).
MDX based syntax Simplification
More detail or granular level calculation possible with MDX based
syntaxes in script logic
It is possible to focus on a specific record by indicating a dimension
value.
Generally the syntax contains two parts: Dimension itself and
Dimension value with a decimal point in between to tie them up
Both Dimension & Dimension value are bracketed as below for the
MDX syntax to work
Most of the syntaxes written above with SQL approach, can be written
with MDX approach as well
MDX syntax is straight forward, but may incur a performance penalty.
The SQL syntax involves the creation of two records: one with the
value originally held by A and one with the value originally held by B.
The two new records , assigned to account C by the REC statements,
then aggregate up to the correct value.
MDX: [#C] = [A] + [B] The calculated member is prefixed with a #
SQL: *WHEN ACCOUNT *IS A
*REC(ACCOUNT= C )
*IS B
*REC(ACCOUNT= C )
*ENDWHEN
Revaluation example with MDX: [ACC].[#KEYFIGURE] =
[ACC].[KEYFIGURE] * 1.10
Addition example with MDX : ADD%ACCT%=[ACCOUNT1],
[ACCOUNT2], [ACCOUNT3], [ACCOUNT4]
SAMPLE SYSTEM VARIABLES
USER% - Returns current planning & consolidation User
%APPSET% - Returns current planning & consolidation AppSet
%APPLICATION% - Returns current planning & consolidation
Application
%YEAR% - Returns current planning & consolidation Current Calendar
Year

Common questions

Powered by AI

The '*LOOKUP' statement in SAP BPC is effective for retrieving and using data from different models without transferring it, which is essential during calculations like currency translation where external data from models like EXCHANGERATES is needed. This statement serves as a conceptual "READ" operation that references necessary data without the overhead of data transfer, thereby ensuring that data integrity and model independence are maintained. This approach is efficient when frequent cross-model data access is required .

The '*SELECT / *ENDSELECT' syntax in SAP BPC script logic allows for the retrieval of a list of values from a master data table and stores them in a variable. This capability enhances functionality by enabling dynamic data selection and manipulation within the script, based on variable contents. It provides flexibility to use these values in computations throughout the logic and is typically positioned at the beginning of the script for optimal use .

Script logic in SAP BPC provides flexibility and can be used for various calculations and transformations at the model level, not at the member level of the dimensions. It allows for real-time calculations and the execution of processes in different ways and from different places. However, it struggles with very complex calculations and larger data volumes. In contrast, ABAP-BADI is suited for handling complex processing and larger data volumes, which script logic cannot manage. Both can be executed from the script logics or directly from the data manager, but script logic is more suited for transactional data and real-time processing, whereas ABAP-BADIs provide more robust solutions for comprehensive calculations .

The '*XDIM_MEMBERSET' statement in SAP BPC script logic is used to restrict the data scope by filtering the source dataset to the required dataset for specific calculations intended in the script logic. It sets the initial context for which data will be processed. In contrast, the '*XDIM_ADDMEMBERSET' statement adds additional member values to this already defined scope, allowing for conditional calculations. Therefore, while the former is used for initial filtering, the latter expands the context as needed .

SQL-based syntax should be preferred over MDX-based syntax in SAP BPC script logic when performance is a critical consideration. SQL-based syntax generally provides better performance and is recommended for most calculations or transformations, especially when handling large datasets. MDX-based syntax, while allowing for more granular level calculations, may incur performance penalties and is better suited for specific record-level calculations .

DEFAULT.LGF is a standard script logic provided by SAP that executes automatically during any planning process in BPC. It serves as a base logic file to which other custom script logics can be included using the INCLUDE command for better efficiency and maintenance. This default script is critical as it ensures that essential calculations and transformations are executed without the need for manual intervention, streamlining the planning process .

The '*RUN_ALLOCATION' syntax in SAP BPC script logic is used to manage data allocation processes by distributing amounts across different dimensions based on predefined rules. This feature facilitates efficient distribution of values, such as allocating budgets or expenses, by automating the calculation process and ensuring that data is accurately allocated according to specific criteria, thereby saving time and reducing the risk of manual errors .

The '*COMMIT' statement in SAP BPC script logic is crucial for saving or storing calculated values into the database. Its usage can significantly affect performance because each commit requires a transaction to be processed by the database. Therefore, it is advised to use the '*COMMIT' statement sparingly—ideally, only once within a script—to minimize the processing overhead. This ensures efficient use of resources and improves the overall performance of the script .

The structure of a script logic file in SAP BPC enhances its functionality by organizing the process into three main parts: 'Scoping', which restricts the dataset for which the calculations will be performed; the 'Body part/coding', where actual calculations, transformations, and aggregations occur; and 'Commit', where the calculated results are saved to the database. This clear structure helps in managing the logic's efficiency and performance, ensuring that data is correctly processed and stored .

Script logic struggles with handling large data volumes due to its limited capacity for complex calculations and memory constraints. This challenge can be mitigated by carefully scoping the data to be processed, using the '*XDIM_MEMBERSET' to limit the working dataset, and minimizing the use of nested statements or loops that can increase processing time. Additionally, transitioning to ABAP-BADI for more complex or large-scale processing needs can alleviate performance issues inherent to script logic .

You might also like