Advantages of a Star Schema

A well-designed schema allows you to quickly understand, navigate and analyze large multidimensional data sets. The main advantages of star schemas in a decision support environment are:

Query Performance

Queries run faster against a star schema database than an OLTP system because the star schema has fewer tables and clear join paths. In a star schema design, dimensions are linked through the central fact table. Dimensions are linked with each other through one join path intersecting the fact table. This design feature enforces accurate and consistent query results.

Load Performance and Administration

The star schema structure reduces the time required to load large batches of data into a database. By defining facts and dimensions and separating them into different tables, the impact of a load operation is reduced. Dimension tables can be populated once and occasionally refreshed. New facts can be added regularly and selectively by appending records to a fact table.

Built-in Referential Integrity

A star schema is designed to enforce referential integrity of loaded data. Referential integrity is enforced by the use of primary and foreign keys. Primary keys in dimension tables become foreign keys in fact tables to link each record across dimension and fact tables.

Efficient Navigation Through Data

Navigating through data is efficient because dimensions are joined through fact tables. These joins are significant because they represent fundamental relationships of real business processes. You can browse a single dimension table in order to select attribute values to construct an efficient query.