A Star Schema is one of the simplest data warehouse schema where data is manipulated into facts and dimensions.
This blog post will be an overview of what the star schema and a deeper dive into facts and dimensions will follow.
So, what exactly is a star schema?
The picture above provides a basic illustration of what the schema is. At the centre lies the Fact Table and the Dimension Tables branch out from the middle, forming the points of the star. The simplicity in the design makes it easily understandable from a quick glance and is highly regarded and utilised because of this.
So apart from being easily interpreted, what are the other benefits?
Star schemas denormalized. This allows for:
- Simple queries – Joins between the fact and dimension tables are usually done through a foreign key relationship and hence simpler.
- Fast aggregation – This is furthered by the simpler queries.
- Query performance – Again, simpler queries leads to more efficient processing, hence better performance.
- Simplified business logic – Deriving core business logic is easier with a star schema as it is in an easy to understand structure. It shines bright in circumstances where business reporting is frequent.
As with anything, there are also disadvantages to star schemas as well.
Some of the disadvantages include:
- Lessened data integrity – Data integrity is not strictly enforced as the star schema is highly denormalized. In the process of trying to improve read performance of a database, write speed performance is sacrificed in the process. A lot of redundant data can end up being added to the database due to denormalization.
- Limitation in more complex analytics – Star schemas are purpose built, meaning that queries which follow the same business logic are very easily executed. However, it does not offer the flexibility for querying more complex queries.
In summary, star schemas are very to understand, and query efficiency is a flow on effect from this. Because of its nature, it is very commonly used and is also widely supported by a large array of business intelligence tools. Keep on shining bright, star schemas.