bank analyzer – index creation

Best_Practice_Bank_Analyzer_Index_Creation_V21.doc – 11.11.2008

Best Practice

Bank Analyzer – Index Creation

Dietmar-Hopp-Allee 16D-69190 Walldorf

CS STATUScustomer published

DATE VERSION

Nov-11 2008 2.1

SOLUTION MANAGEMENT PHASE SAP SOLUTION

Operations & Continuous Improvement SAP Banking (Bank Analyzer)

TOPIC AREA SOLUTION MANAGER AREA

Application & Integration Management Configuration Management

Best PracticeBank Analyzer – Index Creation

© 2008 SAP AG - Best_Practice_Bank_Analyzer_Index_Creation_V21.doc page 2/18

Table of Contents1 Management Summary 3

1.1 Goal of Using This Document 31.2 Alternative Practices 31.3 Duration and Timing 3

2 Best Practice 42.1 Preliminary Tasks 42.2 Setup 4

2.2.1 Steps to Implement 42.2.1.1 Index Creation for SDL tables (/BIC/….) 42.2.1.2 Indexes for Segmentation Service Structures (/1SGS/…) 52.2.1.3 RDL Indexes for Result Data Areas 102.2.1.4 Secondary Indexes for Accounting Tables (/1ACC/….) 13

2.2.2 KPI Monitoring and Optimization 17



1 Management Summary

Bank Analyzer is a generic solution, where most of the structures and tables are generated during theimplementation process, depending on the customer-specific definition of characteristics and key figures.Because of this high flexibility inside the different scenarios of Bank Analyzer it is not possible to deliver SAPstandard indexes for the mentioned generated structures and tables.

To achieve the expected throughput of the mass processes, a technical performance analysis is required toidentify missing indexes on the customer defined structures and tables. Without the additional secondaryindexes it is not possible to process high volumes and the Bank Analyzer solution will not be scalable.

The document explains how to and where to create indexes for Source Data Layer (SDL) objects,segmentation service structures (SGS), result data area (RDA) types and accounting journals which can beused in a Bank Analyzer solution.

1.1 Goal of Using This Document

The goal of this document is to provide the step sequence of creating a table-specific index based on our bestpractices inside the customizing of Bank Analyzer.

The generated structures and tables from the implementation process will only keep their created indexes inthe system after release upgrades if the necessary indexes were created with the relevant customizingsettings. Otherwise the indexes on structures and tables will be lost. Therefore, it is mandatory to follow theIndex Creation process explained in the document.

In addition to that, the Index Creation process is also relevant for newly created structures and tables in aproductive environment because no SAP standard indices are delivered for customer-defined structures.

1.2 Alternative Practices

There is still the possibility to create an index outside the recommended step sequence, e.g. directly on tablelevel, but doing this could lead to tremendous problems: During transportation of generated tables to the productive system, an index that was created outside

customizing is lost. Technical experts who follow the recommended way to create an index in customizing might not recognize

that there is something done outside. This means doubled effort and wasted DB space.

1.3 Duration and Timing

The creation of appropriate indexes is an ongoing process. Each time that changes to the end-to-endbusiness process causes additional tables, the impact on performance has to be measured and due to this,the creation of an adequate index has to be taken into account.



2 Best Practice

The following sections describe the steps necessary to create indexes within a Bank Analyzer solution besidethe standard SAP methodology for index creation in SAP dictionary.

2.1 Preliminary Tasks

Before any index can be created within a Bank Analyzer solution, a performance analysis by technicalexperts is mandatory. This includes the analysis as well as the definition of the required indexes. BankAnalyzer is a very generic solution, where most of the structures and tables are generated during theimplementation process, depending on the customer-specific definition of characteristics and key figures. Asthe structure and table name differ between different systems (Development-Consolidation-Production) theonly way to maintain indices on the structures is the structure or table description in the customizing which isidentical for the systems.

2.2 Setup

2.2.1 Steps to Implement

In the following, you will find the description of necessary steps to configure and customize indexes.

2.2.1.1 Index Creation for SDL tables (/BIC/….)

How to proceed? Get the name of the ODS object. In the source system, create the index wanted using RSA1 in the development system. Transport the changes into the production system.

You can easily derive the name of the ODS object from the table name.

Example: Table name is /BIC/AZDDKX00100 Remove /BIC/A and the last two digits (00). The remainder is ZDDKX001. Start Transaction RSA1

Choose .

Then use the search button above the InfoProvider column.

In our example, it would look like this:



Press Find and position to the object using the result list. Double-click the object.

With a right click when highlighting on Indexes you enter index creation.

Choose the fields you planned for the new index. Save and activate to create the index also in the DB.Finally, release the transport order to transfer the additional index into the production environment as well.

2.2.1.2 Indexes for Segmentation Service Structures (/1SGS/…)

Important: Make sure the customizing activities are done in the same client in which the job was running thatcontained the expensive SQL statement.

Scenario: You want to create a secondary index to defuse an expensive statement on "/1SGS/428D6902:



SELECT * FROM "/1SGS/428D2302" WHERE "MANDT" = :A0 AND “/BA1/C41FINST” = :A1 AND “/BA1/C42SACC” = :A2 AND“/BA1/C43CLACC” :A3 AND “/BA1/C47HEDGE” = :A4 AND “/BA1/C55ACCSY” = :A5 AND“/BA1/C55AOCL” = :A6 AND “/BA1/C55AOCURR” = :A7 AND “/BA1C55ASSLIA” = :A8 AND“/BA1/C55LGENT” = :A10 AND “/BA1/C55SP_NO” = :A11 AND “/BA1/C69TMBCKT” = :A12AND <= :A13

How to proceed? Find the logical structure in the production system. In the source system, maintain the logical structure creating the index wanted. Transport the changes into the production system.

Find the underlying logical structure

Transaction SPRO: Bank Analyzer Basic Settings Segmentation Service Edit Characteristic Structure



Make use of the menu option Where-Used List for SGS Tables.

Enter the table name and set Used Only:

You will find the wanted information on in the Structure column.



Segmentation service index definition in original system

Transaction SPRO: Bank Analyzer Basic Settings Segmentation Service Edit Characteristic Structure

Enter the name of the logical structure found in the production system and press to enter change mode.

In the Attributes tab you see the name of the table belonging to the logical structure in the dev-system.

The Fields tab contains the fields of the structure and Indexes shows the currently existing indexes. Becautious, the primary index does not show up here. The primary index consists of the key fields you see inthe table definition of the /SGS/… table in the ABAP dictionary.

Go to the Indexes tab.



Enter change mode and create index .

Enter an index ID and a reasonable description and press .

Now you can choose one or more fields you want to have as index fields. The client is added automatically.



The new index name consists of the name of the structure and the index ID. In this example/1SGS/8F423720~A

Save and activate .

In a realistic scenario, the change is stored in a transport order that needs to be released and transported toget the change in the production environment. In the target system, the logical structure will be altered andthe new index to the segmentation table will be created.

Important: Very often it is necessary to create at least indexes on the fields /BA1/C40FTRAN,/BA1/C41FINST and /BA1/C10UOID.

2.2.1.3 RDL Indexes for Result Data Areas

Important: Make sure the customizing activities are done in the same client in which the job was running thatcontained the expensive SQL statement.

Scenario: You want to create a secondary index to defuse an expensive statement on:

SELECT T_00 . "/BA1/CR0ROOTID" , T_00 . "/BA1/CR0KEYDAT" , T_00 . "/BA1/CR0TSTMP" , T_00 . "/BA1/C93TCID" , T_01 . "/BA1/CR0KEYDAT" , T_01 . "/BA1/CR0TSTMP" , T_01 . "/BA1/CR0LEAFID" , T_01 . "/BA1/C92SCEN" , T_01 . "/BA1/CR0ROOTID" , T_01 . "/BA1/KR0NC02" , T_01 . "/B20C/S_CCUSTTYP" , T_01 . "/B20C/S_CHEDGED" , T_01 . "/B20C/S_CHOLDCAT" , T_01 . "/BA1/C40FTRAN" , T_01 . "/BA1/C41FINST" , T_01 . "/BA1/C42SACC" , T_01 . "/BA1/C43CLACC" , T_01 . "/BA1/C55AOCURR" , T_01 . "/BA1/C55AOTYPE" , T_01 . "/BA1/C55IMPAIR" , T_01 . "/BA1/C55LGENT" , T_01 . "/BA1/C69HRLCAT" , T_01 . "/BIC/CCLTYPE" , T_01 . "/BIC/CDEPOTKNZ" , T_01 . "/BIC/CKZFRWRD" , T_01 . "/BIC/CKZHNDL" , T_01 . "/BIC/CLOANTYPE" ,T_01 . "/BIC/CPTORI" , T_01 . "/BIC/CSYNDIND"FROM "/1BA/HM_N3EYV001" T_00 INNER JOIN "/1BA/HM_N3EY_001" T_01 ONT_01 . "CLIENT" = :A0 AND T_00 . "VERSION_GUID" = T_01 . "VERSION_GUID"WHERE T_00 . "CLIENT" = :A1 AND T_01 . "/B20C/S_CCUSTTYP" = :A2 ANDT_01 . "/BA1/C41FINST" = :A3 AND T_01 . "/BA1/C43CLACC" = :A4 ANDT_01 . "/BA1/C55LGENT" = :A5 AND T_01 . "/BA1/C92SCEN" = :A6 ANDT_00 . "/BA1/CR0KEYDAT" <= :A7 AND T_00 . "/BA1/CR0TSTMP" <= :A8 ANDT_00 . "VALID_TO_KD" >= :A9 AND T_00 . "VALID_TO_TS" > :A10#



How to proceed? Find the logical structure in the production system. In the source system, maintain the logical structure creating the index wanted. Transport the changes into the production system.

Find the underlying logical structure

