Data warehouse Star schema is a popular data warehouse design and dimensional model, which divides business data into fact and dimensions. In this model, centralized fact table references many dimension tables and primary keys from dimension table flows into fact table as a foreign key. This entity-relationship diagram looks star, hence the name star schema.
This model divides the business data into fact which holds the measurable data, and dimension that holds descriptive attributes related to the fact data.
For examples, fact data includes price, quantity, weight measurements and related dimension attributes example includes product color, sales person name, sales geographic locations.
Read:
Representation of Data warehouse Star Schema
To understand the this model, consider medicine sales table that holds the information about the sales quantity for each product, patient, hospital and on certain day. In this example, sales quantity is the measure and primary keys from Product, Date, hospital and Patient dimension flows into fact table. Fact table always holds measures and keys from dimension tables.
Below is the pictorial representation of the schema.
This schema is one of largely used data warehouse design methodology and popularly used in data warehouse applications, hence optimized for querying large data sets. This model is popularly used in OLAP, business intelligence and analytics applications, of course in ad-hoc queries.
Characteristics of Star Schema
- Dimensional Tables are not normalized.
- The dimension table should contain the set of attributes.
- Dimension table is joined to only Fact tables. They are not joined to each other.
- Fact table stores keys from dimension tables and measure.
- The Star schema is easy to understand and provides optimal disk usage.
Benifits of Data warehouse Star Schema
Star schema model are deliberatly denormalized to speed up the process. Below are the benefits ;
- Simple queries: Join conditions are simple joins in this schema
- Simplified business logics: This model simplifies common reporting business logics
- Performance: This model provides performance enhancements for reporting applications
- Fast aggregation
- Feeding cubes: This model is generally used by OLAP systems to build cubes. Building cube is very fast process
Disadvantages of Data warehouse Star Schema
Below are some of disadvantages;
- The main disadvantages is, that data integrity is not enforced as in OLTP databases
- This is not flexible inters of analytical application as normalized databases.
- This model don’t support many to many relationship between business entities. These types of relationships are simplified in star schema