ABAP Trial Version for Newbies Part 14: The Founda...

thomas_weiss · ‎06-11-2007

This weblog is about building and managing the basics of any business application: The database table(s). With the ABAP declarative approach of the Data Dictionary these things are so easy that you can concentrate on your data model and leave all database vendor specific details to the system. From a technical point of view what I will explain is easier to understand than the SAP Change and Transport Management. Still this weblog is hard stuff because it contains a lot of details and it is has become longer than I planned. But I saw no good way to cut the material into different parts. In addition to the technical information I will explain the concept of a client in the SAP world since we will build a client-specific table.

When creating a table on the Application Server ABAP all you have to do is to define the metadata of a table, including foreign key relationships and check tables, search helps and fixed values. Just activate the relevant object (s) in the Data Dictionary (transaction SE11) and the table is created on the underlying database of the system. No need for any code as far as the data definition on the database is concerned. No need either to care about vendor specific technical details. Your database tables will be created on any database that is supported by the Application Server ABAP if this is the database of your system.

We will implement a simple data model almost from scratch in this web. You will learn in some depth the basics of

How to define a database table

How to define a foreign key relationship and thereby

How to provide a check table for the relevant column in the foreign key table.

How to create Data Elements and Domains

How to create Fixed Values in the Domain.

You perform all these things in different screens of the Data Dictionary. This way you will get to know how to manage database tables in a metadata-driven approach that is typical of developing on the Application Server ABAP. Our central table will be a table of accounts that is related to some other auxiliary or check tables. So I will give some substance to the claim of how easy database table handling is on the AS ABAP.

Another weblog will show you how to fill this account table with data very easily by inputting some data into auxiliary tables in the Data Browser (SE16) and then using these data in a program to create random entries for the account table.

The Contents of this Weblog in Some Detail

We want to create a table YACCOUNT that contains accounts and we want to use this table plus its auxiliary tables for queries later in other weblogs. The account table is related to a customer table by a n:1 relationship: One customer can have many accounts, but an account is uniquely assigned to a customer. As for the customer data we can profit from the fact that there is already a table with customer names and some relevant attributes: The table SCUSTOM is part of the flight model that is delivered with every ABAP system and this table should contain about 1500 customers in the Demo System you use. So we need not define a customer table of our own and fill it with data, but we can simpily re-use the table SCUSTOM and relate our account table YACCOUNT to it by a foreign key relationship.

In another weblog we will fill the database table YACCOUNT with some test data. This step is very important for every business application. You have to test an application to see if it works. And you only can test it, when you have some test data: We will first manually provide a small set of test data for the check tables in the Data Browser (SE16). Using these entries in the check table a program of some 70 lines will create random entries for the table YACCOUNT. This way we will get the test data that enable us to run Web the Dynpro ABAP based applications we write in later weblogs in the environment these applications need.

Let us now consider the structure of our central table in some detail: The account table should provide columns for

The client

The account number,

The bank,

The city of the bank,

A customer ID that is related to the customer table,

The balance

The currency

A category that specifies the height of the income that is transfer to the account every month.

Since the identity of an account is defined by client, account number, bank and the city of the bank, we define a composite key with these four columns. This means there cannot be an account with the same account number in the same client with the same bank in the same town.

Further will assume that the number of banks, towns, currencies and income categories is limited in our example. So we will provide check tables: For example the currencies permitted in our table, the foreign-key table, are controlled by a table for currencies, the check table. These tables have to be related by a foreign key relationship that is can also defined in the ABAP Dictionary.

This way we will also create these check tables and the relevant foreign-key relationships when defining the table YACCOUNT. Though we can profit from the existence of the table SCUSTOM we still need some other tables: For the bank and the city of the bank we will provide check tables to delimit the banks and cities allowed, but we will keep these tables very lean indeed. As for the category, we do not need a check table, but can use so-called fixed values in the Domain of the relevant Data Element to control the number of entries in the category column of the account table.

Defining all these things plus the Data Elements required might seem a bit tiring if not intimidating at first, but you will soon realize that you do not need a lot of practice to create Data Elements and database tables really fast in the Data Dictionary. It will be the same experience as the one you had when ABAP Trial Version for Newbies Part 7: Creating a Program and a Package - An Introduction to the SAP...: When you have done it a few times with the relevant tools and have got accustomed to the procedure, you will find it as easy as pie. So do not let yourself be put off when having a look at the all the stuff we are defining in this weblog.

