Star Schema & Extended Star Schema in SAP BI/BW

By : Jessica Blake Jessica Blake
Hours Updated

⚡ Smart Summary

Star schema and extended star schema in SAP BW organize data into fact and dimension tables, where the extended model links dimensions to master data through SID tables, enabling far greater analysis depth and master data reuse.

  • ⭐ Classical Star Schema: A central fact table surrounded by dimension tables that hold master data inside the cube.
  • 🔗 Extended Star Schema: SID tables link dimensions to master data stored outside the cube for reuse.
  • 📐 Analysis Depth: The extended model allows 16 dimensions times 248 SID tables for richer analysis.
  • 🔑 SID Tables: Surrogate IDs connect dimension tables to attribute, text, and master data tables.
  • 📊 Fact Table: A fact table holds key figures and supports up to 233 measurable key figures.
  • 🤖 AI Assistance: AI and SAP HANA optimize these models for fast, large-scale analytical queries.
Read More

Star Schema and Extended Star Schema in SAP BI/BW

What is a Schema?

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

What is a 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 InfoObjects (characteristics and key figures) and is structured according to the star schema. There is a large fact table with key figures for the InfoCube, surrounded by many dimension tables that form the star. A fact table can have a maximum of 16 dimensions.

The benefits of the star schema are easy slicing of data, easy understanding, and improved performance.

Star Schema in SAP BI/BW

What is an Extended Star Schema?

In the extended star schema, the “fact table” and the “master data table” are connected through an SID (Surrogate ID) table. The fact table and dimension table sit inside the cube, while master data sits outside it. It has an analyzing capacity of 16 × 248 (SID tables). The fact table is small and the dimension table is large, contrary to the classical star schema. Under the extended star schema, the dimension table does not contain master data.

Extended Star Schema in SAP BI/BW

The different components of an extended star schema are:

  1. The attribute table holds the attributes of master data.
  2. The SID table creates a unique SID (Surrogate ID) for every master data record.
  3. The dimension table creates a DimID for every unique combination of SIDs (a maximum of 248 SID characteristics can be accommodated in a dimension table).
  4. The text table holds descriptions of master data.
  5. The fact table contains a unique combination of DimIDs and key figures (a maximum of 233 key figures can be accommodated in a fact table).

Extended Star Schema in SAP BI/BW

Below is an example of how a fact table of an InfoCube looks.

Extended Star Schema in SAP BI/BW

Below are the dimension and SID tables.

Extended Star Schema in SAP BI/BW

Below are the InfoObject master data and text tables.

Extended Star Schema in SAP BI/BW

RELATED ARTICLES

InfoCube: Sample Extended Star Schema

The following is an example of an 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 of 3.
  2. The value of the fact table (“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 tables.

InfoCube Sample Extended Star Schema

FAQs

An SID (Surrogate ID) table assigns a short, unique integer key to each master data value. In the extended star schema, SID tables link dimension tables to master data, text, and attribute tables, improving join performance and reuse.

Master data is stored outside the InfoCube in separate attribute, text, and SID tables, not inside the dimension tables. This separation lets the same master data be reused across many InfoCubes instead of being duplicated.

A fact table stores the measurable key figures and the DimIDs that link to dimensions. A dimension table groups related characteristics and connects, via SID tables, to the master data that describes them. Fact tables are large; dimension tables are smaller.

Yes. SAP HANA and machine learning analyze query patterns to recommend aggregates, partitioning, and model changes. In SAP Datasphere and Analytics Cloud, AI further suggests models and surfaces insights from the underlying schema.

Machine learning reads the clean, structured fact and dimension data to train forecasting and anomaly-detection models. The star schema’s consistent keys and master data make it a reliable foundation for AI-driven analytics and planning.

Summarize this post with: