bpc - script logics

© 2010 Accenture. All rights reserved.

Training

SAP BPC 7.5 NW

Logics

2© 2010 Accenture. All rights reserved. Accenture, its logo and High Performance Delivered are trademarks of Accenture. SAP is a trademark of SAP AG, Germany

• Logics• Worksheet Logics• Dimension Logics • Script Logics

• Validation Engine

SAP BPC Logics

Agenda

Logics allows you to perform calculations on SAP BPC data.

You can create three different types of logic:

Worksheet logic Dimension logic Advanced (Script) logic

Each type has advantages and disadvantages.

You typically use some combination of all three types to achieve the desired results.

SAP BPC Logics

Logics

Uses SAP BPC and standard Excel mathematical operators and functions to produce results.

Is typically used for simple calculations and events, such as data retrievals (EvGTS), sends (EvSND), and variance calculations (EvBET).

Produces calculated values that are not stored in the database.

Exercise

Create a Range “_Data” with some numbers.

Create a EVSND function that Send the how many positive numbers are in Range

“_Data”.

Retrieve the value sent with a EVDRE function.

SAP BPC Logics

Worksheet Logic

Pros Speed: formulas reside in the worksheet, so calculations are fast and have minimal

impact on performance.

Flexibility: you can define many formulas in the worksheet.

No server load: worksheet logic uses the client machine to perform calculations, so it allows the server to run more efficiently.

Cons Lack of scalability: the formulas reside in the worksheet in which they are

defined, and need to be rewritten for each worksheet.

Cannot be applied as a process: worksheet formulas cannot be executed via the Data Manager (i.e. the calculations are not performed until the sheet is opened and the data refreshed).

Not stored: results are not stored in the database, cannot easily execute a further logic based on the result of the worksheet.

SAP BPC Logics

Worksheet Logic




SAP BPC Logics

Agenda

Is based on MDX syntax, and is assigned directly to a member.

MDX (Multi-Dimensional Expressions) is a language for querying and manipulating data stored into OLAP databases.

SAP BPC is shipped with many useful MDX functions that help define your calculated members.

Dimension logics use Formula property to store how the member value will be calculated

SAP BPC Logics

Dimension Logic

The Formula property

Must be added using “Maintain dimension property” in order to use dimension logic.

Must have a field size equal to at least the length of your longest formula.

Will appear as a column in the dimension member sheet.

SAP BPC Logics

Dimension Logic

You have the following members in the Account Dimension:

• Account1

• Account2

• Account3 (Formula is Account1/Account2)

You have the following members in the Product Dimension:

• Product1

• Product2

• Product3 (Formula is Product1 + Product2)

SAP BPC Logics

Dimension Logic

SolveOrder Example

When SolveOrder is not used:

When SolveOrder is used:

Incorrect result

because ratio is

calculated prior

to the addition.

Correct result

because ratio is

calculated after

the addition.

[Account3] = [Account1] / [Account2], SOLVE_ORDER=1

[Product3] = [Product1] + [Product2], SOLVE_ORDER=2

[Account3] = [Account1] / [Account2], SOLVE_ORDER=2

[Product3] = [Product1] + [Product2], SOLVE_ORDER=1

SAP BPC Logics

Dimension Logic

Formulas can affect retrieval performance if not used properly.

Dimension formulas should only be used for formulas that need to be calculated after aggregations (e.g. ratios).

Dimension formulas should not be used on members that need to aggregate.

Use the “SOLVE_ORDER” property when dimension formulas are used on more than one dimension.

Exercise

Create three account: ACC1, ACC2, ACC3.

Create a calculated account: ACCM, which value is the average of ACC1, ACC2, ACC3

Create a input schedule that send data on ACC1, ACC2, ACC3 and retrieve ACCM.

SAP BPC Logics

Dimension Logic

Dimension formulas calculate on the fly the result at retrieval time.

Pros• Consistency: the same logic is applied to all members in all applications that use

the dimension.

Cons• Performance issue: Dimension Logic slow retrieval performance.

• Lack of flexibility: dimension logic applies to all levels in the dimension and in all applications using the dimension.

• Not stored: results are not stored in the database, cannot easily execute a further logic based on the result of the worksheet.