Transaction SE12: Enter the generated RDL table name /1BA/HM_N3EY_001

In the Short Description field of the table below there is the link to the relevant result data area. In this case itis result data area EHBA, client 001 and result data type SF_KEYDT.

Transaction SPRO: Bank Analyzer Result Data Layer Basic Settings Edit Technical Settings in ResultData Area



Mark the result data area EHBA and click on Result Types.

In the Result Type view, mark the relevant result type SF_KEYDT and then choose Index for Result Types.

The Index for Result Types view shows the currently existing indexes.

By clicking on Characteristics: Index for Result Types, the existing characteristics per index will be shown.

For index creation press and enter an index name and a reasonable description.



In the screen below, index Y0 was created with the index fields /BA1/C41FINST and /BA1/C55LGENT. Thefield Position defines the position of the characteristic field in the index. In the example, the field/BA1/C41FINST will be generated on the first position and /BA1/C55LGENT on the second.

Save and activate .

In a realistic scenario, the change is stored in a transport order that needs to be released and transported toget the change into the production environment. In the target system, the logical structure will be altered andthe new index to the result data layer table will be created.

Important: Very often it is necessary to create at least indexes on the fields /BA1/C40FTRAN,/BA1/C41FINST and /BA1/C10UOID.

2.2.1.4 Secondary Indexes for Accounting Tables (/1ACC/….)

Important: Make sure the customizing activities are done in the same client in which the job was running thatcontained the expensive SQL statement. The index creation for the accounting tables is relevant for the BankAnalyzer “Merge” scenario.

Scenario: You want to create a secondary index to defuse an expensive statement on /1ACC/049__KA001:

How to proceed? Find the journal in the production system using transaction /EACC/SHOW_PLIMA. In the source system, edit the index for journals in transaction SPRO. Transport the changes into the production system.



Find the accounting journal

Each accounting journal consists of tables. These are the system-generated Header, Item and Line Itemtables. In the accounting system IAS, there are the following journals:

In a first step, find the journal in which table “/1ACC/049__KA001” is acting either as Header, Item, or LineItem table.

Start transaction /EACC/SHOW_PLIMA.

Always set Without Number of Rows to avoid lengthy response times. Use the search help choosingAccounting System.

Use the search help choosing one of the available journals. Be aware that you might have to repeat this for allavailable journals to finally find the table.



Running the transaction with the settings shown above provides the following screen:

Here you see that example table /1ACC/049__KA001 is the Line Item table of journal COST_REV, and thusthe planned secondary index also needs to be maintained in the original system (development system) forjournal COST_REV.

Journal index definition in original (development) system

Transaction SPRO: Bank Analyzer Accounting Balance Analyzer After Generation Basic Settings Performance Optimization Edit Database Index for Journals

First choose the accounting system and journal. In our example these are S_IAS and COST_REV.

In the next screen, you see all indexes that currently exist. Indexes in blue are maintained by SAP and cannotbe changed.



Press and choose a clear name for the index, e.g. journal and the process step, plusperformance.

Example: To create a new index, add lines and fill IndexName, Item and Char.

If you want to create an index with the name COST_REV_BAUS_PERF consisting of the fields/BA1/C40FTRAN, /BA1/C41FINST and /BA1/C47HEDGE, the three lines should look like this:

IndexName Item Char.

COST_REV_BAUS_PERF 1 /BA1/C40FTRAN

COST_REV_BAUS_PERF 2 /BA1/C41FINST

COST_REV_BAUS_PERF 3 /BA1/C47HEDGE

Save and assign a customizing request.

So you ensure that the new index created for each table of the journal will also end up in the productionsystem.



2.2.2 KPI Monitoring and Optimization

Missing indices can be identified very well in transaction SM66.

As shown in the snapshot below, there are many processes in action sequential read on the same table(/1SGS/428D*). This is very often a good indicator that there is either no or no appropriate index on that table.

For the example a detailed performance analysis is necessary to identify the missing index fields and toimprove the performance.



© Copyright 2007 SAP AG. All Rights ReservedNo 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, z/OS, AFP, Intelligent Miner, WebSphere, Netfinity, Tivoli, and Informix are trademarks or registered trademarks of IBMCorporation.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 CitrixSystems, Inc.HTML, XML, XHTML and W3C are trademarks or registered trademarks of W3C®, World Wide Web Consortium, MassachusettsInstitute 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 byNetscape.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 theirrespective logos are trademarks or registered trademarks of SAP AG in Germany and in several other countries all over the world. Allother product and service names mentioned are the trademarks of their respective companies. Data contained in this document servesinformational 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 formor 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 documentcontains only intended strategies, developments, and functionalities of the SAP® product and is not intended to be binding upon SAP toany particular course of business, product strategy, and/or development. Please note that this document is subject to change and maybe 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 theinformation, 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 thatmay 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 mayaccess through the use of hot links contained in these materials and does not endorse your use of third-party Web pages nor provide anywarranty whatsoever relating to third-party Web pages.

bank analyzer – index creation

Documents