BW Training 3-Types of Applications and Details , R/3 Database Design

TYPES OF APPLICATION

Applications are generally categorized into 2 categories , Online transaction processing (OLTP) & Online analytical processing (OLAP).

OLTP

You are going to process your transaction online. As soon as the business takes place , This data has to be updated immediately into the database. In this case we will be using OLTP type of applications.

Example :

Why data has to update immediately , Suppose you went to an ATM and with drawn some money and system updating this after 15 minutes . Meanwhile you can withdraw some more  money . Even sap r/3 is a business driven application and is OLTP database . Suppose am developing a PO , Sales order , etc ..

Primary operation here is to update the data into the database Immediately .

OLAP

 Primary operation in OLAP is to analyse the data . You are extracting your data from OLTP to OLAP . When it comes to your BW , it is OLAP type of application .

DIFFERENCE BETWEEN OLTP AND OLAP

Relevance of data Example :

Any point of time , What the relevance of data in database . In OLTP always you will have present data . OLAP you will have historical data .

At 8 AM , In a  table 1000 records are there .

OLTP --- 1000 records                

After extraction ,

OLAP ---- 1000 records

At 8:05 AM

Lets say 5 transaction happened at this time . , Immediately OLTP gets updated .

OLTP --1005

OLAP--1000

EXTRACTION PROCESS:

Update Mode : It is a parameter setting which has to be set before extracting the data to BW, which defines what amount of data has to be extracted from the source.

Types of Update modes :

Full Update :

Whenever we use this update mode and extract the data ,it extracts entire data from source to target . If we are extracting from a file , Its always full update

Initial Update

Same as full update , But it will maintain a pointer / timestamp.

Delta update

Compares to the pointer and extracts only the updations.It extracts only LUWs

• Logical Unit of Works (LUWs) : These are modified and newly added records compared to previous load .

Example :

If we are extracting from a store or other system , first time we will be extracting using Initial update and there on delta update

If we want to see pointers / time stamps  of last extracts can be find using transaction RSA7  in R/3

DATABASE DESIGNS

First we are going to understand how exactly data gets saved in SAP R/3 , Next we will see how it gets stored in BW

R/3 DB Design :

R/3 called a 2 dimensional model . Data is stored in the form of table.a table consists of rows and columns .Every table will have fields / Entities  .Field defines physical existence of an object .Every column is a field and each row is a record. Every table will have primary key . The column which has primary are called as KEY COLUMN and the rest are called NON KEY COLUMNS.

Combination of two columns can  be  used to create primary key .maximum we can use 16 columns to form a primary key . Thats why we will call CUBE is 16 dimensional .

Example for primary key defined based on (BILLNO+ITEMNO).Maximum we can have 16 fields as a part of primary key.

If we use primary key of one table used in another table called as foreign key .

R/3 Database is designed based on ER model , which stands for Entity Relationship model .

Any transaction takes place in R/3 , This data will not be updated in single table . The transaction has been splitted (based on relationship between the entities.)and updated in multiple tables , in order to avoid data redundancy.

Below example illustrates how data stored

  R/3 is a ready made database, ready made tables are there , Ready-made fields are there .Everything is pre-designed, We just analysed how it was designed. We are not going to design any database of R/3.

Normal data storage in single table.

Data storage after  Entity relationship split

R/3 is optimized for storage not for analysis . This is one reason why we won't develop musch reports in R/3.When we develop report from R/3 it has to combine data from multiple tables and create a report . Can top level management wait that much of time to generate report.

So while extracting data from R3 to BW , I will put relevant tables data in one cube of BW.Even Though it start degrading DB performance , For us important is Analysis. BW is optimal for analysis , BW is denormalized.