• Load: Can create Load on server. Formulas are fast enough if used sparingly, but do not scale well on larger applications.

SAP BPC Logics

Dimension Logic




SAP BPC Logics

Agenda

Reads a specific data selection from the application.

• Applies to it a set of user-defined formulas (stored in a logic file)

• Derives the values

• Writes those values back to the application.

Results are written directly to the application as base data, and aggregated up the dimensional hierarchy as they are, without being recalculated at upper levels.

• All dimension logic formulas are applied to these results.

• This process allows you to perform such calculations as units times price because the formulas are applied only to those members specified in the logic file.

SAP BPC Logics

Script Logic

The Logic module:

Advanced logic can be automatically invoked each time data is sent to the database.

Logic contained in the Default logic file is executed immediately after data is sent.

Advanced logic can be run from Data Manager for batch processing of formulas.

Useful for calculations that do not need to be executed immediately.

For example, you may decide to wait until all the data has been entered in the local currency before generating the translated amounts in the reporting currencies.

Logics can be written using MDX language or SQL-based functions (better performance).

SAP BPC Logics

Script Logic

Script Logic Editor is available in the admin module, under each application.

SAP BPC Logics

Script Logic

Simple logic that make constant the value of an account:

This logic must be inserted on Default Logic, under Planning App.To see the result of this logic make a simple report that retrieve the value of this Account.Note that the value is constant only where a data has been sent.

SAP BPC Logics

Script Logic

Demo

Logic that increase an Account by 30%

This logic must be inserted on Default Logic, under Planning App.To see the result of this logic make a simple report that retrieve the value of this Account.Note that the value is constant only where a data has been sent.

SAP BPC Logics

Script Logic

Demo

Logic write Account 2 data on Account 1

This logic must be inserted on Default Logic, under Planning App, and write data sent on PL000008 to PL000006.To see the result of this logic make a simple report that retrieve the value of this Account.Note that the value is constant only where a data has been sent.

SAP BPC Logics

Script Logic

Demo

SAP BPC Logics

Script Logic

Logic: write Account 1 data / Account 2 data on Account 3 using SQL-based functions

Write this logic into a new logic file named Formula.lgf under Planning App, and insert into the Default Logic the following statement:

*include Formula.lgfThis logic will perform the ratio between the data value on Account TA0009 and the data value on the Account TA0004 and will store the result on the Account KPI0006.To see the result of this logic make a simple report that retrieve the value of these Accounts.

Demo

SAP BPC Logics

Script Logic

Logic: write Account 1 data / Account 2 data on Account 3 using MDX language

Write this logic into the Formula.lgf logic file created in the previous step, under Planning App.This logic will perform the ratio between the data value on Account TA0009 and the data value on the Account TA0004 and will store the result on the Account KPI0006.To see the result of this logic make a simple report that retrieve the value of these Accounts.

Demo

SAP BPC Logics

Script Logic

Logics are useful when you have to manage Price x Quantity calculation

P x Q Calculation


1. Create a new logic file named PxQ.lgf under Planning App.

2. Write a logic that performs the following PxQ formula:

Outgoing Volume (TA0013) = Average Customer Base (TA0004) * MOU (KPI0004)

3. Write into the Default Logic an include statement to include the PxQ logic file.

4. Create an input schedule and send some data to the Accounts TA0004 and KPI0004.

5. Verify that in the Account TA0013 is written the correct value.

SAP BPC Logics

Script Logic

P x Q Calculation Exercises

SAP BPC Logics

Script Logic

Allows to define and run a top-down allocation. Integrated into the script logic engine. Define the allocation logic using a tabular format.

An allocation is essentially always made up of the following components:

WHAT needs to be allocated WHERE the results of the allocation must be written What driver should be USED to perform the allocation How the allocation driver should be used, i.e. what FACTOR must be applied

to the source amounts during the calculation.

Allocation Engine

SAP BPC Logics

Script Logic

FACTOR USING/TOTAL

DIMENSION WHAT WHERE USING TOTAL

CUSTOMER CUS_ND BAS(TOT_CUS) BAS(TOT_CUS) BAS(TOT_CUS)

