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