TAW10 - DATA MODELING AND DATA RETRIEVAL

DATA MODELING

A business unit is represented as an entity. An entity relationship model (ERM) is used to map the relationships that these entities have with each other. This data model is the basis for implementing appropriate table definitions (transparent tables), including their relationships with each other, in the ABAP Dictionary. The corresponding database tables are automatically created in the database with the activation of the table definitions.


FLIGHT DATA MODEL FOR ABAP TRAINING COURSE

The package SAPBC_DATAMODEL contains the Repository objects for the flight data model used in ABAP training courses. A customer of a travel agency might enquire about suitable airports, flight connections, timings, and so on.


RELATIONAL DATA MODEL

Entities logically connect business information. The flight data model comprises the entities that logically connect flight information:


IMPLEMENTATION USING TRANSPARANT TABLES

The developer creates a platform-independent description of a database table, known as transparent table in the ABAP Dictionary, for each entity fixed in the data model. A transparent table allows you to store and manage data records in a structured way. Table fields – declared as key fields – help in the unique identification of data records within the database table. The key of a table (table key or primary key) consists of key fields.


TECHNICAL STRUCTURE OF A TRANSPARENT TABLE

A transparent table defined in the ABAP dictionary contains the actual application data. A table of same name is automatically created on the database when the transparent table is activated. The fields of the transparent table form the identically-named columns of the corresponding database table. The data elements are normally used to describe the individual fields, which then refer to domains for their technical properties.



TRANSPARENT TABLES IN ABAP DICTIONARY

For creating a table of the same name on the database, the transparent table, in addition to the list of fields, contains other information:

  The determination of the key for the database table (key fields)

  The technical properties required to create the database table, such as expected size and expected frequency of access

  Settings for technologies to speed up the access to database table, such as secondary indexes and buffering 


STRUCTURES IN THE ABAP DICTIONARY

Transparent tables can be used in the same way as structure types. They can, for instance, be used for defining a structured data object (structure variable); for typing an interface parameter; or as the line type of a global or local table type. The list of fields is of major importance, whereas properties such as the key definition or the technical properties are irrelevant.

TRANSPARENT TABLES AS AN ABAP DATA TYPE

Transparent tables were used as data types in older programs. Nowadays, the advice is to use them directly only in connection with access to the database.

A transparent table is always a list of elementary fields. In contrast, the components of a structure type can themselves be structured again (nested structures). If the component of a structure is typed with a table type, it becomes a deep structure.

DATABASE ACCESS (ARCHITECTURE)

Structured Query Language or SQL enables you to define, change, and read access to database tables.

Let us now understand the difference between native SQL and Open SQL.

Every relational database system has a native SQL, which is database-specific. It cannot be used in all SAP systems, because different SAP systems may have different database systems.

In contrast, open SQL is an SAP-defined, database-independent SQL, standard for the ABAP language. Open SQL statements are dynamically converted to the corresponding native SQL statements of the database system in use. Thus, open SQL allows uniform access to data, regardless of the database system installed.

SEARCHING FOR DATABASE TABLES

There are different ways of searching in a database table:

An application-specific search is indicated if the search is within a particular component, such as an Application Hierarchy.

A program-specific search relates to a search that uses a program, which can access the relevant database table.

This search involves three steps:

  The source code search tries to locate the specific code in the program, such as the SELECT statement in the program editor.

  Function debugging is carried out, to set a break point at the SELECT statement. Before executing a program with this kind of search, you must change to debugging mode using /h.

  Using the screen field information, you can display a structure field by F1 and then display the technical information. You can query a Where-Used List in Table Fields after navigating to the relevant data element.


REUSE COMPONENTS FOR DATA RETRIEVAL

Four types of reuse components encapsulate database accesses:

  Logical databases, in which data retrieval programs belong together hierarchically

  Function modules, which comprise encapsulated functions, such as reading from hierarchically related tables

  BAPIs, which act as methods of Business Objects with, for example, read function

  Methods of global classes



DATABASE READ ACCESS (OVERVIEW)

The SELECT statement in the Open SQL allows for read access from a program database, which will fetch or read the required data.

The SELECT statement contains a series of clauses, each of which has a different task:

  SELECT describes which fields of the table are to be read.

  FROM names the source (database table or view) from which the data is to be selected.

  INTO determines the target variable, into which the selected data is to be placed. The target area must be structured like the table row or like the specified fields of the row. In other words, it has to be left-justified.

  WHERE specifies the rows of the table that are to be selected.


READING SINGLE RECORDS

You can use a SELECT SINGLE statement for retrieving a single record. All key fields must be filled in the WHERE clause, to ensure unique access. The current client is taken into consideration if the client field is not specified. You can use an asterisk (*), to specify that all the fields of the table row are to be selected.

If the system finds a suitable record, the return value sy-subrc equals 0.

CORRESPONDING TARGET STRUCTURE FOR THE FIELD LIST

You can retrieve a certain selection of fields from the table row by specifying a field list within the SELECT statement.

In the INTO clause, you must then name a target structure variable that has the same structure as the field list. The names in the target structure do not matter. It should contain the fields of the field list in the same order. Only the corresponding field types match.

Alternatively, you can list the corresponding target fields in the INTO clause:INTO (field_1, …, field_n).

FIELDS IN THE TARGET STRUCTURE WITH THE SAME NAMES AS IN THE FIELD LIST

You can use the CORRESPONDING FIELDS OF addition for retrieving the records in a structure variable that has fields with the same names as those in the target list, but with a different structure, such as additional fields or a different order of fields. The effect is that only fields of the same name are filled in the target area.


