Classic and Extended Star Schema Comparisons

In Classic star schema, dimension and master data table are same. But in Extend star schema, dimension and master data table are different. (Master data resides outside the Infocube and dimension table, inside Infocubecube).

In Classic star schema we can analyze only 16 angles (perspectives) whereas in extended star schema we can analyze in 16*248 angles. Plus the performance is faster to that extent.

Below are some of the basic differences between the two.

Classic Star Schema               SAP BW Star Schema

Cube                                         InfoCube

Fact Table                                 Key Figure or KPI

Dimension Attribute                   Characteristic, Attributes, Hierarchy Node

Dimension Table                        Dimension Table, Master Data Table, External Table, SID Table

-                                                Standard Business Content

-                                                Hierarchies

-                                                MultiCube

-                                                Remote Cube

Advantages of Classic Star Schema

Data access runs performantly due to the small number of joining operations.

There are only join operations between the fact tables and the involved dimension tables.

Disadvantages of Classic Star Schema

Redundant entries exist in the dimension tables

Historization of dimensions is not easy to model. The dimension changing may be done slowly.

No multi language capability

It is difficult to model some hierarchy’s dimension.

Query performance is also made worse, since aggregates and fact data are stored in the same table (fact table).

Advantages of the SAP BW Star Schema

Faster access to data than via long alpha-numeric keys. SAP BW use automatically generated INT4 keys for SID and Dimension ID

Can model in easy way: Historizing, multi-lingual, and shared dimensions. It is happen because of the excavation of master data from the dimension tables using the SID technique.

The query performance is improved here as aggregated key figures can be stored in their own fact tables.

More About Star Schema

SAP BW Star schema is based on the extended of the classic star schema (aka “snowflake” schema). The enhancement comes from the fact that the dimension table does not contain master data information. This master data information is stored in separate tables, called master data tables. In SAP BW Star Schema, the distinction is made between two self-contained areas: infocube and surrogate ID (SID) tables.

Infocube

Infocubes are the central objects on which reports and analysis are based in SAP BW. It describes a self-contained data set within a business area, for which we can define queries.

Infocube consist of a central fact table and several surrounding dimension tables. In SAP BW star schema , the facts in a fact table refers to key figure and the dimension attributes refer to characteristics.

In contrast to classic star schema concepts, characteristics are not component the dimension tables since the characteristic values are not stored in the dimension tables. They are stored in master data tables. There are foreign keys that replace the characteristics as the component of dimension table, i.e.: SID stand for Surrogate ID. In picture above, these keys are given the prefix SID_.

Each Dimension table has a generated primary key, called the dimension key. (In Picture above, the keys are given the prefix DIM_ID_..). As the classic star schema, the primary key of the fact table is made up of dimension keys.

Master data Table or Surrogate ID (SID) Table

In SAP BW, additional information about characteristic is referred to as master data. Master data information is stored in separate tables called master data tables. There are 3 types of master data, i.e.: Attributes, Text and Hierarchies.

Here, SID tables play an important role in linking the data warehouse information structured to the subject-oriented infocubes. The master data tables, text tables, and hierarchy tables are not directly linked to the associated dimension tables. These tables are infocube independent and joined with the dimension tables using SID table. The SID therefore provides the link to the dimension tables.

The connection between Infocube and Master Data Tables

Picture above illustrates the connection between master data table and infocube. Master data tables are connected to an infocube by way of the SID tables. The picture above also explains that in SAP BW star schema, the master data is independent infocube and can be used by several infocubes at the same time.

Extended Star Schema

The BW extended star schema differs from the basic star schema, in case of extended star schema, we will have Fact table connected to the Dimension table and the Dimension table is connected to the SID table and SID table is connected to the master data tables.

Fact Table and Dimension table will be inside the cube.

SID table and Master data tables are outside the cube.

One Fact table can get connected to 16 Dimension tables, one Dimension table can be assigned with maximum of 248 SID tables (248 characteristics).

When we load Transaction data into InfoCube, System generates DIM ID based on the SID’s and uses the Dim ID’s in the Fact Table.

Each Characteristic can have its own master data tables (ATTR, TEXT, HIER). Attribute Table is used to store all the attribute data, Text table is used to store the description in multiple languages, Hier table is used to store the Parent-Child data.

Fact Table

Fact Table will have Dimension ID’s and Key figures.

Maximum DIM ID’s – 16

Maximum KeyFigure – 233

The Dimension ID’s in the Fact Table is connected to the Dimension Table.

Fact Table must have at least one Dimension ID.

Dimension Table

Dimension Table contains Dimension ID and SID columns.

One column is used for Dimension ID.

We have maximum of 248 SID Columns.

We can assign maximum of 248 characteristics to one dimension.