SAP Logical Databases applications providing Business-requirements-driven solutions
A.Selmeci *, T. Orosz**
* Óbudai Egyetem AREK, Székesfehérvár, Hungary
** Óbudai Egyetem AREK, Székesfehérvár, Hungary
E-mail selmeci.attila@arek.uni-obuda.hu; orosz.tamas@arek.uni-obuda.hu
Abstract— SAP, the leading Enterprise Resources Planning System in the World, has been providing different effective solutions for business requirements for more than 40 years.
Therefore, it might be a difficult issue to select proper solutions for a particular business issue. Usage of SAP Logical Databases provides a convenient tool including smart user interfaces, authorizations, linked database tables, etc. Although several SAP ABAP programs, that apply Logical Databases are being replaced by new programs applying more efficient database selection routines, data access efficiency is only a restriction of business requirements efficiency. The goal of our present paper is to emphasize that robust and reliable programming techniques still remain essential building blocks even though they are categorized as obsolete objects in some aspects. As a conclusion the proper application of Logical Databases are considered to be an efficient way of design and create new program developments
Keywords: SAP, ERP, Logical Database, Selection Screen, Authorization, Database Selection conditions, database access, Access optimization
I. INTRODUCTION
SAP, the leading Enterprise Resources Planning (ERP) System in the World, provides efficient tools and methods in several core and industry Business applications, like Financials, Logistics, Human Resources, Automotive, Pharmaceuticals, Public sector, etc. Changes and enhancements in SAP ABAP Programming techniques also reflect such continuous improvements. Thus one can find either old, probably obsolete, or new and modern development approaches when making an analysis of the source code. In spite of the oldness of a development technique, however, it could be very affective and useful in special areas. Logical Databases exemplify such a behavior of development techniques. This article discovers the technology behind the SAP logical database technique and figures out the power in this solution.
II. POSSIBLE DATA ACCESS IN AN SAPSYSTEM The SAP systems have multi-layered client-server architecture. The presentation layer can be divided into more tiers depending on the technology (see Fig. 1). The main solution is the SAP standard is the SAP GUI (Graphical User Interface), but for Web application other tiers play important roles as well. The server side has two layers: the database and the so-called application server
layer, which runs the business logic of the SAP applications. The application layer serves as a client from the database point of view, but it works a server, for the real end-users (clients) as well.
Figure 1 WebAS high-level technical architecture The SAP architecture an SAP system consist of one database and one or more application instances. The application data (separated to master- and transactional data) are stored in the same database as the customizing and the repository (development objects, like programs, screens, menus, table structure definitions and so on). As an SAP system can use several application instances managing their own memory areas this layer gives a great opportunity bringing up the load from the database layer to the application level. If we try to follow the tiers available from an SAP ABAP application program to the data files to retrieve business data we have many options and access speed corresponding to the tiers. Generally the data is stored in the underlying database and it should be fetched to the ABAP program. If we read the data first time after starting the system, the data is read really from the data file by the database management system.
Depending on the data selection condition the database systems can store the data read in the database buffer (database cache). That helps to the system to have a better performance in the future, because another selection on the same data can be fulfilled from the database cache, which is much more faster. Of course the modern data file storage solutions even they are local ones or storage systems, give some opportunity (e.g. storage cache) to speed up the data block access time, but it is not part of this article. The Figure 1 shows that the ABAP engine connects to the database layer by process communication, where the SAP dialog processes build up database
connections to the database server processes. From SAP side the Database Interface (DBIF), which is a part of the SAP work processes (mention as ABAP WP on the figure) manages this connection. The Database Interface plays a huge role in the database connection, because it checks the SAP Data Dictionary settings of the given table and uses this information to read the data, translates the SAP Open SQL call to the native SQL of the underlying database management system. DBIF is responsible for adding the client (mandant) condition to the client-specific tables as well. But from our perspective the main point is that it checks the technical settings of the table (defined in ABAP Dictionary) and according to the buffer settings, the DBIF tries to find the table in the buffer or if it is not there it will be retrieved from the database system.
Looking at these tiers of data retrieval we can think about the amount of data, which can be stored on the different levels and the access time of a data record by levels. In general case a database size is about 100 GB to 1+ TB, the database cache size is about 500 MB, the SAP ABAP buffer size is around 130 MB and the local work process memory is 5-8 MB. It means from perspective of loading that more data can be stored if it is further from the processing unit (in our case from the work process). The opposite direction is true if we check the access times. If the data is not yet cached by the database system, it should be read from the data files, which takes around 10ms. If the data is already cached the SAP database interface can access it in 1ms around. If the records are buffered according to the Dictionary settings, the work process can fetch the data in 0.1ms. The access time is in invers proportion to the storable data amount, which is always a performance issue for system and application designers.
The other performance question on database access is the data amount to be read and the size of database load. As we mentioned above the SAP systems is based on a single database system and many application servers. It gives immediately the way, how to optimize the data access: the required data should be brought up from the database level to the application server level for further processing. The database cursor should be opened for a time as short as possible. It means the data to be read, should be collected and transferred to the application server in one step, and not fetched the data row by row, keeping the connection alive. This performance recommendation comes into play when we create our own reports, but if we use SAP logical databases, we should think about another way.
III. ARCHITECTURE OF THE LOGICAL DATABASES There are three approaches to read data from the underlying database if do not deal with the buffering (mentioned above). The standard and detailed way is the use of OPEN SQL statements. In this case the database interface translates the SQL statement to a native call and manages the data according to the Dictionary settings.
There is another way to get data through native SQL statement. In this case the check mechanism of the database interface is bypassed and the real native SQL statements should be used. It is not recommended way, because the business logic, data check, and connection between tables are not handled, the program will be database dependent, and so we cannot ensure portability.
The third way is the use of logical database (LDB). The logical database as its name show also is not a real database, but it retrieves data records and makes them
available to ABAP programs and queries. A logical database provides special authority checks, selection screens pre-programmed for it. A logical database can manage many tables, where the relationship is coded in the LDB program, but the data is not changed in this program.
As we see, the logical database is a program that reads data from the real, underlying database and makes the data read available for other programs. To build up a logical database a hierarchical structure is defined to determine the order of reading from different data sources (e.g. from tables), examples on Fig. 2. The logical database programmer (in many cases SAP) determines the hierarchy. The hierarchy consists of nodes of different types. The easiest node type is the table, because it refers to a database table. In earlier SAP versions only this type was available. Another useful node type is the Dictionary structure. In this case the data is not read in the logical database program from a table, but a corresponding reading mechanism is coded to fill the structure (node) entries. SAP can define structures and table types in two ways in ABAP Dictionary: define as a dictionary object or through a type group. The type group (also known as type pool) is a programmed Dictionary object, where ABAP program type definitions are defined globally. In such type group structures and table types can be declared as well.
The third node type is the type groups itself, which is similar to the previous one, but the structure definition is not object-base, but coded. There is a special node type for dynamic programming. In this case the node do not have fixed type during the design time of the logical database, but they are classified during runtime.
Figure 2 Example structures of logical databases The simplest way to understand and follow the working of logical databases is, if we consider the nodes as real tables. The logical database cannot be used directly, but a report (or query) is needed to use the capabilities of LDB.
The logical database should be considered as an external program, which triggers event if it is used in a report. The report can have event handler blocks for these events. ABAP is an event driven programming environment. The runtime system calls the individual event processing blocks according to the predefined sequence, independently of the processing block position in the program. These blocks are not really modularization units in the ABAP environment, but they are called and the statements are executed sequentially if an event is triggered and a corresponding event block exists in the program. There are events, which are controlled by system program (like in the case of logical databases) or by the user. Some event block starting statements follows:
LOAD-OF-PROGRAM: this is the first event triggered by the system when the program is loaded to the work process memory.
INITIALIZATION: this system event is triggered to set initial values before displaying or executing anything.
START-OF-SELECTION: This is the default event block, so if no event block is declared in the report the whole code belongs to this event block. In a standard report it can be regarded as the main program.
AT SELECTION-SCREEN [OUTPUT]: there are more events started with this statement referring to different handling blocks of the selection screens. If the OUTPUT is added the event block content is executed before the selection screen is displayed. If it is not there the block will be called after a user event on the front-end.
AT LINE-SELECTION: this user event is triggered when the user selects a line on the result of the report. It is used for interactive lists.
The reports handle in the source code these event block statements, as highest levels and the content of the blocks are indent. The block order in the source is not relevant, because the runtime system searches for the corresponding event block, when an event triggered. If no relevant event block exists, the runtime system steps forward to the next event to trigger. Any executable ABAP program that has a logical database linked to it can contain event block references for each node of the logical database structure.
When the program is running, the corresponding event blocks are processed in the sequence prescribed by the hierarchical structure of the logical database. If a program linked to a logical database does not contain a event block for every node of a logical database, the processing passes through all the nodes that lie in the path from the root to the nodes specified by event blocks.
A logical database is defined through subcomponents:
the above-mentioned hierarchy or structure, selections and the database program. Each report can have selection screen definition. A selection screen is defined to provide an interface for the end-user to control the report run by parameters. The parameter values are used in the reports for filtering the data selection from the database tables and restrict the amount of data read. The selection screen is a special dynpro type, which is defined via ABAP statement and used only for reports. The statements define the content of the selection screen and during program load generation the system generates the selection screen dynpro from the statements. There are different statements to define single input fields, multi selection criteria, and other screen elements (like push button, empty line, texts, positioning, etc.). As we described in the event statement examples, some special events are available to set default values, screen field behaviors, and control or check input values. The statements are the following:
PARAMETERS: determining single values (elementary variables)
SELECT-OPTIONS: complex selections
SELECTION-SCREEN: formatting the selection screens (e.g. positioning) and defining user defined additional selection screen
CALL SELECTION-SCREEN: call up a user selection screen defined in frame of SELCTION-SCREEN statements.
The statement SELECT-OPTIONS can be use to define multi line criteria on the screen. The selection criteria are based on special internal tables. These selection tables have a predefined structure in the system to handle multi line criterion in a standard way. The statement itself always refers to a field declared in the report to define the structure (type) of the field, which the restrictions are given for. As an example we can consider the main customer master table NKA1, where the customer number is stored in the field KUNNR. In the SELECT-OPTIONS statement a reference to KUNNR defines the selection tables structure, because the restriction values must have the same type as we use for restriction. Multi selection criteria means, that not only one value can be given, but more entries are available also (that’s why SAP uses internal table to store the values). Values can be exact ones, but patterns (using wild chars) or even ranges can be used for restrictions. The selection table can be used later in the program for real selection, filtering. Main purpose of them is to directly translate the selection criteria into database selections using the WHERE addition in Open SQL statements. The database interface translates the selection criteria to Native SQL call depending on the content of the selection table. With these capability dynamic selection can be used in ABAP reports.
The selection screen, that we define when we use the PARAMETERS or SELECT-OPTIONS statements on their own, has a standard layout or format in which all parameters appear line by line. This layout is not always sufficient or additional screen elements are needed, like a group of radio buttons, push buttons, etc. The SELECTION-SCREEN statement has its own formatting and screen element definition options that we can use to define the layout for selection screens. The screen elements can be grouped together for formatting purposes like frame definition or positioning them into a single line. On the other hand the grouping can be used for input handling as well. There is an option to group screen elements and layout setting into user defined selection screens. These are not called directly by the runtime system, as the default one (number as 1000), but the program should call it explicitly by the CALL SELECTION-SCREEN statement. The input checks and settings of default values of screen elements are processed in special event blocks (AT SELECTION-SCREEN…).
The selections in a logical database are defined using the normal statements (listed above) for defining selection screens. In a logical database, we can also use the additions VALUE-REQUEST and HELP-REQUEST to define specific input and value help. We define the selection screen of a logical database in a special include program known as the selection include. The selection screen definition for logical databases uses the same statements, but there are references to the nodes (FOR <node> closure).
The actual appearance of a selection screen therefore can depend on our program. With FIELD SELECTION FOR
<node> extension we can enable a field selection for the individual nodes in the logical database program. Some logical databases even note dynamic selections for individual nodes. With this option the end-user can determine which selection fields should be set.
When we write programs using a logical database, we can also add our own selections. The standard selection screen then contains the database-specific selections, followed by the program-specific selections that we have defined. The database-specific selections depend on the nodes of the used logical database required for the program. For details see the following code snippet referring to the logical database CKM mentioned on Figure 2.
PARAMETERS:
KLIDCOPY LIKE KALA-KALAID FOR TABLE MACKW VALUE-REQUEST,
KLDTCOPY LIKE KEKO-KALADAT FOR TABLE MACKW VALUE-REQUEST.
…
* Enable DYNAMIC SELECTIONS for selected tables:
SELECTION-SCREEN DYNAMIC SELECTIONS FOR TABLE MARA.
SELECTION-SCREEN DYNAMIC SELECTIONS FOR TABLE MACKW.
* Enable FIELD SELECTION for selected tables:
SELECTION-SCREEN FIELD SELECTION FOR TABLE MARA.
SELECTION-SCREEN FIELD SELECTION FOR TABLE MACKW.
The first part of the selection shows some fields, which depend on the using of MACKW node in the report referring to the logical database. The second part is a special addition to the logical databases, which cannot be used in other areas. If there is any entry containing the SELECTION-SCREEN DYNAMIC SELECTIONS FOR NODE extended statement for a table (node), the report requesting the logical database will offer a special functionality (via a pushbutton on the generated selection screen) to define dynamic selections to improve performance of data reading. The last code portion shows another step to create better performing logical databases.
This statement feature gives the opportunity to read from the database lines not all the fields, but only those, which are needed by the report. (Details come later.)
The third, main sub-object of a logical database is the database program itself. This program is not a directly executable code, but essentially it contains a number of form routines (subroutines in ABAP), which the system calls at runtime according to the declaration of the report program.
IV. WORKING WITH LOGICAL DATABASES A logical database program contains at least the following form routines.
FORM INIT: Called once before the selection screen is displayed. This is the logical database initialization phase, where the authority check, reading of customizing, defaults settings take place.
FORM PBO: This is the subroutine for processing the Process Before Output event for the selection screen of the logical database. Called each time before the selection screen is refreshed.
FORM PAI: This subroutine is defined for the Process After Input event of the selection screen. A similar form handles the input checks, the value and help-request. Called each time the user presses a key ordered to a function code on the selection screen.
FORM PUT_<node>: For each node defined in the structure of the logical database there is a form routine declared. These subroutines are called in the order determined by the structure of the logical database. The data of the node <node> is read using SELECT statements (or other technique according to the node type defined) and an event is triggered to redirect the program flow to the report, where the corresponding event block is executed. The data retrieved for the node entry is passed to the event block for further data processing.
The main points of a logical database program are the PUT_<node> forms. The <node> must exist in the structure hierarchy, and the database program reads (selects) the data from the database table (if the node is defined as a table type) and row-by-row gives the data to the using report. The PUT statement, as an event, directs the program flow to the appropriate GET statement in the executable program (report) delivering the data record read (see the code portion below from the CKM standard logical database).
FORM PUT_MARA.
…
*** Declarations for field selection for node MARA ***
STATICS MARA_FIELDS TYPE RSFS_TAB_FIELDS.
MOVE 'MARA' TO MARA_FIELDS-TABLENAME.
READ TABLE SELECT_FIELDS
WITH KEY MARA_FIELDS-TABLENAME INTO MARA_FIELDS.
…
SELECT (MARA_FIELDS-FIELDS)
INTO CORRESPONDING FIELDS OF MARA FROM MARA
WHERE MATNR IN CK_MATNR AND DISST IN CK_DISST AND MTART IN CK_MTART AND (MARA_WHERE-WHERE_TAB).
PUT MARA.
ENDSELECT.
…
ENDFORM. "PUT_MARA The GET statement refers always to a node defined at the beginning of the report in the declaration part by NODES statement. The GET statement starts an event block containing the execution for the record of the corresponding node. According to the hierarchy structure after executing the corresponding GET event block the runtime system gives forward the control to the subroutine (PUT_<subordinate_node>) for the next node. If the using report defined a corresponding GET event block for the lower level node it will be executed either. If there is no reference to the underlying nodes the control is given toward to the next sibling node. Before leaving a node level, or better to say before reading the next record from a node the runtime system directs the control to the corresponding GET <node> LATE event block. This event block can be used for sum or data aggregation from the data of the subordinate nodes. If we follow this tree traversal it is working as a pre-order traversal. There can be for all nodes a GET <node> and a GET <node> LATE event block, but for the nodes, but for leaf nodes the two event block contents are executed immediately after each other.
As we have seen, when the report attributes contain logical database reference, the report will be executed by
logical database program will run “parallel”. There are two more event blocks in connection with logical databases: START-OF-SELECTION and END-OF-SELECTION. Both statements are event block starting commands, and not the first and last statements of a block. The first block is executed before starting the database selection through the hierarchy of the logical database. The second one will be executed after the last record requested by the caller report is processed in the report. These blocks give a frame around the logical database usage.
Our review referred to the report usage of the logical databases, but there are other options as well. SAP delivers the ABAP Query a design based reporting tool using almost zero programming. The Query can use as data source single tables, joined tables, programmed data source or logical databases. Applying logical databases as data source for ABAP Queries are very useful and beloved solution; because no preparation is needed to define the data source and the authorization check are implemented into the logical database program.
As we discovered the logical databases can be used with full power in report programs, but there are many other cases, where it could be useful. SAP realized this requirement and as of release 4.5A offers a function module (LDB_PROCESS) to help other kind of programs to use the capabilities of logical databases. The difference is that here we do not have to define event block in a report program, but so-called call back forms should be defined to call by the function module when the control would be given to the using program (e.g. by PUT event). The call back forms are declared to the function module via an internal table containing entries for nodes of the logical database hierarchy. If the caller uses a node a corresponding entry is stored in the internal table containing the name of the node itself, and the form name, the program name containing the form, and the handling behavior. A form can handle not only the GET event, but the GETLATE events as well, retrieving an event parameter.
As we described above the logical database usually have selections to restrict the data to be read. If the logical database is not used by a report, but via the function module, the selection screen is not displayed, but selection criteria can be given via internal table.
There some restriction on using logical databases for reports, e.g. there is only one place to define the used logical database. If we want to use more than one logical database parallel we have to use the function module to define separate forms for required nodes of different logical databases.
V. PERFORMANCE ISSUES
As we described at the beginning of our study there are different database reading techniques within the SAP ABAP language. Using logical databases we can immediately explorer that the hierarchy uses nested loops.
The CKM standard logical database contains only two Dictionary tables (views). Fig. 3 shows the LDB and a nested loop solution for the same task.
Figure 3 Logical database and nested loop The early logical databases read full data lines, which make high load to the database and the network without using the whole data amount retrieved to the application server layer. The newer LDBs allow using field selection, which means that the caller report can define which fields are needed from the report and should be transferred from the database to the application program. As we mentioned in earlier section the selection part of the logical database should have special entries (SELECTION-SCREEN FIELD SELECTION FOR <node>) to let the caller to select the required field of a requested node. The system will filter for columns only if the caller report contains field enumeration in the GET <node> statement using the FIELDS <field1>..<fieldn> closure. The same can be used for GET LATE statements.
If dynamic selections are used or rather default values or values list are applied the performance can be better, because the data to be read are further restricted.
We did not write too much about the authorizations, but the executed early enough authorization check can improve the performance, because less data is read if no authorization is given for a node. There are authority check forms for nodes as well, which are executed before the PUT_<node> forms are invoked.
A report using logical database cannot request a node directly in the middle of the hierarchy tree, but all nodes above it should be requested using the NODES statement as well. It means each node will be selected from the database independently whether the corresponding GET statement appears in the program or not. The logical database program can be improved by using a special internal table called GET_EVENTS to have a list from the nodes really requested in the caller program. With these tools a comprehensive database access technique is in our hand to create reports (and programs if the function module is used) without thinking about the data reading, but only dealing only with the task to work with the data retrieved.
The logical database technique is a robust and well- defined tool to make good performing reusable database access surface for reports and other programming tools, like object-oriented methods or transaction programs. The technique is not obsolete, many standard and customer designed reports are still using logical database. The best example for that are the HCM (Human Capital Management) PA (Personal Administration) and PD (Personal Development) reports delivered and created at customer side.
VI. CONCLUSION
In our research we get deep information on the idea and capability, lying behind the logical database programming. From our perspective the main focus were the usability and effectiveness of LDB, and whether it could be an advisable method of database access from methodology point of view. Logical databases can save us having to program robust selection screens and read data from the database in each executable program. The program does not have to specify how to retrieve the data, but instead only has to process it and display it on the output screen. The selection screens of reports using the same logical database look similar to help the end-user to fill out the fields and execute the report in a standard manner.
As we mentioned, an executable program can always only work with one logical database, but several executable programs can use each logical database. This offers considerable advantages as reusability compared with the method of integrating the database accesses into the individual executable codes using SELECT statements (see Fig. 3 above). It means that we only have to code identical access paths once and in many cases SAP did it already for us by delivering more than 200 logical databases. If we think about authorization checks written in reports, we realize that in many codes using similar data areas the authorization checks are the same. We do not have to program these check for each program, because it is programmed once in the logical database program.
When we use logical databases, most executable programs benefit from having
An easy-to-use and standard user interface with the central selection definition in the logical database definition
Verification methods which check user input whether they are complete, correct, and plausible
Meaningful data selection
Well-defined and centralized authorization checks for each node of the database before accessing the data itself
Good performance in reading (e.g. views are used) while retaining the hierarchical data view determined by the application logic.
The executable programs using logical databases remain flexible, because the request of nodes to be used are in our hand, selection screens for each program can be extended by our own fields and criteria, and the event block can contain any code we want to execute on data reading. If further database access, authorization checks or user dialogs are needed, we can freely use them in our reports.
As we discovered the SAP logical database looks old fashion programming style, but it has several today also useful capability to find its way to apply them in daily use.
REFERENCES
[1] Igor Barbaric, “Design Patterns in Object-Oriented ABAP”
Galileo Press Bonn-Boston, 2010
[2] Rich Heilman, Thomas Jung, “Next Generation ABAP Development” Galileo Press Bonn-Boston, 2007
[3] Rita Ősz, “Competencies in the Process of Computer Based Learning”, 4th International Conference of PHD Students, University of Miskolc, 2003.
[4] Lívia Róka-Madarász, “Performance Measurement for Maintenance Management of Real Estate”, Acta Polytechnica Hungarica Vol. 8, No. 1, 2011.
[5] Selmeci, A., Orosz, I., Orosz, T., “SAP BAPI as a break-through and future communication enabler”, 2011. Dec. AIS2011
[6] Hermann Gahm, “ABAP Performance Tuning” Galileo Press Bonn-Boston, 2007
[7] Stephan Kaleske, “SAP Query Reporting — Practical Guide”
Galileo Press Bonn-Boston ISBN: 978-1-59229-365-0
