Difference between Star and Snowflake Schema: Quiz and Flashcards

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

What is the primary purpose of the fact table in a star schema?

To contain the descriptive attributes needed for decision analysis and query reporting (correct)
To contain the dimension tables that are connected to the central fact table
To store the numerical values that are used for slicing and dicing the data
To provide classification and aggregation information about the data in the fact table

What is the relationship between the dimension tables and the fact table in a star schema?

The dimension tables have a one-to-one relationship with the fact table
The dimension tables have a many-to-many relationship with the fact table
The dimension tables have a one-to-many relationship with the fact table (correct)
The dimension tables are not directly connected to the fact table

What is the primary purpose of the dimension tables in a star schema?

To provide the central fact table with foreign keys that link the tables together
To contain the numerical values and performance measures that are used for decision analysis
To serve as the central table around which the star schema is designed
To store the classification and aggregation information about the data in the fact table (correct)

How does a snowflake schema differ from a star schema?

A snowflake schema has dimension tables that are further normalized into additional dimension tables (C) Signup and view all the answers

What is a multi-fact star model?

A star schema with multiple central fact tables (A) Signup and view all the answers

What is the primary purpose of the decision analysis attributes stored in the fact table of a star schema?

To support the analytical and reporting needs of the organization (B) Signup and view all the answers

What is the purpose of the key column in a fact table?

Point to the primary keys of dimensional tables for business analysis (C) Signup and view all the answers

What do fact tables in dimensional modeling mainly consist of?

Measures and keys (C) Signup and view all the answers

What is the grain in a measurement of an event?

The level of detail in the measurement event (B) Signup and view all the answers

How are fact tables related to dimensions in dimensional modeling?

One-to-many relationship (A) Signup and view all the answers

Which part of a fact table stores business activity measures like sales revenue?

Measure column (C) Signup and view all the answers

In a multi-fact star model, what do fact tables contain besides measures?

Actual business activity measures (C) Signup and view all the answers

What does a star schema represent?

Fact table surrounded by dimension tables (D) Signup and view all the answers

Which schema type requires many joins and has a very complex design?

Snowflake schema (C) Signup and view all the answers

What is a key characteristic of snowflake schema?

Normalized data structure (B) Signup and view all the answers

In multi-fact star models, what are the multiple facts typically associated with?

Store sales and store inventory (B) Signup and view all the answers

What is a common illustration that simplifies explanations of dimensional models?

Single fact table surrounded by dimensions (A) Signup and view all the answers

Which type of schema is good for datamarts with simple relationships like 1:1 or 1:many?

Star schema (B) Signup and view all the answers

Study Notes

Star Schema

A star schema consists of a central fact table connected to multiple dimension tables via foreign keys.
The fact table contains a large number of rows representing observed facts and external links.
The fact table contains descriptive attributes for decision analysis and query reporting.

Fact Table

A fact table primarily addresses what the data warehouse supports for decision analysis.
The fact table contains performance measures, operational metrics, aggregated measures, and other metrics needed to analyze organizational performance.
Examples of measures include sales volumes, customer retention rates, profit margins, production costs, and scrap rate.
A fact table is composed of two types of columns: keys and measures.
Key columns consist of foreign keys that point to the primary keys of associated dimensional tables.
Measure columns represent the actual business activity measures, such as sales revenue and order quantity.
Each measurement has a grain, which is the level of detail in the measurement of an event.

Dimension Tables

Dimension tables contain classification and aggregation information about the central fact rows.
Dimension tables contain attributes that describe the data contained within the fact table.
Dimension tables have a one-to-many relationship with rows in the central fact table.
Dimensions are used to slice and dice numerical values in the fact table to address ad hoc information needs.

Star Schema vs Snowflake Schema

A star schema has a single dimension table for each dimension.
A snowflake schema has a hierarchy of dimension tables for each dimension.
Star schema has a de-normalized data structure with high data redundancy.
Snowflake schema has a normalized data structure with low data redundancy.
Star schema is simple to maintain and suitable for datamarts with simple relationships.
Snowflake schema is more complex to maintain and suitable for core to simplify many-to-many relationships.

Multi-Fact Star Models

A multi-fact star model consists of multiple fact tables surrounded by shared dimension tables.
Each fact table represents a different business event, such as sales, expenses, and inventory.
Shared dimensions are referred to as conformed dimensions.
Examples of conformed dimensions include item, date, and buyer dimensions.

Studying That Suits You

Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

Description

Learn about the differences between Star and Snowflake schema types. Star schema consists of a fact table surrounded by dimension tables, while Snowflake schema involves a hierarchy of dimension tables. Star schema has less join compared to Snowflake schema.