4 Script Logic Approach Script Formulas Use when calculation can be performed before the aggregations Data is generated at the base levels in all dimensions and the cube performs natural aggregations Triggered via a data update or DTS package Currency conversion, Unit X Rates, Allocations etc. Syntax MDX or SQL based logic? Note: SQL Logic is NOT Microsoft but our proprietary language processed on the application tier Answer is ALWAYS go with SQL logic MDX will perform poorly with concurrent users on the application SQL logic has the same capabilities with new functions like Calc_Dummy_Org for parent values and Calc_Each_Period for carry forward logic SAP Proprietary 9/21/06 4

8 SQL Based Logic Developed for performance and scalability Completely separate syntax (proprietary) Bases calculations on the existence of data (record) in FACT tables (always keep this in mind as you get more advanced in your calculations this can be critical in your design) Calculations performed at the SQL level not the OLAP (Cube) level. Major paradigm shift SAP Proprietary 9/21/06 8

9 SQL Based Logic Behind the Scenes Logic module loads a selection of data into memory (scope of the query). The user defined formulas are applied to the selected data and a result set of records is generated The result set is written back to the database. SAP Proprietary 9/21/06 9

10 SQL Based Logic Behind the Scenes Understanding the selection region (Scope of the Query) When invoked via Excel the Scope is Account Dimension All non-calculated accounts All Other Dimensions Only specific members sent via worksheet/workbook When invoked via a DMM Package Selected Dimension Only members selected for prompted dimensions (package and logic dependent) Non-specified Dimension All base members Currency LC if not specified Scope can also be controlled by written logic instructions in the script SAP Proprietary 9/21/06 10

