Skip to Content

INTRODUCTION:

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                deepakmachal894@gmail.com

0002            A                deerohit87@gmail.com

0003            B                Deepak.machal@rediffmail.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                    deepakmachal894@gmail.com

0002            Roopali Machal      Australia                    deerohit87@gmail.com

0003            Rohit Machal          US                            Deepak.machal@rediffmail.com

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:

/wp-content/uploads/2012/09/1_135675.jpg

Similarly we have Key fields for table CKIS:

/wp-content/uploads/2012/09/3_135676.png

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.

/wp-content/uploads/2012/09/4_135677.png

Choose Data Base View:

/wp-content/uploads/2012/09/5_135678.png

Following Screen will come give the description of view:

/wp-content/uploads/2012/09/6_135679.png

Give the table name here and Hit enter:

/wp-content/uploads/2012/09/7_135680.png

Give the Join condition as I have given below.

/wp-content/uploads/2012/09/8_135681.png

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.

/wp-content/uploads/2012/09/9_135683.png

Click on table and select the base table let us first select all wanted fields from CKIS table/wp-content/uploads/2012/09/10_135684.png

/wp-content/uploads/2012/09/11_135685.png

Now do the same, select wanted fields for KEKO and press COPY

/wp-content/uploads/2012/09/12_135686.png

Fields from table KEKO will also come here :

/wp-content/uploads/2012/09/13_135687.png

Check and activate the View.

Now let’s see if we have created the right view./wp-content/uploads/2012/09/14_135688.png

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.

/wp-content/uploads/2012/09/15_135689.png

See data below:

/wp-content/uploads/2012/09/16_135690.png

Reference(s)

1.      http://help.sap.com/saphelp_nw70/helpdata/en/cf/21ecd2446011d189700000e8322d00/content.htm

2.      http://help.sap.com/saphelp_nw04s/helpdata/en/cf/21ed2d446011d189700000e8322d00/content.htm

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Deepak Machal Post author

    Here is the document showing how to create view.. which is usally need while creating the Generic Data Source based on view.

    Comments are welcome   😉

    Thanks,
    Deepak Machal

    (0) 

Leave a Reply