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.