📚 SAP ABAP Data Dictionary

The transaction of the database dictionary is SE11. It is a central repository to create objects and to store the data. An advantage of a data dictionary is reusability - once an object is developed in a data dictionary it can be used in any program throughout the SAP.

Creating Database Tables

Radio Button 1: Database Table Overview

Database table is a combination of structure and table data (table structure consists of field name and table data will store the business data like master and transaction data) in which we can display, Create, and Update the changes in the table.

Step-by-Step Table Creation Process

Let's create a table zcustomer_tabl table.

Step 1: Table Name Creation

Write the name of the table that you want to create in the box. If that name is already taken by another table write another name, and click on create button.

Step 2: Short Description

We need to write a short description first (any meaningful description regarding tables like customer information).

Step 3: Delivery Class Configuration

Then we need to provide a delivery class in the delivery and maintenance tab. It is a tick mark field which means it is a mandatory field we need to provide value here otherwise it will throw an error (compulsory otherwise we cannot proceed further).

Select the search help near the box or press F4. It will display the search help bar. We have 7 types of delivery classes:

• A - Application table (master and transaction table - 99% we will work with master and transaction data)
• C - Customizing table (we can make direct changes only in customizing server only not in the production server)
• L - Used by application programmers
• G, E, S, W - Used by SAP only

 
Understanding Data Types

Master Data - It is only created once in the database (e.g., Bank Master).

Transaction Data - Which is created frequently or changes frequently (e.g., deposit, withdraw).

Delivery Class - It is used to control the Transport of the table data. It is used to specify the type of the table.

• In Real time we mostly use the delivery class 'A'
• In the case of delivery class 'A' the table can maintained in any system (development, quality, production systems) using SM30

Data Browser/Table View Maintenance

This property is used to specify whether the table can be maintained or not. In real time we mostly use "Display/maintenance allowed" option.

It is of 3 types:

1. Display/Maintenance allowed with Restrictions - We can only Display the data. Create, or change is not possible
2. Display/Maintenance allowed - We can Display, Create, Change directly
3. Display/Maintenance not allowed - We cannot create, display, or change the data directly

Step 4: MANDT Field Configuration

In the fields tab while creating a table in SAP, the first field always be MANDT and it is always the primary key, it is a non-transportation object.

MANDT - Using mandt we can restrict the data across the client (dev and unit test system). Whatever data we have entered in the development server should not be there in another system like a unit test server, that is why we have to use the mandt field.

Step 5: Primary Key Definition

Then write your all fields in a single table. Min. 1 primary key is mandatory and max. 16 primary keys allowed, tick key button, and initial checkbox.

• The key checkbox will tell us that the field is a primary key
• An initial checkbox will tell us that the column cannot be NULL

Step 6: Field Definition

Then write the field name that you want to insert into the table. We can give the data type and field description directly but we will lose the features like search help and value range which are given through data element and domain level only.

Step 7: Technical Settings

After defining the data type with their corresponding fields we need to give a technical setting that consists of data class and size category.

Data Class - Is used to specify the type of the data to be maintained in the table. Data class provides what type of table it is:

• APPL0 (master data class) - The data which is not changing frequently (e.g., DATE OF BIRTH)
• APPL1 (transaction data class) - The data which is changing frequently (e.g., SALARY)
• APPL2 (organizational data class) - The data which is changing rarely (e.g., COMPANY CODE)

In Real time we mostly use APPL0 or APPL1.

Size Category - Size category ranges from 0 to 8. It determines the space required for the table.

• It is used to specify the initial space required for the table in the database
• In real time we mostly use '0' category (0 – 8) -> [0 – 2700]
• 0 means e.g., 8kb it will reserve 8kb of space in the main memory

Step 8: Currency and Weight Fields

If you want to add any currency or weight field you need to define the reference table and reference field. Because every Country has different measuring units that is why SAP has made it mandatory.

Activate it.

Enhancement Category

EXTRAS -> ENHANCEMENT CATEGORY

Enhancement category is used to enhance the particular fields of the table. In Real time we mostly use Can be enhanced (DEEP).

• If we want to add any fields in the future we have to choose can be enhanced
• If we don't want to add any fields then cannot be enhanced
• If not sure then select not classified

Foreign Key Relationships