READING SEVERAL ROWS USING A LOOP

The SELECT–ENDSELECT loop can be used to read several rows of a database table into the program in succession.

The lines to be read into the target structure – and processed using the statement block specified in the loop body – are determined by the WHERE clause. Multiple logical conditions can also be defined within the WHERE clause, by using AND or OR.

The database delivers the data to the Database Interface of the application server in packages, after which the specified processing block copies the records to the target area, row by row, for processing.

You should query the return value after the SELECT–ENDSELECT statement by usingsy-subrc. The sy-dbcnt contains the number of records read.

READING SEVERAL ROWS USING AN ARRAY FETCH

Instead of copying the selected part of the database into an internal table row by row, you can use the INTO TABLE clause to copy it directly. This technique is called an array fetch.

The ENDSELECT statement is neither required nor allowed, as array fetch is not a type of loop processing.

The internal table that is specified as the target in the array fetch must be structured left-justified in the same way as the field list.

READING DATA FROM CLIENT-SPECIFIC TABLES

A database table is called client-specific, if it has a client field (data type CLNT) as the first key column and contains client-specific entries.

If you select data from client-specific tables without specifying the client, then only data records from the current client are read. This is because the current client is transferred to the database system, where the data retrieval takes place.

To read data from an explicitly specified client, you specify this client in the WHERE clause, and use the CLIENT SPECIFIED addition after the FROM clause.

ACCESS THROUGH KEY FIELDS

The data records are managed within a database table, which is based on the content of the key fields. If access to the table is restricted to all the key fields, or at least the first few, then the required data records can be accessed very quickly and effectively.

ACCESS THROUGH NON-KEYFIELDS

If the fields to be accessed do not belong to the table key (non-key fields), then the internal ordering principle cannot be used for rapid access. In the worst-case scenario, the entire table – or at least a very large part of it – may be searched for the required entries. This type of sequential search can increase the waiting time for database access.


ACCESS THROUGH SECONDARY INDEX

If a database table is frequently accessed using a certain selection, a secondary index is defined for the fields contained in the selection. This speeds up the corresponding accesses.

A secondary index is a separate, small table that contains the content of the index fields, as well as a reference to the relevant data records in the actual database table. The required fields should match the fields in the secondary index, at least by being left-aligned and gap-free. This prerequisite enables the database to first search within the secondary index, and then read the corresponding data records through the reference.

You can create a secondary index in the ABAP Dictionary by choosing Indexes and then activating it. The index is not manually specified in the SELECT statement.

ACCESS THROUGH THE SAP TABLE BUFFER

You can reduce runtime by buffering the data on the application server. The database interface first tries to get the required data from the SAP Table Buffer, thus speeding up the access by a factor of 10 to 100. This is much faster than reading data from the database, particularly if the data is needed frequently but seldom changed.

If the required data is not yet in the SAP Table Buffer, then the entire database is accessed.

The read data is then transferred from the database interface and stored in the SAP Table Buffer.

EXAMPLE OF A TABLE JOIN

You can use the JOIN statement in the SELECT query to select fields from more than one table.

ENTRIES FOR DEFINING A TABLE JOIN

When defining a Table Join, you first decide three criteria:

  Join Tables, which specifies which tables should be accessed

  Join Condition, which determines the condition under which the relevant records from the join tables are summarized to a join record

  Join Columns, which identifies the columns from the join tables that should be available in the table join

IMPLEMENTATION OPTIONS FOR TABLE JOINS

You can use two options for implementing a table join:

  You can create a database view in the ABAP Dictionary, which corresponds to a table join, and select it in your program.

  You can select from a join that is defined in your program (ABAP join).



DATABASE CHANGE ACCESSES

In addition to the SELECT statement, Open SQL also contains change accesses, such as the UPDATE, INSERT, DELETE, and MODIFY statements.


SAP AUTHORIZATION CONCEPT

Authorization objects can be defined within object classes.

This authorization can be integrated into the required user master records by means of an authorization profile.

Several different authorizations can be created for an authorization object.

AUTHORIZATION OBJECTS AND AUTHORIZATIONS (EXAMPLE)

You specify appropriate fields without values while defining an authorization object.

To create an actual authorization, you subsequently assign values to these fields.

AUTHORIZATION CHECK (PRINCIPLE)

You use AUTHORITY-CHECK statements to check whether the current users have the authorization required, in their user master records, for executing the function. You use the system field sy-subrc to validate the current user. If the return value is 0, then the user is authorized to view the data. Otherwise the user receives an error message.

In addition, you can also protect programs and transactions by means of authorization checks.

DISPLAYING AUTHORIZATION OBJECT

To use the required authorization check in your program, you must first define the structure of the respective authorization concept.

An object usually consists of the ACTVT (activity) field and one other field, which specifies the data type to be protected. The values of these authorization fields specify what the user is authorized to do.

AUTHORIZATION CHECK (SYNTAX EXAMPLE)

You can use the AUTHORITY-CHECK statement to specify the authorization object, its fields, as well as the appropriate field values. You also check the return code sy-subrc, to control the subsequent processing of your program.

In the example, we check the authorization of the user for the S_CARRID object.

This object has the CARRID field; while the ACTVT field has 03 as its value. This check is performed against the master record of the current object.

IMPLEMENTING AUTHORIZATION CHECK IN PROGRAMS

The AUTHORITY-CHECK statement can be generated into your source code by choosing Pattern. This way you can avoid spelling errors in object and field names. Thereafter, you can maintain the field values and implement the sy-subrc check.