All About Classical & Extended Star Schema

What is Schema ?
In database management system (DBMS), the schema represents relational database. It defines the tables, the fields in each table and the relationship between fields and tables. In other words schema is a collection of database objects, including tables, views, indexes and synonyms. Schemas are generally stored in a data dictionary.

What is Classical Star Schema?
A classical star schema is a multidimensional data model.It is based on a central fact table surrounded by several dimension tables in the shape of a star (hence the name).

An InfoCube consists of several Info-Objects (Characteristics and Key figures) and is structured according to the star schema. This means that there is a large fact table with key figures for the InfoCube, as well as many dimension table surround it structure which appears like star.

The benefits of star schema are slicing down, easy understanding of data and performance increase.

What is Extended Star Schema?
In extended star schema, “fact table” and “master data table” is connected through SID (Surrogate ID) table. In extended star schema , fact table and dimension table will be inside the cube. It has analyzing capacity of 16*248 (SID Table). Fact table is small and dimension table is huge contrary to Star Schema model. Under the extended star schema model, the dimension table do not contain master data

Following are the different components of an Extended Star Schema.

1. Attribute table holds the attribute of master data.
2. SID table creates the unique SID for (Surrogate Id) every master data records.
3. Dimension table creates the DimId for every unique combination of SID(Max 248 SID(characters) can accommodate in DIM table).
4. Text table hold description of master data.
5. Fact table contains unique combination of DIM ID and key figures (Max 233 key figures (measurable quantity) can accommodate in fact table).

Below is an example of how a fact table of an Infocube looks like.

Below are the Dimension and SID tables.

Below shown are the Info object master data and text table.

Infocube : Sample Extended Star Schema:
The below is an example of Infocube showing the Extended Star Schema.

Steps explaining the Extended Star Schema of an Infocube:

1. The Fact table of the InfoCube has a value 3.
2. The value of the fact table (i.e “3”) is mapped in the Dimension table.
3. The Dimension ID “3” has an SID mapped in the SID table.
4. The SID value is mapped with the Text and Master data table.