SAP BI/BW STAR SCHEMA Vs EXTENDED STAR SCHEMA

A Star Schema is the simplest form of  a dimensional model,in which the data is organized into facts and dimensions.

A Fact is an event which counted or measured , such as quantity and sales.

A Dimension contains a reference information about a fact such as date,product or customer.

An Info Cube is made up of a Fact table surrounded by Dimensions.A Fact table can have maximum of 16 dimensions.

One table consists  maximum of 16 primary keys so that we cannot create more than 16 Dimensions.

Let's look into the concept of Star Schema.

The above diagram resembles the concept of Star Schema.

We have an Customer (or Dimension) table ,Material table and Vendor tables and so on connected to Fact table inside the Cube.

Disadvantages Using  Star Schema :

• The Master data table is inside the cube,so the reusability is reduced or not possible.This means the master data tables are not shared across the other Fact tables(and Cubes).
• Performance is slow due to Alpha-numeric values.
• Dimension and master data tables are same.Since one Dimension table = one Master data table.
• The analysis can be done on 16 ways or 16 angles.Because the fact table directly connects to master data / dimension table.

When comes to an Extended Star Schema

Advantages Using Extended Star Schema :

• The Master data table is reusable as it is outside of the Info Cube.So it can be accessed ouside of the fact tables and infocubes.
• It has SID (Surrogate ID) table , Through this SID table the system generates the Numeric values for the each characteristic value.So performance is increased due to Numeric values.
• Master data tables and dimension tables are not same.
• The analysis can be done through 16 (Dimensions) * 248 (Characteristics of SID table).

      Just look how the analysis is improved..

1 table  can have 256 fields of which

                              -1 used by the dimension table

                              -7 used by the system for its internal purpose.

                       ---------------

Remain              248 fields.(For one table)

So for  16 Dimension tables ,  16 * 248 = 3968 ways.

Note : In the above Extended Star Schema diagram , I combined the two SID characteristics into single dimension table.It is better to combine when the customer and products are in 1 : 1 relationship.
It is not recommended when the customer and products are in 1:M relation.It is better to make multiple dimensional tables for each SID characteristic...