Chapter 3: Extended Star Schema (SCN)

Chapter 3: Extended Star Schema

via Content in SCN by Sunny Reddy on 9/14/12

• Fact table consists of DIM ID and key figures.

• Every Info cube has two types of tables

1. Fact table
2. Dimension tables

• Info cube consists of one fact table (E and F), surrounded by multiple dimension tables.
• Maximum number of dimension tables in an info cube is 16 and the minimum number is 4.
• There are 3 system generated tables

1. Data Package dimension table (Technical dimension)
2. Time dimension
3. Unit dimension

• Maximum number of key figures in an info cube are 233
• Maximum number of characteristics in an info cube are 248

Advantages of Extended Star Schema:

• Faster loading of data/ faster access to reports
• Sharing of master data
• Easy loading of time dependent objects

Classical Star Schema:

• In classical star schema, the characteristic record is directly stored in DIM tables.
• For every Dimension table, a DIM ID is generated and it is stored in the fact table.

Differences between Classical Star Schema and Extended Star Schema:

• 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 Info cube and dimension table, inside Info cube).
• 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.