2.Data Dictionary

DDIC

1.Introduction

1. Tcode for DDIC is SE11.
2. Data dictionary is the central source of the data base management system. It is used to create and alter data base table in SAP
3. SAP is the ready made product which contain predefined database tables and programs
4. In real-time most of the time, we use standard database tables only. very few time we create our own database table.

    1.1.Steps to view table entries

1. Execute TCode SE11.
2. Select radio button database table.
3. Enter the database table name and click the display button.
4. Now, Click on contents from menu.
5. Click execute to view database entries.

    1.2.SQL ( Structured Query Language )

1. DDL - Data Definition Language. Which is used to create and alter the DB table.
2. DML- Data Modification Language. Which is used to perform DM operations like Insert, Update, Modify, Delete
3. DCL- Data Control Language. Which is used to perform commit and roll back operations.

    1.3.SQL vs Open SQL

1. SQL is database dependent, Where as SAP is database independent. So SQL does not support SAP.
2. Open SQL support the SAP because Open SQL is database independent.
3. Open SQL does not support DDL, So we cannot create DB tables like we create tables in other database systems like oracle, etc
4. Open SQL supports DDIC( Data dictionary ), Which is used to create and alter DB tables in SAP.

2.Data Dictionary(DDIC)

1. Data dictionary is the central source of the database management system.
2. Main functionality of the data dictionary is to create and alter the database tables.

There are two ways of creating the tables by using DDIC

1. Direct Method
2. Data Element Method
• Bottom-Up Approach
• Top-Down Approach

    2.1.Technical requirement to create the table:

1. Name of the table.
• In ABAP, Name of the table must be start with 'y' or 'z', because 'a' to 'x' are reserved for SAP.
2. List of fields, data types, length.
3. Delivery Class.
• Delivery class defines the owner of the tables as well as it controls the transport of the data from one table to another table.
• Types of delivery class:
• A - Application table.
• C - Customising table.
• L - Table for storing temporary data.
• G - Customising table.
• E - Control table.
• S & W - System table.
4. Data class.
• Data class defines the physical area of the database, where our tables are logically stored.
• Some of the important data classes are:
• APPL0 - Master data class
• Master data are the data, That are accessed and updated rarely. 
• Ex: Vendor master data, Customer master data, Material master data.
• APPL1 - Transactional data class
• Transactional data are the data, that are accessed and updated frequently.
• Ex: Sales order data, Purchase order data.
• APPL2 - Organisational data class
• Organisational data are the data, that are accessed frequently but updated rarely.
• Organisational data are created at the time of configuring system.
• Ex: Company data, Branches data. 
5. Size category.
• Size category determines the space required for the table.
6. Domain.
• Domain is the collection of data type and its length.
• Domain = (Char, 10).
7. Data element.
• Data element is the collection of domain with short description.
• Date element = (Domain, Desc).

Note:

1. Every table must have at-least one field as primary key field, that should be first field and the date type for that field should be char.
2. SE11 is the Tcode for DDIC.

    Object 1:

    Create the table(id, name, addr) for employee using direct method.

    How to create a table in DDIC using direct method:

1. Execute SE11 -> Select radio button (Database table) -> Provide the table name(zemp_det) -> Click on create -> Provide short description -> Provide delivery class (A) -> Select the display maintenance is allowed from drop down -> Click on fields tab -> Click on predefined type button -> provide field name, data type, length, short description 
• Eid, Char, 10, Employee Id
• Ename, Char, 25, Employee Name
• Eaddr, Char, 35, Employee Address
2. Click save -> Click local object
3. Check the table(ctrl+f2) -> Click back button
4. Click on technical setting -> Select the data class -> Size category -> save the tech setting -> click back button 
5. Activate the table -> press Enter

    How to enter data to the table(zemp_det):

1. In the menu bar click on utilities -> table contents -> create entries -> provide the data

    How to display the table's data(zemp_det):

1. In the menu bar click the utilities -> table contents -> display -> execute

Note:

1. In real time we never create the database tables using the direct method because, if we want to establish the relationship between any two tables then we must maintain the same domain name for both the tables.
2. In direct method, we never use domain name. 

