Star schema

Key takeaways

Star schema belongs to astronomy; place it in that map before memorising details.
Learn the definition first, then one example that makes the definition concrete.
Connect Star schema to a quantity you can measure, compute or draw — that is where exam questions come from.
Reproduce the core statement of Star schema from memory before moving on to harder problems.

Reference excerpt

In computing, the star schema or star model is the simplest style of data mart schema and is the approach most widely used to develop data warehouses and dimensional data marts. The star schema consists of one or more fact tables referencing any number of dimension tables. The star schema is an important special case of the snowflake schema, and is more effective for handling simpler queries. The star schema gets its name from the physical model's resemblance to a star shape with a fact table at its center and the dimension tables surrounding it representing the star's points.

Model The star schema separates business process data into facts, which hold the measurable, quantitative data about a business, and dimensions which are descriptive attributes related to fact data. Examples of fact data include sales price, sale quantity, time, distance, speed and weight measurements. Related dimension attribute examples include product models, product colors, product sizes, geographic locations, and salesperson names. A star schema that has many dimensions is sometimes called a centipede schema. Having dimensions of only a few attributes, while simpler to maintain, results in queries with many table joins and makes the star schema less easy to use.

Fact tables Fact tables record measurements or metrics for a specific event. Fact tables generally consist of numeric values, and foreign keys to dimensional data where descriptive information is kept. Fact tables are designed to a low level of uniform detail (referred to as "granularity" or "grain"), meaning facts can record events at a very atomic level. This can result in the accumulation of a large number of records in a fact table over time. Fact tables are defined as one of three types:

Transaction fact tables record facts about a specific event (e.g., sales events) Snapshot fact tables record facts at a given point in time (e.g., account details at month end) Accumulating snapshot tables record aggregate facts at a given point in time (e.g., total month-to-date sales for a product) Fact tables are generally assigned a surrogate key to ensure each row can be uniquely identified. This key is a simple primary key.

Dimension tables Dimension tables usually have a relatively small number of records compared to fact tables, but each record may have a very large number of attributes to describe the fact data. Dimensions can define a wide variety of characteristics, but some of the most common attributes defined by dimension tables include:

Time dimension tables describe time at the lowest level of time granularity for which events are recorded in the star schema Geography dimension tables describe location data, such as country, state, or city Product dimension tables describe products Employee dimension tables describe employees, such as sales people Range dimension tables describe ranges of time, dollar values or other measurable quantities to simplify reporting Dimension tables are generally assigned a surrogate primary key, usually a single-column integer data type, mapped to the combination of dimension attributes that form the natural key.

Benefits Star schemas are denormalized, meaning the typical rules of normalization applied to transactional relational databases are relaxed during star-schema design and implementation. The benefits of star-schema denormalization are:

Simpler queries – star-schema join-logic is generally simpler than the join logic required to retrieve data from a highly normalized transactional schema. Simplified business reporting logic – when compared to highly normalized schemas, the star schema simplifies common business reporting logic, such as period-over-period and as-of reporting. Query performance gains – star schemas can provide performance enhancements for read-only reporting applications when compared to highly normalized schemas. Fast aggregations – the simpler queries against a star schema can result in improved performance for aggregation operations. Feeding cubes – star schemas are used by all OLAP systems to build proprietary OLAP cubes efficiently; in fact, most major OLAP systems provide a ROLAP mode of operation which can use a star schema directly as a source without building a proprietary cube structure.

Use and comparison with the snowflake schema A star schema denormalizes dimension attributes into single wide tables to improve understandability and reduce join complexity for analytic workloads. By contrast, a snowflake schema normalizes dimension hierarchies into multiple linked tables. Kimball recommends avoiding snowflaking unless there is a clear need (for example, extremely large dimensions) because it adds complexity for users and can hurt query performance. Star schemas align well with multidimensional/OLAP models commonly used in decision support.

Typical tables Dimensional modeling distinguishes a central fact table and surrounding dimension tables. Common fact table types in star schemas are transaction, periodic snapshot, and accumulating snapshot; frequently used conformed dimensions include date/time, product, customer, organization, and geography.

Query performance considerations Analytic queries over a star schema usually join one large fact table with a handful of relatively small dimensions; many DBMSs implement ‘‘star-join’’ optimizations for this pattern. Performance characteristics of such workloads are commonly studied using the Star Schema Benchmark (SSB).

Example

Consider a database of sales, perhaps from a store chain, classified by date, store and product. The image of the schema to the right is a star schema version of the sample schema provided in the snowflake schema article. Fact_Sales is the fact table and there are three dimension tables Dim_Date, Dim_Store and Dim_Product. Each dimension table has a primary key on its Id column, relating to one of the columns (viewed as rows in the example schema) of the Fact_Sales table's three-column (compound) primary key (Date_Id, Store_Id, Product_Id). The non-primary key Units_Sold column of the fact table in this example represents a measure or metric that can be used in calculations and analysis. The non-primary key columns of the dimension tables represent additional attributes of the dimensions (such as the Year of the Dim_Date dimension). For example, the following query answers how many TV sets have been sold, for each brand and country, in 1997:

… excerpt ends here. Continue reading the full article.

Illustrations

Worked examples

Example 1 — a first encounter with Star schema

Start with the simplest possible case. Write down what Star schema claims or describes in one sentence, then invent the smallest concrete situation in which that sentence is true. In astronomy, the smallest case is usually a single object, a single equation or a single measurement. Check that every symbol or term in your sentence has a meaning in that case.

Example 2 — changing one variable

Take the situation from Example 1 and change exactly one quantity: double it, halve it, or set it to zero. Predict what should happen to Star schema before you calculate. Comparing your prediction with the result is the fastest way to find out whether you understand the idea or only the words.

Example 3 — an exam-style question

Typical questions about Star schema ask you to (a) state it precisely, (b) apply it to given data, and (c) explain a limitation. Practise writing all three answers in under five minutes; the third part is what separates a full-mark answer from an average one.

Applications of Star schema

In research: Star schema appears in astronomy research whenever the underlying quantities have to be modelled precisely. Papers usually cite it as a starting assumption and then explore where it breaks down.
In technology and industry: Engineering practice reuses Star schema in design rules, simulations and safety margins. Knowing the idea lets you read a specification sheet and understand why the numbers look the way they do.
In the classroom: Star schema is common in secondary-school and first-year university syllabi. It links to neighbouring topics Data modeling, Data warehousing, so understanding it makes those chapters shorter.
In everyday life: Look for Star schema outside the textbook — in sport, cooking, traffic, electronics or the sky above you. An example you found yourself is remembered far longer than one you were given.

How to study Star schema in 20 minutes

Read the reference excerpt below once, without taking notes.
Close the page and write down what Star schema means in your own words.
Compare your version with the excerpt and mark what you missed.
Work through the three examples above with pen and paper.
Explain Star schema out loud to somebody else — or to Teacher Smith in the lgStudy chat.

Frequently asked questions

What is Star schema in simple terms?

In computing, the star schema or star model is the simplest style of data mart schema and is the approach most widely used to develop data warehouses and dimensional data marts. The star schema consists of one or more fact tables referencing any number of dimension tables.

Why does Star schema matter?

Because it connects several astronomy ideas at once: it gives you a definition you can apply, a quantity you can calculate, and a way to check whether a result is plausible.

How should I study Star schema?

Read the excerpt, restate it from memory, then work through the examples and applications listed on this page. The five-step study plan above takes about twenty minutes.

What does this page cover?

It gives you a compact reference excerpt plus original lgStudy explanations, examples, applications and study material on Star schema.