I will spend less time on explaining details than in the last weblogs. Having worked your way through the weblogs of our series you are sort of an advanced Newbie to ABAP. When I show you how to create Data Elements and Domains, how to define tables and how to relate them by a foreign key relationship I presuppose that you are able to find your way in the ABAP Dictionary. In other words: I presuppose that you are familiar with the ABAP Trial Version for Newbies Part 11: Getting More Familiar With the ABAP Dictionary in our series. In particular you should know what a Data Element is, what the respective definitions of field labels for Data Elements the definitions of fields for a table look like, not in great detail, but to the degree that I have explained this in the weblog mentioned

The Concept of a Client: A Key-Player in the SAP World of Business Programming

When defining the fields of the table YACCOUNT you see that the first field is the client. Probably you do not know what a client is. At least to me "The Client" was no more than a suspense movie before I joined SAP. Within the context of SAP ERP systems the client is an important concept that deserves some words of explanation. Though the main focus of this weblog series is on ABAP, its development environment and the way you develop with this powerful language for business programming, it is nevertheless for some of you also a first step into the SAP world. And within this world you simply should not only know the definitionof what a client is, but have some basic understanding of this concept. Those of you who are more interested in ABAP development in a narrower sense or are already familiar with the client concept should simply skim or skip the rest of this section.

A Client is in commercial, organizational, and technical terms, a self-contained unit in an SAP system with separate master records and its own set of tables. This definition of a client in the SAP Help Portal is, of course, comprehensive and true, but you may still as yourself how one concept can have a commercial, an organizational and a technical aspect at the same time. Let me start by sketching the commercial aspect of the client concept. Then I explain how this creates technical requirements and how they are fulfilled.

From one point of view an ERP system holds

A large bunch of business data and

the applications that work with this data. These applications represent the business processes of a company.

Obviously it is a good idea to organize these data according to organizational units and the business needs of a company. The same holds true for the technical fine tuning of the business applications: For example, the different international subsidiaries of a global company may need to implement the same business processes differently in their systems. What I have loosely called the technical fine tuning is done by means of customizing in an ERP system: You adapt an application to the specific structure of your business processes. And maybe you want to adapt it in different ways for different subsidiaries. In other words: The data need to be structured by organizational and business-related criteria, and the processes may require different customizing for different subsidiaries of a company.

It is the concept of a client and the way it is implemented in a SAP ERP system that helps to fulfill all these requirements:

The client is the highest organizational unit in an ERP system and a self-contained unit with its own master data. From a commercial point of view a client can represent a corporate group of legally independent companies. And you can customize the same process differently in different clients.

How is this separation technically achieved with the client concept:

The client is the first key field in most relevant tables of an SAP ERP system.

A user in an ERP system is always logged on to a particular client of a system.

A SELECT in a program always selects only the data from the client the programs runs (system field sy-mandt ). There is no need to specify the client in the WHERE-condition. The client-specific data are automatically selected.

It is because of this structure that you can keep the data of different clients in one table and still keep them apart as if they were stored in different tables. And this is why our table is designed the way it is with the client as the first key field. By the way, the client-specific customizing is also, to a large degree, realized by tables that are designed the way I have just described.

But now let us return from this abstract explanation of a key concept of the SAP ERP world to the mundane task of creating our table.

Defining the Data Elements

It is common practice to use Data Elements for all columns of a table and there are good reasons for this practice:

All foreign keys must be typed with a Data Element.

So check tables to control the values of columns require a Data Element.

The field labels defined in a Data Element will automatically provide translatable column headers and labels when the respective Data Element is shown in the User Interface. This applies for Dynpro-driven test UIs and Web Dynpro based user interfaces.

We need Data Elements for the account number, the bank, the city, the customer ID, the balance the currency, the date of the last entry, and the category of the account. The first column for tables in common business applications is the field MANDT which represents the client. We name the other columns ACC_NUM, BANK, CITY, CUSTOM'ER, BALANCE, CURRENCY, LAST_ENTRY, and CATEGORY. The Field CUSTOMER contains an ID that is related to the customer table SCUSTOM by a foreign key relationship. For the columns MANDT, CUSTOMER and CURRENCY we can reuse Data Elements that already exist. This re-use of existing Data Elements is also common programming style when developing in ABAP.

Data Element for the Account Number: Y_ACC_ NUM

First we create the Data Element Y_ACC_ NUM. We navigate to the transaction SE11, select Data Type and press the button Create. In the dialog window we choose Data Element and continue. We enter the short description Account Number, select the radio button Predefined Type on the tab rider Data Type, and input NUMC, a numerical character type and a length of 8. On the tab rider FIELD LABEL we have to input labels of different length for our field:

We input Acc.Num. as a short label and Account Number in the other lines. We need not provide enough space for some translation of the labels, and so we need not input the length for the Field Labels, but the system calculates it by the actual length of the field labels we have just input.