Note:

1. Creating table using data element method.
• Bottom Up Approach: Create domain -> Create data element -> Enter it in table.
• Top Down Approach: Create Table -> Enter and create data element -> Enter and create domain.

    Object 2:

    Create the table(id, name, addr) for employee using data element bottom up approach.

    How to create domain:

1. Execute SE11 -> Select radio button domain -> provide the domain name -> click on create -> provide the short description -> provide data type -> provide length -> save -> check -> activate -> Enter
2. Repeat the step 1 to create domain for remaining fields.

    How to create data element:

1. Execute SE11 -> Select the radio button data type -> provide the data element name -> click create  -> Select data element -> Click create -> provide description -> provide the domain name -> press enter ->  save -> check -> activate the data element
2. Repeat the step 1 to create domain for remaining fields.

    How to create table in DDIC using data element bottom up approach:

1. Execute SE11 -> select radio button data base table -> provide table name -> click on create -> provide short description -> select delivery class A -> select table maintenance allowed -> enter the field names and its respective data element one by one (Field name, Data element name).
• Eid, zde_eid
• Ename, zde_ename
• Eaddr, zde_eaddr
2. Save the table -> check the table -> activate the table -> click technical setting -> provide data class and size category -> save tech setting -> click back -> activate the table.

Note:

1. Display maintenance allowed - we can create entries to table and display them.
2. Display maintenance allowed with restriction - we can only display the table entries, can't create.
3. Display maintenance not allowed - we cannot create or display the table data.

Note:

1. We cannot delete the domain which is already assigned to the data element.
2. We cannot delete the data element which is already assigned to the database table.

    Object 3

    Create the table(id, name, addr) for employee using data element top down approach.    

    How to create table in DDIC using data element top down approach:

1. Execute SE11 -> select radio button data base table -> provide table name -> click on create -> provide short description -> select delivery class A -> select table maintenance allowed ->
2. Click on field tab  -> provide field name -> Enter data element name (which is not created yet) -> double click on the data element name -> click local object -> press enter -> click yes -> provide description -> 
3. provide domain name(Which is not created yet) -> double click on domain name -> click yes -> click local abject -> create the domain -> click yes -> provide description ->
4. provide the datatype and length -> save, check, activate domain -> click back -> save, check, activate data element -> click back
5. repeat the above 4 steps for remaining fields.
6. save, check, activate table.
7. If we get enhancement category for the table is missing warning message, then click extra -> enhancement category ->press enter -> select can be enhanced ( char type or num type ).
8. click technical setting -> provide data class and size category -> save tech setting -> click back -> activate the table.        

Note:

1. We can place upto 16 primary keys per table.
2. Primary key field in the table should be unique and not null.

    2.2.Foreign key relationship:

1. Foreign key is a field in one table that is connected to another table vis foreign key relationship.
2. The purpose of FK is to validating the list of possible value being entered in one table(FK table), against the list of values present other table(check table).

    Technical requirement for FK relationship:

1. Domain name of the both the fields in both the table(FK and Check table) must be same.
2. The check table field must be the primary key.

Note:

1. /O - It opens a new sessions without terminating the current session.
2. /N - It opens a new sessions but terminate the current session.
3. We can open upto 6 sessions at a time.
4. In real time, depends on the server capability no of sessions can be opened may restricted to 3 or 4.

    Steps to establish the FK relationship:

    Execute SE11 -> select database table -> provide the FK table -> click change mode -> select the field for which we want to establish FK relationship -> click FK icon under fields tab ->provide check table name -> click generate proposal -> press enter -> save check activate the table.

    2.3.Reference field:

1. Whenever we are working with amount field, Then we must provide reference as currency field.
2. Whenever we are working with quantity field, Then we must provide reference as unit of measurement field.
• amount field - curr
• currency field- cuky
• quantity field - quan
• uom field - unit
3. Table field name:
• MNO, MDESC, MQTY <-> MUOM, MPRICE <-> MCUKY.
• where muom is FK rel with table t006-mseh1 and mcuky is with table tcurc-waers.

Note:

