Creation of DataBase View and its Fundamentals
Type of View
There are three types of views:
i) Database View
ii) Projection View
iii) Maintenance view
iv) Help View.
Let me give you the brief of various view in few lines.
Database View: data is distributed among various tables, so data base view provide the required/wanted view on such distributed data.
Projection View: This view is based upon only one table is basically used to hide the fields of table for example while accessing the database you only read/write the field content you actually needed.
Maintenance View: It has primary table it contains same key as its primary table. The related data in several tables can be maintained together in maintenance view.
Help View: Create Help view if the view with outer join is needed as selection method of search help. Database views are based upon the Inner Join and Help view are always based upon the Outer Join.
We will be talking about the Data Base view only in this document.
In some cases we may require to create the data from more than two tables in that case view comes into picture. So we may choose few fields from one table and few fields from other table to the view. We also maintain some join condition while creating the view such that how the view is going to hit the tables and fetch records. That condition is known as Join Condition.
Before I proceed further let me give you simple example on how the view work. Let’s say we have two table Customer1 and Customer2.
Customer 1 contains the following fields:
CustNo. CustName CustAdd
0001 Deepak Machal Australia
0002 Roopali Machal Australia
0003 Rohit Machal US
Customer2 table contains the following below entries:
CustNo CustLevel CustMail
0001 A email@example.com
0002 A firstname.lastname@example.org
0003 B Deepak.email@example.com
So our requirement is that to create single table/view in SE11 t-code which has the following fields and below data:
CustNo CustName(Tabl1) CustAdd(Tabl1) CustMail(Tabl2)
0001 Deepak Machal Australia firstname.lastname@example.org
0002 Roopali Machal Australia email@example.com
0003 Rohit Machal US Deepak.firstname.lastname@example.org
We can achieve the above table by creating view on top of that and giving the joining condition as
Table Field Table Field
Customer1 CustNo Customer2 CustNo.
So this is the basic example for WWW of view i.e. What Why and Where for view.
Let’s take the practical and Business scenario for creating the view and how to extract data from that into BW system.
We have table KEKO in ECC side of sap BW which is used to extract data related to Product Costing. It will provide the header data for the same.
The other table CKIS which provide the item unit costing / Itemization Product costing. It provide the item level data for product costing. Now for us the challenge is to show the header as well as item level data for Product costing.
We have the common key fields for both the table which will help us in getting only the common records in both the table sharing the same key. So In this way we will have Header data for Product Costing of reference object from KEKO table and its corresponding item level data from CKIS table.
See below the screen shot of KEKO table showing the key field of table:
Similarly we have Key fields for table CKIS:
BY examining the two tables we can see that we have the following common key fields from both the tables which will use in join condition for view to extract the data from both the tables.
1. MANDT (Client)
2. BZOBJ (Reference Object)
3. KALNR (Cost Estimate Number – Product Costing)
4. KALKA (Costing Type)
5. KADKY (Costing Date (Key)
6. TVERS (Costing Version)
7. BWVAR (Valuation Variant in Costing)
8. KKZMA (Costs Entered Manually in Additive or Automatic Cost Est)
Now let’s create a new view having this two table (KIKO and CKIS) as source with above fields as join condition:
Go to T-code SE11:
Give the view name you wish to create in my case its ZLBI_KEKI_CKIS and hit Create.
Choose Data Base View:
Following Screen will come give the description of view:
Give the table name here and Hit enter:
Give the Join condition as I have given below.
Go To View Fields tab. Here we can give the different fields which we want to see from different tables. View Field signifies the description of field in a view. Table shows which table the field is coming Fields is technical name of field.
Now do the same, select wanted fields for KEKO and press COPY
Fields from table KEKO will also come here :
Check and activate the View.
Give some selection if you want to give to see data otherwise press execute for display data. You can also click on Number of Entries to see the no of records fetch by view.
See data below: