27interviews.blogspot

This blog doesn't contain any copied information or copyright content from other sites. Every topic i presented in this blog is authored myself. Every time i tried to help the sapients to understand the concept in an easier way. Thats it. Have a Grt Day!

Translate

Star Schema Vs Extended Star Schema (Differences)


Correct me if there are any mistakes pls.
This question will be asked in every BI BW interview.
May be it may comes in second or third in ranking.



DIFFERENTIATING POINT
STAR SCHEMA
EXTENDED STAR SCHEMA
STRUCTURE
Fact table surrounded by dimension tables
Fact table surrounded by dimension tables, surrounded by SID table, surrounded by master data attributes texts & hierarchies.
DIM & MD TABLES
Dimension tables=master data table (i.e. Same)
Dimension table not =master data table(i.e. Not same)
Master data resides inside the fact table & dimension tables also reside inside the cube
Master data resides outside the InfoCube & dimension tables reside inside the InfoCube & contains no master data
GRAPHICAL REPRESENTATION
Star like
Not star, scattered
ANALYZING
16 angles
16*248(SID tables)
Common dimensions
Unit & time
Keys
Fact table: PK ( primary key)
Dimension table: FK ( foreign key)
PK, SID (SID tables provide the links between the md & the dim tables)
Size
Fact table : huge
Dimension table: small
Fact table: small
Dimension Table: Big
Tables
No SID tables, only fact tables & dimension tables
F table, E table, Packaged dimension, Time dimension, Unit dimension, N=user dimension tables. Extra tables that are SID tables, master data tables( P, Q, T, X, Y)
Multiple languages
No
Yes because hierarchies are present
Data retrieval & performance
Data retrieval & performance  slow as degraded uses alphanumeric keys in fact table
Data retrieval & performance fast as SID’S is a numeric key in fact table
Slowly Changing Dimension
Not applicable
Yes applicable time dependent
Query Performance
Slow or degraded
Enhanced performance, because aggregated KF are stored in their own fact tables
Dimension sharing
No
Yes shared by using SID tables
MD reusability
No
Yes, reusable
Aggregates
No
Yes, aggregates in its own table for faster access
SAP terminology
Simply cube
Simply InfoCube
Data Redundancy
Dim tables are specific to a fact table, i.e. Dimensions are not shared across other fact tables and Cubes. When another fact table needs the same dim data, another dim table that’s specific to a new fact table is needed. So this situation leads to slower performance, because data will be duplicated in several dim tables  instead of sharing data from one single table
There is no data redundancy because, there the dimension tables are shared from one single table.
Diagrammatic representation
Overall
See below Diagram 1
See below Diagram 2
Diagrammatic representation
Elaborated
See below Diagram 3
See below Diagram 4

Diagram 1 Star Schema:



Diagram 2 ESS Extended Star Schema:


Diagram 3 Star Schema elaborated:


Diagram 4 ESS Extended Star Schema elaborated:




For interview purpose you can explain as below:



STAR SCHEMA – a representation of a cube:
Star Schema is a model in which Fact table surrounded by dimension tables. Dimension tables=master data table (i.e. Same ) and Master data resides inside the fact table & dimension tables also reside inside the cube, appears as a star like in diagram or graphical representation. We can analyze the data in 16 angles and has two common dimensions, Unit dimension & time dimension, with two keys (primary key) PK for Fact table: and FK (foreign key) for Dimension table. Actually the size of the Fact table is huge and the size of Dimension table is small. Data retrieval and performance is slow as degraded because of alphanumeric keys in the fact table. So the query performance also will be slow. In SAP terminology, it is simply called CUBE. Important point here to consider in star schema is data redundancy or data duplication occurs, i.e. dimension tables are specific to a fact table, i.e. Dimensions are not shared across other fact tables and Cubes. When another fact table needs the same dimension data, another dim table that’s specific to a new fact table is needed. So this situation leads to slower performance, because data will be duplicated in several dim tables instead of sharing data from one single table. The major drawbacks include, star schema doesn’t support aggregates, hierarchies, multiple languages, slowly changing dimensions, and master data is not reusable and no drill down capabilities supported.

Extended Star Schema: E S S: Representation of an InfoCube:

Extended Star Schema is a model in which the fact table is surrounded by dimension tables, surrounded by SID tables, surrounded by Master Data attributes, texts and hierarchies. Dimension tables are not equal to master data tables, I.e. not same and master data resides outside the InfoCube and dimension tables resides inside the InfoCube and contains no master data, appears not as a star like, rather it looks like scattered in diagram or graphical representation. We can analyze the cube in 16 into 248 (SID tables) and has three common dimensions like packaged dimension, time dimension & unit dimension, with PK (primary key), SID tables which provide the links between the master data and the dimension table. Actually the size of fact table is small and dimension table is big. Here in the E S S apart from F, E, P, T, U, N tables, extra tables that are SID tables master data tables that are P, Q, T, X, Y are formed. Data retrieval and performance is fast as SIDs has numeric key in the fact table. So the query performance will be faster. In SAP terminology it is simply called InfoCube and the master data in InfoCube-E S S is reusable. And there will be no data redundancy because dimension tables are shared from one single table. Importance of E S S is that it supports aggregates as it has aggregates in its own table, supports Hierarchies, supports Multiple language, supports slowly changing dimensions, supports drill down capabilities & the master data is reusable.




Correct me if there are any mistakes pls.
There was an error in this gadget