13 SQL Based Logic Basic Syntax The *REC statement generates a new record *REC[([FACTOR EXPRESSION={Expression}[,{dim1}= {member},{dim2}= ])] FACTOR and EXPRESSION Derive the new value by applying simple math. FACTOR is faster and simpler to use if just multiplying the value of current record by something (an exchange rate or a price or a percent ownership or just a straight value). EXPRESSION lets you write more complex formulas like EXPRESSION=(%VALUE%+GET(ACCOUNT="SomeAccount"))/GET(ACCOUNT="SomeOt heraccount") you cannot write this expression using a FACTOR %VALUE% contains the value of current record. The GET lets you retrieve the value of some other record. Hint: the GET only looks for records you already have in memory, it does not go to the DB. The last parameter(s) allow you to redirect the result to different dimension members. SAP Proprietary 9/21/06 13

14 SQL Based Syntax - Factor To calculate a new Statistical Account Named UpsideRev that is the total of all revenue accounts marked up 25%.. *WHEN ACCOUNT.GROUP *IS REV *REC(FACTOR=1.25,ACCOUNT= UpsideRev ) *ENDWHEN *COMMIT SAP Proprietary 9/21/06 14

15 SQL Based Syntax - Expression To calculate a new Statistical Account Named UpsideRev that is the total of all revenue accounts marked up 25%.. *WHEN ACCOUNT.GROUP *IS REV *REC(EXPRESSION=%VALUE%*1.25,ACCOUNT= UpsideRev ) *ENDWHEN *COMMIT SAP Proprietary 9/21/06 15

16 SQL Based Logic Syntax - Get GET allows you to apply another value from the selected region and use it in the FACTOR or EXPRESSION. GET({dimension}={member}[, {dimension}={member}] ) Instead of hard coding a percentage, to pull the percentage from another Account named UpsidePCT *WHEN ACCOUNT.GROUP *IS REV *REC(FACTOR=GET(ACCOUNT= UpsidePCT ),ACCOUNT= UpsideRev ) *ENDWHEN *COMMIT SAP Proprietary 9/21/06 16

17 More Logic Control There are more methods of controlling your complex logic execution: Expanded scope Selecting a range SAP Proprietary 9/21/06 17

18 Query Scope Considerations Reminder: SQL logic only applies to the region of records in memory (not in the DB) There are often issues where a value required in the calculation is outside the scope of your selection. There are a a few of ways to handle this Expand the member region Override the member region Use the LOOKUP\ENDLOOKUP structure SAP Proprietary 9/21/06 18

19 Expand Member Selection It is possible to expand the selection range passed into the Logic Module runtime memory. *XDIM_MEMBERSET {Dimension name} = {Members Set} We want our Unit * Price Calculation to run on the Non_Interco member of the INTCO Dimension. *XDIM_MEMBERSET IntCo = Non_Intco It is possible to merge members to the set of members passed into the region. *XDIM_ADDMEMBERSET {dimension} = {members set} SAP Proprietary 9/21/06 19

20 SQL Based Logic Syntax - Memberset Assume you need only this calculation: Account A = Account B + Account C The logic to achieve this will look as follows: *WHEN ACCOUNT *IS B,C *REC(ACCOUNT=A) add them into account A) *ENDWHEN (if you find values for these accounts This logic would, by default, load in memory all possible accounts. However, if this is the only calculation, there is no need to load in memory all accounts, and the logic will run faster if it s written this way: *XDIM_MEMBERSET ACCOUNT=B, C *WHEN * *IS * *REC(ACCOUNT=A) *ENDWHEN The instruction * XDIM_MEMBERSET supports also the not equal to operator with the syntax: * XDIM_MEMBERSET {Dimension}<>{MemberSet} SAP Proprietary 9/21/06 20

21 SQL Based Logic Syntax Calc_Dummy_Org To avoid MDX to get a parent value, you can generate parent totals in memory // make sure you have all entities in memory *XDIM_MEMBERSET ENTITY=<ALL> // generate all parent values based on the hierarchy number *CALC_DUMMY_ORG ENTITY=PARENTH1 // use them as appropriate (note the # sign for valid Parent IDs) *WHEN ENTITY *IS #SALESEUROPE *REC(FACTOR=1/GET(ENTITY=#WORLDWIDE1), ACCOUNT= SomeRatio ) *ENDWHEN SAP Proprietary 9/21/06 21

22 SQL Based Logic Syntax Calc_Each_Period To avoid MDX to get previous balances, you can generate the carry-forward balance Combination of the following is required: Keyword Prior SQL syntax for Calc_Each_Period SQL syntax for Calc_Dummy_Org Memory variables SAP Proprietary 9/21/06 22

24 Selecting a Range We want our Unit * Price Calculation to run on the members of the INTCO Dimension where the GROUP Property has a value of NoInp. *SELECT(%IC_MBRS%, ID, INTCO, [GROUP] = NoInp' ) *XDIM_MEMBERSET IntCo = %IC_MBRS% *SELECT is run against the SQL tables Or using MDX against the cube *MEMBERSET({variable}, {member set in MDX format}) *MEMBERSET (%IC_MBRS%, filter{[intco].members, [INTCO].properties( GROUP )= NoInp ) *SELECT and *MEMBERSET statements are executed at the time the logic is validated, and the expanded result is written in the LGX file. This means that if the related dimension is modified, it may be necessary to re-validate the logic. Workaround is to call an LGF file instead of an LGX file in the DM package. The file is validated at run-time which captures any recent changes to the dimensions. Can also call an LGF for default logic using an include mydefault.lgf SAP Proprietary 9/21/06 24

25 Selecting a Range We want our Unit * Price Calculation to run on the base members of the DataSrc Dimension under the PreAdj Parent.. *MEMBERSET(%DS_MBRS%,"Descendants([DataSrc].[PreAdj],999, LEAVES)") *XDIM_MEMBERSET DATASRC = %DS_MBRS% *MEMBERSET uses MDX and is run against the Cube. The MDX query is very light (no access to values only to dimension information), so we still use it. In allocations there is newer syntax not requiring MDX by which you can say DATASRC=BAS(PREADJ) *XDIM_MEMBERSET, as follows: *XDIM_MEMBERSET DATASRC=BAS(PREADJ) However it's not supported yet. If you have only one level you can also use this instruction, which is fully SQL: *SELECT(%DS_MBRS%,ID,DATASRC,PARENTH1='PREADJ') *XDIM_MEMBERSET DATASRC=%DS_MBRS% SAP Proprietary 9/21/06 25

26 Copyright 2006 SAP AG. 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 AG. The information contained herein may be changed without prior notice. Some software products marketed by SAP AG and its distributors contain proprietary software components of other software vendors. Microsoft, Windows, Outlook, and PowerPoint are registered trademarks of Microsoft Corporation. IBM, DB2, DB2 Universal Database, OS/2, Parallel Sysplex, MVS/ESA, AIX, S/390, AS/400, OS/390, OS/400, iseries, pseries, xseries, zseries, System i, System i5, System p, System p5, System x, System z, System z9, z/os, AFP, Intelligent Miner, WebSphere, Netfinity, Tivoli, Informix, i5/os, POWER, POWER5, POWER5+, OpenPower and PowerPC are trademarks or registered trademarks of IBM Corporation. Adobe, the Adobe logo, Acrobat, PostScript, and Reader are either trademarks or registered trademarks of Adobe Systems Incorporated in the United States and/or other countries. Oracle is a registered trademark of Oracle Corporation. UNIX, X/Open, OSF/1, and Motif are registered trademarks of the Open Group. Citrix, ICA, Program Neighborhood, MetaFrame, WinFrame, VideoFrame, and MultiWin are trademarks or registered trademarks of Citrix Systems, Inc. HTML, XML, XHTML and W3C are trademarks or registered trademarks of W3C, World Wide Web Consortium, Massachusetts Institute of Technology. Java is a registered trademark of Sun Microsystems, Inc. JavaScript is a registered trademark of Sun Microsystems, Inc., used under license for technology invented and implemented by Netscape. MaxDB is a trademark of MySQL AB, Sweden. SAP, R/3, mysap, mysap.com, xapps, xapp, SAP NetWeaver, and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP AG in Germany and in several other countries all over the world. All other product and service names mentioned are the trademarks of their respective companies. Data contained in this document serves informational purposes only. National product specifications may vary. The information in this document is proprietary to SAP. No part of this document may be reproduced, copied, or transmitted in any form or for any purpose without the express prior written permission of SAP AG. This document is a preliminary version and not subject to your license agreement or any other agreement with SAP. This document contains only intended strategies, developments, and functionalities of the SAP product and is not intended to be binding upon SAP to any particular course of business, product strategy, and/or development. Please note that this document is subject to change and may be changed by SAP at any time without notice. SAP assumes no responsibility for errors or omissions in this document. SAP does not warrant the accuracy or completeness of the information, text, graphics, links, or other items contained within this material. This document 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. SAP shall have no liability for damages of any kind including without limitation direct, special, indirect, or consequential damages that may result from the use of these materials. This limitation shall not apply in cases of intent or gross negligence. The statutory liability for personal injury and defective products is not affected. SAP has no control over the information that you may access through the use of hot links contained in these materials and does not endorse your use of third-party Web pages nor provide any warranty whatsoever relating to third-party Web pages. SAP Proprietary 9/21/06 26

Document split in new G/L Document splitting in new G/L part 4 SAP ERP SAP AG Leading item cross company posting The example focuses in the configuration of the leading item. SAP AG 2004 / General Ledger

Overview: Interactive Forms in Guided Procedures Document Version 1.00 November 2005 - What is an interactive form? An Interactive Form is a selfcontained electronic representation of a paper form with

ALE2011 Berlin An Unconference for Lean and Agile practitioners 10 years of Agile We ve been challenged What s next I m little bit skeptical about Enterprise Agile Rachel Davies 2 Complexity approach We

Planning Project-Based SAP Service Delivery Projects with SAP Portfolio and Project Management, as a Technical Quality Manager (TQM) How to 1. How to create a project (Project Lead) 2. How can I find my

Maintenance Management with SAP Solution Manager An Overview of Maintenance Certificate for VAR Scenario SAP Global Service & Support December 2009 Disclaimer This presentation is a preliminary version

Research Priorities for the European Software Industry Roger Kilian-Kehr, SAP Motivation Need of European businesses: Effective use of ICT Challenges & inhibitors today: complex ICT landscapes, hard to

System Monitoring and E2E RCA for hybris with SAP Solution Manager 7.1 SP13 Disclaimer This presentation outlines our general product direction and should not be relied on in making a purchase decision.

Advanced Availability Transfer Transfer absences from HR to PPM A PLM Consulting Solution Public Advanced Availability Transfer With this solution you can include individual absences and attendances from

How to Request a Maintenance Certificate manually through SAP Support Portal? Options to Request a Maintenance Certificate How to Request a Maintenance Certificate via System Data How to Request a Maintenance

Customer Influence Program Why do we need a Feedback Agreement? Active Global Support 2012 Why do we need a Feedback Agreement? (You may use the content of this slide for customer communication.) The Feedback

Lead market potential: The promise of Networking Embedded Devices Stamatis Karnouskos Senior Researcher, SAP AG Consultation Meeting of the European Commission on "Sensing, Monitoring and Control" 28 March