Star Schema
A star schema is a type of BI data warehouse design that is a special version of a snowflake layout. It’s worth examining as a underlying data structure for a business intelligence implementation.
The name comes from the design layout view. Instead of a snowflake, where the lookup tables might be multiple and normalized, this structure only has one table for each hierarchy used in the system. The data stored is redundant and so this type of design is completely denormalized. The result looks like a star, hence the name. A more complex design might have multiple fact tables potentially, but the concept and idea is the same. Here’s a diagram to help show it visually:
The advantage to using this design is that you will have less tables in the database, which can reduce maintenance. More importantly, it reduces the number of joins used in the SQL, which can improve query performance. Certain bi software systems tend to perform better with a denormalized environment such as this, like Microstrategy. This is not always the case, though, if there is a very large lookup table going against a very large fact table. In those cases, a more normalized design may be better. A consolidated star schema concept can also be used. A level ID can be created that tells the sql engine which level the particular record is at in the lookup table. This can improve performance.
Another potential issue here is using this structure with aggregate tables. In order to properly return results from aggregate fact tables, the lookup tables need to be at the same level in the database. This requires a separate lookup table. This can be done for those separate fact tables that need it, and the layout can still retain the denormalized tables for quick joins to the main fact table.
Copyright 2011-2022 BusinessIntelligenceBase.com