Star Schema and Snowflake Schema
Star Schema
- This model graphically represents the STAR, so, it is named as Star Scheme
- In this Schema, Fact table is in the Center, and the dimensional tables are relationally linked to the fact table. It means, Dimensional tables are surrounded by the fact table.
- Fact tables, an Dimension tables are connected to each other by a 4-bye surrogate key
- Historical display of attributes is supported in the star schema
- The keyfigures and the 4-bye keys of the dimensions are included in Fact table.
- This enables the use of keys with few and short fields (usually 4 bytes), which results in better performance of the database.
- The dimension tables form the end’s of the star and contains characteristics and attributes.
- The Star schema is a simple, easy and effective concept, recognized by technical resources as well as by the business analysts.
- The central intersection entity (Sales order) defines a fact table. Attributes which forms 1:N relationship should be stored in the same dimensions (i.e. Cust and cust name, city.. ). Primary Key of Dimension table becomes foreign key of Fact table. As there is no correspondence to the time in E-R Model, time attributes (Day, Month, Year..) has to be introduced in Multi-dimensional model to cover the analysis needs.
Star Schema
Snowflake Schema
- lSnowflake schema is an enhancement of the Star schema with master data tables
- lIt allows for the attributes to display not only historically but also currently
- lAttributes can be stored not only in dimensions but also in master data tables, that are relationally linked to characteristics in the dimensions
- lFact table is connected to the dimension table through DIM ID, Dimension tables are connected to the master data table through SID’s
- lUnlike in normal Star schema, true characteristic value will no longer be found in Dimension tables, but in attached SID tables of the Infoobjects.
- lIn the below figure, Phone No can be added either to the customer dimension (Historic display) or to the Customer master data (current display). It is also possible to add the Phone no to both tables so that the user can choose between these alternatives in the data analysis
| Data Models | Performance | Complexity | Historicity | Current Display |
|---|---|---|---|---|
| Star Schema | Good | Medium | Supported | Not supported |
| Snowflake Schema | Good | High | Supported | Supported |
Visit my youtube channel, for SAP Videos. Subscribe and watch videos.
Very useful info for SAP BI freshers. Thanks for sharing.
Thanks,
Umashankar