FOREIGN KEY - Select the row in which you want to add a foreign key with the parent table click on the key icon. It will ask for a check table which will be the parent table that gives the name of a parent table.

Click on the proposal it will give you the field name in which the foreign key can be maintained. Select on that field click on ok. That field will be added as the foreign key. Parent table is called check table and we can maintain foreign key with its primary key and child table column but data element must be same to apply foreign key in the child table.

It gives data element level field validation.

Cardinality Ratio = Single record of a header table corresponds to multiple records of a Secondary table.

Text Tables

Text Table – These are Tables That hold the Description of the Parent Table.

Creating Text Tables

Step 1: Create A Parent Table

Step 2: Create a text table

2nd key always be langu in the text table.

Step 3: Create a foreign key relationship

• Mark 1st row and select foreign key button
• Mark 2nd row which is langu and select foreign key button
• Mark the 3rd row select the foreign key button and give the parent table name in the check table field
• Mark the radio button Key fields of a text table
• Cardinality 1:N
• Create a proposal and click on copy

Text table is now created.

Step 4: Check the text table in the parent table

It will show text table was created or not.

Index Tables

INDEX TABLE - To improve the performance of select queries. Suppose, your query is taking too much time to execute then to resolve that issue we need to create an index table. E.g., bseg, mseg, lips etc.

We create index tables in very rare cases, especially in the ECC system. Not necessarily in the Hana system because the Hana system is very fast.

Advantages

• It gives high performance
• It stores the data always in sorted order

Disadvantages

• It occupies space from the table in which the index table is being created which will reduce the performance of the database table, which is why it is recommended to create an index table very rarely

Types of Index

Primary Index

Primary Key will act as Primary Index. We can create Primary Key for custom Table only. SAP automatically creates a primary key index implicitly, when we activate our database tables.

When we search the record in any tables based on the primary key it always gives high performance.

Secondary Index

These are created and maintained by the Technical Consultants/ABAPer. Based upon our requirement, we will create a secondary Index. Maximum 9 Secondary Index we can create in a table. Secondary Index is possible for both Standard and Custom Tables.

Creating Index Tables

1. STEP 1: CLICK IN INDEX TAB
2. Step 2: Click on create button then create index
3. Step 3: Give 3 digit Index Name Starting with Z and click Continue
4. Step 4: Give a Short Description and field Names that you want to use in your program
5. Step 5: Give Field Names and Activate your index table

Buffering

Overview

Buffering is a temporary storage on the application layer. There are 3 layers in SAP:

1. Presentation Layer – In which we interact with SAP system
2. Application layer – In which we write our programs
3. Database layer – In which tables data are residing there (it is a permanent storage)

It improves the performance when accessing the data records contained in the table. Sometimes, we need to access a particular set of records more frequently than others. Accessing Records Directly from a database table can be very time-consuming.

Buffering Options

1. Buffering not allowed – Table buffering Is not performed for the table
2. Buffering Switched on – Table Buffering is Performed for the table
3. Buffering allowed, but switched off - Buffer is allowed but It is switched off and can be switched on anytime according to the requirement of the customer

Types of Buffering

1. Single Record Buffering

In the case of single record buffering single record load in the buffer at a time.

When to use: Single record Buffering should be used for tables where single records are accessed by specifying the complete Key.

Advantages:

• This type of Buffering requires less memory on the application layer

Disadvantages:

• There is no Significant reduction in the number of database access

2. Full Record Buffering

In the case of Full Record Buffering, The Full Records of a table load in the buffer.

When to use: Tables that are best suited for full record buffering are frequently accessed and rarely changed. Tables that contain transaction data should not go for full record buffering.

Advantages:

• There is a Significant reduction in no. of database access

Disadvantages:

• This type of buffering requires more space on the application layer

3. Generic Area Buffering

The records that match the generic key are loaded in the buffer. The best example of generic area buffering is language-dependent tables.

In the case of Generic Area Buffering we need to pass the number of key fields. The number of key fields for a generic area buffering is less than the primary key of a table.

Important Points in Buffering

1. If we don't need buffering select checkbox buffering allowed but switched off
2. If we want to reset the buffer of a particular table never use t-code /$TAB. This t-code will reset the buffer of all tables not a single table
3. We can pass the table in t-code AL12 to reset the buffer of a particular table