When activating the Data Element we choose y_abap_demo as the package as in the other examples. The next dialog window proposes us the same transport request as in the last example programs. We continue and activate the data element (we will use this package and this transport request throughout this whole example without mentioning this fact any more in this weblog).

Data Element for the Bank: Y_BANK

Next we create a data element for the name of the bank. The procedure will be the same:

Input the name in transaction SE11: Y_BANK

Enter a short description: Bank.

Input the appropriate format: CHAR of length 30.

Go to the tab rider Field Label and enter Bank for the labels of all lengths.

Activate and Save.

Data Element for the City: Y_CITY

The Data Element Y_City has City as a short description and as the text for all field labels. Its format is CHAR and length 30. For the column CUSTOMER we can reuse an existing Data Element from the table SCUSTOM, as I have already told you.

Data Element for the Balance: Y_BALANCE

So we go on and create a Data Element for the column BALANCE: Y_BALANCE. We proceed in the same way, but this time we will use a Domain instead of defining the data format by ourselves: We input the short description Balance, select Domain as Elementary Type, input the Domain name S_PRICE, and press Enter: The data format of the domain is shown below: CURR 15, decimal places 2. To complete the definition of this Data Element Y_BALANCE we have to provide field labels in the way shown before: We input Balance for all lengths. We save and activate the Data Element. Since you need to save and activate all objects that you create this procedure will only be mentioned time and then. I just presuppose that you know by now when and how to do this.

Data Element for the Last Entry: Y_LAST_ENTRY

For the field LAST_ENTRY we create the Data Element Y_LAST_ENTRY with the Domain S_BDATE and LastEntry as the text for all field labels and Last Entry as a short description.

Data Element for the Category: Y_CAT

Last we define the DATA Element Y_CAT with the short description: Account Category and the field labels: Category (short) and Account Category (all other labels). By forward navigation we define the Domain: Y_CAT, format: NUMC and length 1. We switch to the tab rider Value Range and input the fixed values: 1 with the short description low income, 2 and medium income, 3 and high income.

Creating Database Table YACCOUNT

Now we have all the Data Elements we need at hand and we can create the database table YACCOUNT. By the way: It is also possible to create the Data Elements you need by forward navigation when defining a table, but I have decided to separate the definition of the Data Elements from the definition of the database table in this weblog for didactic reasons.

We return to the start screen of the Data Dictionary, select Database Table, enter YACCOUNT, and press the button Create.

In the next window we input the short description: Account. Under the tab rider Delivery and Maintenance we input A as the Delivery Class and Display/Maintenance allowed with Restrictions.

We change to the tab rider Fields and enter the name of the fields and their Data elements as shown in the figure below:

Next we mark the line CUSTOMER and press the Key icon to define a check table for this line and a foreign key relationship. We refuse the proposal in the first dialog window as this suggests the check table from the Domain and this table is not suitable for our purposes. In the next dialog window we input the short text: Check table for account ID and the check table SCUSTOM. Next we press the button Generate Proposal and get to the next dialog window. There is no magic, but the system draws the proposal from the identity of the Data Elements.

We enter 1: CN as the cardinality, and then we check the definition of the table for consistency by pressing the Syntax Check icon. If this check is successful we press the Copy button.

As you need to define other check tables on your own later in this weblog, let us repeat the steps necessary to define a foreign key relationship with a check table that determines which values can be input into a particular field:

Mark the line with the relevant field/column.

Press the button with the Key symbol.

Input some explaining text and the name of the check table.

Press the button Generate Proposal. (If the relevant Data Element has a value table defined in its Domain this table is proposed by the system after step 2.)

If the proposal is ok, just enter the relevant cardinality and press Copy to close the dialog window and keep what you have input there.

We need another check table for the currency. Again we mark the line and press the Key symbol. This time we confirm the proposal:

And we get rewarded by an almost complete proposal that is made by the system:

We only have to enter a suitable text and there we are. We select the Copy icon and have another check table. The tab rider Entry help/check shows us the check tables for the table YACCOUNT.

Next we define a reference field for the column of the type CURR as shown in the figure below:

A reference field is needed for all currency and quantity fields as the figures in this field need a currency or unit. If you have no reference field and table for fields of this kind you will get a syntax error. This syntactic constraint reflects a semantic fact: A figure in a field does not tell you a lot if you do not know to which quantities or currencies the figure is related: It does not suffice to know that you have, for example, five items, but you need to know if you have five meters, five Euros, five pound etc. And this information is provided the reference field and table.

