The star schema is the simplest data warehouse schema. It is called a star schema because its diagram resembles a star: the center (one or more fact tables) is directly joined to its points - the dimension tables.
A star schema is characterized by one or more very large fact tables that contain the primary information in the data warehouse and a number of much smaller dimension tables (or lookup tables), each of which contains information about the entries for a particular attribute in the fact table.
A star query is a join between a fact table and a number of lookup tables. Each lookup table is joined to the fact table using a primary-key to foreign-key join, but the lookup tables are not joined to each other.
A typical fact table contains keys and measures. For example, a simple fact table might contain the measure Sales, and keys Time, Product, and Market. In this case, there would be corresponding dimension tables for Time, Product, and Market. The Product dimension table, for example, would typically contain information about each product entry that appears in the fact table. A measure is typically a numeric or character column, and can be taken from a specified column from the fact table or calculated from two or more columns in one or a few fact tables.
A star join is a primary-key to foreign-key join between a fact table and dimension tables. The fact table normally has a primary-key composed of a few columns.
The main advantages of star schemas are that they:
- Provide a direct and intuitive mapping between business entities analyzed by end users and the schema design.
- Provide highly optimized performance for typical data warehouse queries.
The picture below is a graphical representation of a star schema.