DATASRC MANUAL MANUAL AUTOMATIC_ALLOC AUTOMATIC_ALLOC

For each parameter, a region is defined:

Allocation Engine

SAP BPC Logics

Script Logic

FACTOR USING/TOTAL





Source Data

Region

Allocation Engine

SAP BPC Logics

Script Logic

FACTOR USING/TOTAL





Destination

Region

Allocation Engine

SAP BPC Logics

Script Logic

FACTOR USING/TOTAL





Driver

Region

Allocation Engine

SAP BPC Logics

Script Logic

FACTOR USING/TOTAL





Driver Total

Region

Allocation Engine

SAP BPC Logics

Script Logic

FACTOR USING/TOTAL




The applied formula in the sample above is:

(The amount of the WHERE region) = (the amount of the WHAT region) * (the amount of the USING region) / (The sum of the amount of the TOTAL region)

Allocation Engine

SAP BPC Logics

Script Logic

Data before allocation:

Drivers Source Data

Allocated Data

Data after allocation:

Allocation Engine


1. Open the logic file ALLOCATION.LGF under the Planning App.2. Define the following allocation:

Data to allocate region:- TIME dimension: DEC of the current year- CATEGORY dimension: BUDGET- DATASRC dimension: MANUAL

Destination region:- TIME dimension: All months of the current year- CATEGORY dimension: BUDGET- DATASRC dimension: MANUAL

DRIVER region:- TIME dimension: All months of the previous year- CATEGORY dimension: ACTUAL- DATASRC dimension: AUTOMATIC_ALLOC

TOTAL region:- TIME dimension: All months of the previous year- CATEGORY dimension: ACTUAL- DATASRC dimension: AUTOMATIC_ALLOC

FACTOR: DRIVER data region / TOTAL data region

3. Create an input schedule and send some data to the source and driver region.4. Run the Allocation package to launch the allocation logic created.5. Verify that the allocated data values are correct.

SAP BPC Logics

Script Logic

Allocation Exercise




SAP BPC Logics

Agenda


• Back-end characteristic validation engine

• Designed to prevent incorrect records being saved into the cube.

• Rule-based configuration of the validation checks.

• BAdI-based validation for more advanced checks.

• Covers all BPC modules (BPC Excel, BPC Data Manager, BPC Web,

Journals).

• Supersedes the Journal validation functionality of the MS versions.

SAP BPC Logics

Validation Engine


Driver Dimension

• The dimension of an application from which the rules will be

created.

• Can be any dimension

• Only one driver dimension per application.

• Rules created at dimension level (not application level).

• Thus, applications with the same driver dimension, shares the

same validation rules.

SAP BPC Logics

Validation Engine


• The configuration is not performed in the BPC interface, but in the SAP GUI, through UJ_VALIDATION transaction.

• The configuration is AppSet specific.

• The validation configuration of an AppSet can be maintained only by one user at a time.

SAP BPC Logics

Validation Engine


Assign Driver Dimension

• The Edit Rules button allows to jump directly to the maintenance of the validation rules for the selected dimension.

• This function allows to select the Driver Dimension for each application of the AppSet.

• An empty Driver Dimension means that no validation rules will be applied to the application.

SAP BPC Logics

Validation Engine


Rule Maintenance

• For each rule, specify the members of the driver dimension that the rule applies to.

• Is possible to specify members that not yet exist (a warning will be displayed).

• Define the validation rule by compiling the Logic Table.

• For more complex rules, is possible to create a custom validation logic by creating an implementation of the BAdI BADI_UJ_VALIDATION_RULE_LOGIC.

• This function allows to create/edit/delete the validation rules of the current dimension.

SAP BPC Logics

Validation Engine


Other Functionalities

• Copy Validation: allows to copy a validation configuration from the specified source AppSet to the specified target AppSet.

• Delete Validation Configuration: allows to delete the validation configuration of the specified AppSet.

• Turn Validation On/Off: allows to turn on/off the validation check for one or more modules of the specified AppSet.

• Check Validations: performs a check of the validation configuration of the specified AppSet.

SAP BPC Logics

Validation Engine

bpc - script logics

Documents

high performance delivered

logic file

validation engine

validation rules

default logic

data manager

validation configuration

range data