There still remains some work to be done. We select the menu Extras - Enhancement Category. Here we determine if and if so to which degree the table can be enhanced: We choose Can be enhanced (character-type or numeric) and press Copy.

After saving again we press the button Technical Settings and choose the Data Class APPL0 and the size category 0.

We save and activate, return to the view of the table we have been working with before. Next we save and activate the table.

Let us pause for a second and reconsider the steps necessary to create a database table in the Data Dictionary:

Go to the start screen of the Data Dictionary.

Type in the name of the table and select the Create button.

Enter a Short Description

Choose the Delivery and Maintenance tab rider and input a delivery class and determine if and how the table can be maintained and displayed.

Change to the Fields tab; input the name of the fields and the respective Data Elements.

Select the Key checkbox for the key fields and select Initial Values for all fields.

Save and press the button Technical Settings.

Choose a Data Class and a Size Category by using the input help.

Save, go back and choose the menu item Extras - Enhancement Category and input this category.

Provide check tables and Reference Fields plus Tables if you need to.

Save and activate the table. The table is now being created on the database. This is what is meant by "metadata-driven creation" of database tables: You define the metadata and once you activate this metadata, the table is created on the database.

Providing Some Check Tables

Now you can test to which degree you have already understood the steps you need to define a table. We still have to create the check tables for the columns BANK and CITY, and I will keep the description of how to do this really short. In case you have any problems when creating theses tables just return to the last section.

For the field BANK in the table YACCOUNT we create the table YBANK with only one column BANK and the Data Element Y_BANK: Again we choose A as the delivery class, but this time we select Display/Maintenance Allowed. This is important, because we will input some banks into this table in the Data Browser (SE16), and this is only possible if the maintenance of the table in the SE16 is allowed that is with this value for the attribute Data Browser/Table View Maintenance. The technical settings and the enhancement category for the table YBANK are the same as for the table we have defined above. So should know how to handle this.

We need another check table for the field CITY in the table YACCOUNT. We name this table YCITY. Apart from the columns this table has the same attributes as the table YBANK and the column CITY with the Data Element Y_CITY and another column CURRENCY with the Data Element S_CURR. After you have completed their definitions, we need to create the foreign key relationships with the relevant columns of the table YACCOUNT.

You should also we able to do this on your own by now. Let me just give you some hints on how to do this: Mark the relevant line in the table YACCOUNT, press the Key icon, enter a short text plus the relevant check table, press the button Generate Proposal, and input the Cardinality 1:CN.

You may ask what the second column CURRENCY in this table is for. We will use this table YCITY to generate test entries for the table YACCOUNT in another weblog. For reasons of simplicity we suppose that the currency of an account is determined by the city of the respective account: For example, all accounts on banks in New York have Dollar as the currency. To uniquely assign each city to a currency we write the respective currency for each city in the same row as the city. Our data generator program will select the city and currency and one dataset from one row of the table YCITY. When looking at the code of the program that generates the test data for us, you will understand why and how this simple table will do the same job as a long CASE construction and keep the code far more readable.

Of course, in a more complex example with a more complete data model we would also add the country, maybe as a column of the currency table or in a table of its own. But I have to decided to keep our example simple instead of defining as many tables and attributes as we would need in a more real-world example.

Though we want to keep the model simple we will quickly establish another foreign-key-relationship for the table YCITY. With some experience in data modeling you probably know which relationship is still missing: We mark the line CURRENCY in the table YCITY, press the Key symbol, and we get the right proposal by the system: Check table SCURX. So we complete the definition of this check table in the same way as before when we defined the other check tables. So much for some short considerations on the design of our check tables

Do not forget to save and activate the table YBANK and YACCOUNT, and there we are: We have defined a table with four check tables, which also provide an input help for the respective column of the table. You might ask yourself why there are more input helps than check tables. That there is an input help for the column LAST_ENTRY results from the fact that every date field gets a calendar as a input help by the system for free. The input help for the column CATEGORY is based on the fixed values we have input in its Domain

The table SCUSTOM and the table SCURX are already well filled with data, and the tables YBANK and YCITY will be filled by us in the DATA BROWSER (SE16).

Summary

By now you should be able

To define Database tables

Including all the attributes

To define the Data Elements and Domains you need

To reuse existing Data Types

To establish the foreign key relationships with check tables.

From a more semantic point of view we created the tables we need in subsequent weblogs. Moreover you should have understood the basics of the concept of a client in the SAP world, what it is good for, how it is technically realized, and which impact this concept has on the design of database tables. What is still missing is the content of these tables. And this is what we will provide in the next weblog: We will write a little program of some 70 lines that enables you to create a number of random entries for these tables. Doing this you will understand once more what is so good about internal tables.