SAP BW Training 6-BW Database Design

BW Database Design

BW is multi dimensional.In the case of BW data is stored in the form of cubes .

designing the cube is called SCHEME DESIGN.

Schema designs

Star Schema

• In the case of fact table or transaction table is kept inside a cube . That means a fact table is created in database when we create Cube .
• Even the master tables also kept inside the cube . That means a master table also gets generated when we create a cube .
• Fact table and master table are connected with primary key and foreign key relationship .
• Cube has 16 dimensions

For every dimension of a cube , One master table is linked .

Any schema design we consider , the below condition will satisfy

Facttable ---------Connected to ---------------> master table

Mastertable ---------Connected to ---------------> Dimension

So Indirectly

Fact Table ---------Connected to ---------------> Dimension

In the above figure we have two master tables , that means two dimensions , we can analyse data in two dimensions (Customer & Material)

C.No   RVN

C1 800

C2 400

M.No   RVN

M1 600

M2 600

BW 1.0,1.1,1.1C,> 2.0,2.1,2.1D

Star Schema have some disadvantages, so sap moved to extended start schema.

DISADVANTAGES:

• As the master table is inside the cube , we can't reuse fact table which degrades database performance .
• processing takes more time due to Alphanumerics values in Fact table which degrades the processing performance .
• As we have 16 dimension in cube and each one linked to mater table , Our analysis limited to 16 characters.

Extended Star Schema

1. Whenever we create a character info object , system will generate master table and SID table in database .

2. Whenever we create infocube , system will generate dimensional tables and fact tables .
3. Whenever we load master data , master table and SID table are filled

4. Whenever we load transaction data , the dimensional tables and fact table are filled .

• Fact table is kept inside the cube where as master table is kept outside the cube .
• Inorder to improve the processing performance , SAP has come up with a concept called SID (Surrogate ID)Technology .

SID concept applicable for only CHARACTERS

• Whenever we create a character info object, System will generate an additional table in database called as SID table
• Whenever we load master data , system going to generate unique numeric ID called SID in its corresponding SID table

refer below screen shot for more details

• In order to improve the analysis SAP has come up with the concept called Dimensional tables .

When we want to create a cube , we need to follow 2 rules

• We need to specify the number of dimensions required.
• For every dimension we specify system will generate one dimensional table within the cube .

In each table we can have 256 fields,

1 goes for Dimension ID

7 goes for Internal purpose

remaininig 248 Columns left

for 1 Dimension -- 248 Characters can be assigned.

like that for 16 dimensions , we can have 16*248 Characters .

A single transaction can be viewed in 248 ways

On what basis we  will decide whether to add characters in a single dimension table or in separate table for each characteristic.

All 1:1 , assign to one dimension

All 1:Many assign it to different dimensions

BIW 3.0,3.1,3.1C,3.5 ,BI7.0 & BW 7.3

below  2 schemas in data warehousing and are not related to SAP BW

SNOW FLAKE SCHEMA

Informatica built on Snow flake schema

HYBRID schema

SAP BW Training 4- BW Concepts Info Objects & types.Types of data and behavior of data

In Order to do modelling we should understand concepts first . After understanding the BW concepts , we will be explaining about BW database design . In this lesson I will be detailing about some of the concepts like Info Objects & types.Types of data and behavior of data

1. INFO OBJECTS :

Suppose I have data like below in R/3

fig 1

This data we need to extract to BW side . Your basic modelling starts with defining  Info object.

Case 1 :

Imagine we don't want price information in target , In this case we don't need fields 4 & 5 in above example .

Case 2 :

Suppose if you want to create a new field in target which calculates total revenue which is product of Revenue * Rev Unit (8 & 9) in above figure . At this time we have to create 1 extra info object for storing calculated totals .

2.TYPES OF INFO OBJECTS

Basically categorized in 4 types .

1. CHARACTERS
2. KEY FIGURES
3. UNIT CHARACTERS
4. TIME CHARACTERS