1. T006 is the std.db table which contains all the unit of measurements.
2. In real time, whenever we are working with unit of measurement field, we must establish the foreign key relationship with T006 as a check table

Note:

1. TCURC is the std.db table which contains all the currency in the world.
2. In real time, whenever we are working the currency field, we must establish the FK relationship with TCURC table as a check table.

    Steps to provide reference field:

1. Select the quantity or amount field -> Click on currency/quantity field tab -> provide the reference table name and reference field name.

Note:

        ( Quantity Field ) <---- Ref to ---- ( UOM ) <-----FK rel with-------- ( Table T006 ).

        ( Currency Field ) <---- Ref to ---- ( CUKY ) <-----FK rel with------ ( Table TCURC ).

Note:

       Some of the std.db tables: 

            T001    -    Company Codes table

            KNA1  -    Customer Master table    

            LFA1   -    Vendor Master table.

    2.4.Include and Append structure:

1. Custom Table - Include structure
2. Standard Table - Append structure

    Diff btw Include Structure and Append Structure::

1. Include structure is used to add additional fields to the custom tables.
2. Same include structure can be used to add fields to the different custom tables.
3. Include structure can be stored in either $tmp or own package.

1. Append structure is used to add additional fields to standard table.
2. One append structure can be used to add fields for one std table only.
3. Append structure should be stored only in own package. because $tmp is non-transportable.

Note:

        If we want to add block of fields to more than one table, instead of maintaining the same fields in all the table we create one structure with those fields and add it to all the tables as include structure.

    Object 4:

    Add ORT01, LAND1, SPRAS fields to the custom table(zemp).

    Soln:

        For this object we have to create a structure with above mentioned fields first and then we use include structure concept to add these fields to tables.

    How to create structure:

1. Execute SE11 -> select data type -> provide structure name(zstr_test) -> click create -> select structure -> press enter provide short description -> provide component and component type(data element) -> 
• ORT01, ort01
• LAND1, land1
• SPRAS, spras
2. save check activate the structure.

    How to add structure into custom table:

1. Execute SE11 -> select database table(zemp) -> click change mode -> select place where you want to include the structure -> provide the field name as .Include -> provide data element as structure name(zstr_test) -> save check activate the table.
2. Now the structure added in the table is called include structure.

    Object 5:

    Add tax indicator field(TIND) to the std.db table KNA1.

    How to create own package:

1. Execute SE80 -> click edit object -> click development coordination tab -> provide package name(ZARKU) -> click create -> provide short description -> press enter -> click create request -> provide short description -> press enter -> press enter.

    How to add field to the std.db table:

1. Execute SE11 -> select database table -> provide KNA1 -> click display -> click append structure in application tool bar -> click create append -> provide the append structure name(zstrc_tax) -> click enter -> provide short description -> 
2. provide field name(TIND) -> provide component type(ZDE_TIND) -> double click on component type -> click yes -> click yes -> 
3. enter your own package(ZARKU) -> click save -> press enter -> press enter ->provide short description -> 
4. provide domain name -> double click on it -> press yes -> save it on your package -> click yes -> provide short description -> provide data type(char) and length (1) -> save check activate the domain -> press back -> save check activate the data element
5. repeat above step for remaining fields

Note:

1. How to display only few fields from a table?
• open a table from SE11 -> click on contents -> click setting from menu bar -> formate list -> choose fields you want to display -> press enter execute.
2. How to display short description as table heading in output?
• open a table from SE11 -> click on contents -> click setting from menu bar -> user parameters -> select field labels -> press enter -> execute.
3. how to resolve the Error. "structure change at field level".
• Any alteration at table leads to error - structure changed at field level. we must adjust the database to resolve the error.
• Click utilities from menu bar -> database object -> database utilities -> click on activate and adjust database table -> click yes.

    2.5.Types of tables:

    There are 3 types of database tables in SAP

1. Transparent Table
2. Cluster Table
3. Pooled Table

    Transparent Table:

1. Transparent tables are one to one relationship. ie.. if you create one table in DDIC then it will store like only one table in database.we can create only transparent table.
2. Ex: LFA1, KNA1, MARA, etc

    Cluster Table:

1. Cluster table are many to one relationship. ie.. if we create many cluster table in DDIC then it will form like a table cluster in database. 
2. Buffering is not possible in cluster table.
3. cluster table is suitable for when to fetch less amount of data from more no of fields.
4. Joins are not possible in cluster table.

    Pooled Table:

1. Pooled table are many to one relationship. ie.. if we create many pooled table in DDIC then it will form like a table pool in database. Ex: A012
2. Pooled table are suitable for when we fetch the large amount of data less no of fields.
3. We cant add new fields in pooled table.ie.. append structure is not possible.
4. Joins are not possible in pooled table.
5. Indexes are not possible in pooled table.

    2.6.Indexes:

    Indexes are used to improve the performance of the select query. There are two type of indexes

1. Primary Indexes
2. Secondary Indexes

    Primary Indexes:

1. Primary indexes are the primary key for the database tables.
2. We can add upto 16 primary keys per table.
3. We cannot create a table without primary index.
4. We can create primary index only for the custom table.

    Secondary Indexes:

1. We can create a table without secondary index.
2. We can add upto 9 secondary indexes per table.
3. Secondary index fields are non primary key fields.
4. We can create secondary index for both standard and custom table.

    How to create secondary index:

1. Execute SE11 -> open a table(zemp) in display mode -> click indexes in application tool bar -> click create -> create index ->
2. Provide index name(SP1) -> provide short description -> provide field name(non pk fields)
3. save check and activate.

    2.7.Search Help:

    Search help is used to provide the list of possible values to the input variable. Create the search help and attach it to data element. There are two types of search help,

1. Elementary search help
2. Collective search help

    Elementary Search Help:

    How to create elementary search help:

1. Execute SE11 -> select search help -> provide search help name(ZSH_TEST) -> click create -> press enter -> provide short description -> provide the selection method is T001
• Search help parameter -  BUKRS, BUTXT
• imp - *
• exp - *, *
• lpos -1, 2
• spos - 1, 2
2. Save check and activate

    How to attach search help to data element:

1. Execute SE11 -> select data type -> provide data element(ZDE_SH_BUKRS) -> click create -> enter -> provide description -> provide domain as char, 4 ->
2. Click further characteristic tab -> provide search help name(ZSH_TEST) and parameters BUKRS ->press enter
3. Save check and activate.

    How to use search help in report.

1. Execute SE38 -> create a report with parameter type as above created data element.
• parameter p_bukrs type ZDE_SH_TEST.

    Collective Search Help:

    Collective search help is the collection of elementary search help.

    How to create collective search help.

1. Execute SE11 -> select search help -> provide search help name(ZCSH_TEST) -> click create -> select collective -> press enter -> provide short description -> 
2. provide search help parameter -> provide import and export checkbox -> provide data element -> click include search help tab -> provide the elementary search helps( ZSH_TEST1, ZSH_TEST2 ) -> press enter.
3. Select each elementary search help -> click parameter assignment -> click yes -> press enter 
4. save, check and activate the collective search help.

    How to attach coll.search help to data element.

1. Execute SE11 -> select data type -> provide data element name(ZDE_CSH_TEST) -> click create -> press enter -> provide short desc -> provide domain as char4 ->
2. Click further characteristic tab -> provide search help name(ZCSH_TEST) and parameters BUKRS -> press enter
3. Save check and activate.

    How to use search help in report.

1. Execute SE38 -> create a report with parameter type as above created data element.
• parameter p_bukrs type ZDE_CSH_TEST.

Note:

        Before search help, match code is used to provide list of possible values to input variable. now match code is absolute

    2.8.Lock Object

    Lock object are used to avoid concurrent access of multiple users on the same data base table.
Whenever we create the lock object then it generates 2 function module.Lock object must be starts with 'EZ'

1. ENQUEUE - For lock
2. DEQUEUE - For unlock

    How to create lock object:

1. Execute SE11 -> select lock object -> provide lock object name(EZ_LO_TEST) -> click create -> provide short desc -> click table tab -> provide table name(KNA1) ->  select the lock mode is read lock -> 
2. save, check and activate ->
3. Menu bar -> goto lock modules to see the generated function modules.
• ENQUEUE EZ_LO_TEST
• DEQUEUE EZ_LO_TEST

    Types of lock:

1. Write/Exclusive lock: Locked data can be read or processed by only one user
2. Read/Shared lock: Several user can read the data but only one can process the data.
3. Exclusive but not cumulative: More than one user can process the data.

    2.9.Views:

1. If we want to display part of the data from more than one table, then we will fetch data from all the table and merge data and display the data.
2. If we are required to display data from more than one table very frequently, then its better to create a view
3. views are logical database.
4. view does not contain data permanently, hold data only at run time.

    Types of views:

    There are four types of view

1. Projection view
2. Database view
3. maintenance view
4. Help view

    Projection view:

1. projection view is used in fetching particular set of fields from a single table repeatedly.
2. projection view always involved in single table.

        How to create projection view:

1. Execute SE11 -> select view -> provide projection view name(Z_PVIEW) -> select projection view -> press enter -> provide shord desc -> provide basic table(T001) -> click fields -> select required fields -> press enter 
2. save check activate.

        How to use projection view in report:

1.         Report zpview_test line-size 1023.
2. 
   
3.         data: begin of wa_t001,
4.                     bukrs type t001-bukrs,
5.                     butxt type t001-butxt,
6.                     ort01 type t001-ort01,
7.                 end of wa_t001.
8.         data: it_t001 like table of wa_t001.
9.         select bukrs butxt ort01 from z_pview into table it_t001.
10.         sort it_t001.
11.         loop at it_t001 into wa_t001
12.             write:/ wa_t001-bukrs, wa_t001-butxt, wa_t001-ort01.
13.         endloop    

Note:

1. Fetching the data from view is faster than fetching the data from database.
2. Whenever we execute the program, the view internally generates a select query to fetch the data form database.       

    Database view:

1. Database view is used in fetching data from more than one table.
2. Database view always involve in more than one table.

        How to create database view:

1. Execute SE11 -> select view -> provide view name(Z_DBVIEW) -> click create -> provide short desc -> 
2. provide the tables which are having relation  -> select all the tables (KNA1, T001) -> click relationship -> select checkbox -> press enter ->
3. click on view fields tab -> click on table fields button -> double click on each table -> select required fields -> press enter -> 
4. save check and activate.

Note:

        Maintenance and help view are created by SAP.

    2.10.Buffering:

1. Buffering is the temporary place in application server
2. When ever we execute any object, the system check for data in buffer area.
3. if the data is available then it pick it from buffer area for processing. if not then it pick from database and place it in buffer area for processing.

Note:

        Presentation layer

                    |       [Buffering area]

        Application layer

                    | 

        Database layer

    Types of buffering:

1. Single record buffer
• With this kind of buffering the selected data will be loaded into the buffer area.
2. Generic record buffer
• With this kind of buffering the key information will be loaded into buffer area.
3. Fully buffered
• With this kind of buffering the entire database will be loaded into buffer area.

    2.11.Table Maintenance Generator(TMG):

    TMG is used to provide the interface to the database table to perform insert, update, modify operations without any code.

    How to create TMG:

1. Execute SE11 -> select database table -> provide table name(ZEMP)
2. in the menubar click on utilities -> TMG -> select authorisation group(get it from basis ppl) -> provide function group as table name(ZEMP) -> select maintenance type 1-step -> provide screen no -> click create -> save in local object -> save

    How to perform DML operation:

1. Execute SM30 -> provide table name(ZEMP) -> click maintain -> press enter -> perform operation on data.

    How to create TCODE for table:

1. Execute SE93  -> provide table(ZEMP) -> click create -> provide short desc -> select transaction with parameter -> press enter -> provide tcode SM30 -> the checkbox initial screen -> select GUI check box 
2. provide view name as table name(ZEMP)
3. Update is X(ie.. maintain)
4. click save.

    Some of the events in TMG:

1. Before saving the data in the database
2. After saving the data in the database
3. Before deleting the data displayed
4. After deleting the data displayed
5. Create new entry
6. ETC.....