Table Maintenance Generator (TMG)

Table Maintenance Generator - It is used to maintain multiple entries & validate the table data using the T-code SM30.

In real-time users cannot access SE11 code. Using a tool called TMG users can perform data entry (DML operation - insert, update, delete). In TMG, Users can perform operations to a particular table plus they will be restricted to a particular table only.

Steps for Working with TMG

1. Execute SE11
2. Select the Radio button DB Table
3. Provide the Table name & click on change
4. Click on utilities
5. Click on TMG
6. Select the auth. group as &NC& and Provide the function group as your table name
7. Select the one-step radio button
8. Enter the overview screen number (could be any). [4 digit only]
9. Click on the create icon(F6) and click on save
10. Press the enter button & Click on Local Object or TR and click on Back
11. Test TMG through SM30
12. Create t-code in SE93 (same name as table name)

Maintenance Types

• One-step - Data entry (multiple records) if our table has fewer fields then select one step
• Two-step - Data entry (record by record) if our table has more fields then select two steps

Recording Routine

• Standard recording routine - It will ask for transport requests
• No, or user, recording routine - It will not ask for transport requests

Events in TMG

Validation can be done by Table events using SM30. Totally 38 events are there in TMG.

Mostly we use these 5 events in real life:

1. Before saving the data in the database
2. After saving the data in the database
3. Before deleting the data display
4. After deleting the data display
5. Creating a new entry

Important Points in TMG

1. To generate the TMG, data browser/table view maintenance options need to be - display/maintenance allowed
2. Whenever we add new columns/fields to the table after the generation of TMG, the newly added fields do not appear automatically while maintaining data with the help of SM30. We need to delete the TMG and generate it again
3. To delete the TMG, open the table in change mode, otherwise delete button does not appear

Database Utility

Database Utility acts as an Interface between the ABAP dictionary and the database.

We can call the database utility from the initial screen of the ABAP dictionary with utilities -> Database utility (T-Code: SE14).

Purpose of Database Utility

1. To check the default value of table fields
2. To adjust the table data

Important point: Do not perform any database operation on the table while performing the database utility. If you do that it will delete the data from the table.

Data Browser

The t-code for data browser is SE16. Through data browser we can view the data of a table. From SE11 we can navigate SE16 data browser. The t-code for new data browser is SE16N.

Types of Database Tables

There are 3 types of database tables:

1. Transparent Table

In case of transparent table, there is a relationship between the ABAP dictionary and the database.

2. Pooled Table

In the case of a pooled table, there is an N:1(many to one) relationship between the ABAP dictionary and the database. For pooled tables, a primary-foreign key relationship is not required.

Table pool structure: Tabname Varkey Dataln Vardata

3. Cluster Table

In case of cluster table, there is N:1(many to one) relationship between the ABAP dictionary and the database. For cluster tables, primary-foreign key relationship is mandatory.

Table cluster structure: Key Pageno Vardata

Views

Views - Views is a virtual table. View does not have any data. It is bringing the data from a table. View is a logical database because it has no data while table is a physical database it has its own data.

The View is a virtual table that gets the field and data from other tables.

Table vs View Comparison

Table:

• Having fields and we can store data
• A table has a technical setting
• In a table, we can store 1 kind of data either master or transaction
• Additional resources are required to maintain tables like technical setting, data class, size category that is why they have poor performance compared to views

View:

• In view, we get the field and data from other table
• Views don't have technical settings
• In views we can view both master and transaction data simultaneously
• No additional Resources are required (Good Performance)

Types of Views

1. Database View

It is created based on 1 or more than 1 table using join condition. Tables Should have relationship between to create Database View.

In the database view, the maintenance status is read-only which means we can not change the data we can see only in the database we can put selection conditions.

2. Projection View

Projection view only creates with a single table called a basis table. Suppose if a table has 200 columns and we want to show only 5 columns then we will use projection view.

Maintenance status is always in change and display mode.

3. Maintenance View

It is also known as a Table maintenance generator. Its purpose is to maintain multiple table. The tables must have relationship between them. Take mandt field in the maintenance view.

4. Help View

It is also called search help. The tables must have relationship between them.

Important Points: Not more than 1 record should exist in the dependent/Secondary table.