KEY FIGURES:

The objects which we analyse in the reports has to be defined as KEY FIGURES.These are measurable objects. Key figures are always Numeric. Its not that wherever you see number , we should define them as key figures . Suppose in the below table Customer Number , Though number cannot be defined as KEY FIGURE .

Fig 2

In the above example , can we compare CUST1 & CUST 2 -- No we cant

Can we compare MAT1 & MAT2 -- no we cant

Can we compare 200 & 100 in Price of material . yes we can . 200 is greater than 100 .So we define this type of fields as Key figures

Shall we measure unit AUD

QTY-- can be measured

Revenue-- can be measured.

All the objects which we can measure and analysed can be defined as KEY FIGURES.

In business Objects we will call these key figures as MEASURES.

KPI -- Key performance Indicators

What is my growth

what is my profit

CHARACTERS :

On what basis we analyse the key figure is nothing but   CHARACTER.

Lets us say we are analysing revenue , is this analysis complete with this .No , On what basis you are going to analyse KEY FIGURE .

In our above Fig 1 Customer Number & Material # should be defined as Characters

UNIT CHARACTERS:

It will give value for the key figures , Without the unit there is no value.Where ever we use key figure , unit character will be always following KEY FIGURE

In the fig 1 , Unit price , Qty Unit & Rev Unit should be defined Unit Characteristics

TIME CHARACTERS

These hold data related to date of transaction, year of transaction , Month of transaction.

In the fig 1, Date of Transaction should be defined as Time Characters

Take any application , we can categorise fields into the above 4 types

Lets take an example of Banks Monthly statement

Date of Transaction -- Time Char

INR/USD------------------ Unit Char

Your Account Number --- Character

Opening Balance /Closing balance -- Key Figure

3.TYPES OF DATA

In SAP terms , data is categorized into two types ,

1. MASTER DATA
2. TRANSACTIONAL DATA

MASTER DATA :

The details level of information of character Info Object is called as Master data.This data is nothing to do with occurrence of business. Master data will not change frequently .

Always remember , primary key should be character , Not key figure

Data form MARA , LFA1

Information of Customer

Information of Vendors

Information of Material

TRANSACTIONAL DATA :

Data related to occurrence of business is called as Transactional data . This data changes   frequently.

Lets take an example of bank balance , This gets updated when you deposit cash / withdraw cash .

4. BEHAVIOR OF DATA

a.) MASTER DATA:

Master data gives present truth , Data in the Master Data Overwrites .

Imagine Customer no & Address

b.) Transactional Data :

Data in the transaction table adds up . Transactional data gives the FACT truth.truth. Thats why we call transactional tables as FACT tables .

Will system allow to use KEY FIGURE in MASTER DATA ??

Answer :

Yes we can use ,

for example you are storing your salary details in Master data

Storing salary details in Transactional Tables

Storing Salary details in Master Table

new salary override old salary and you will be having the below record in Database

NON CUMULATIVE KEY FIGURES ;

These will not add up in the Transaction table

Storing material Price in Transaction table :

    

If we generate report , for FACT table entry it shows 250(100+150) and for Master table it shows 150

If you dont want to sum up these key figures , we should define them as Non Cumulative .

Take the first value / last value of the Month .

We should define them as Non Cumulative key figure last value with respect to year .Any Key figure you want to create in FACT table , By defaults the values sum up , If you don't want this behavior , you have to  define them as Non cumulative key figures .

Why can't we maintain them in Master table rather defining as” non cumulative key figures” ?

When we develop a report we always generate on transactional tables . To maintain History

STEPS OF MODELLING

• IDENTIFY THE NUMBER OF INFO OBJECTS TO BE CREATED IN BW
• IDENTIFY EACH INFO OBJECT AS WHAT TYPE IT HAS TO BE CREATED.
• IDENTIFY WHAT TYPE OF DATA YOU NEED TO EXTRACT