18 Questions
What is the central component of a star schema?
The fact table
What is the primary purpose of the fact table in a star schema?
To store the decision analysis attributes
What is the relationship between the dimension tables and the fact table in a star schema?
One-to-many
Which of the following best describes the purpose of dimension tables in a star schema?
To provide classification and aggregation information
How are dimensions used in querying a star schema?
To aggregate the numerical values in the fact table
Which of the following is not a main characteristic of a star schema?
The dimension tables have a many-to-many relationship with the fact table
What is a key characteristic of a Star schema?
Fact table surrounded by dimension tables
Which type of schema design involves expanding dimension tables into multi-tables?
Snowflake Schema
What does a Multi-fact star model represent in dimensional modeling?
Multiple fact tables surrounded by dimensions
Why is a Star schema considered to have a simple design compared to a Snowflake schema?
Deals with less data redundancy
In the context of dimensional modeling, what is a common characteristic of fact tables?
Contain detailed business events such as sales and expenses
Why are conformed dimensions important in multi-fact star models?
To reduce the number of joins required in queries
What are the two types of columns found in fact tables in dimensional modeling?
Keys and measures
What is the grain of a measurement in a fact table?
The level of detail in the measurement
What does the key column in a fact table consist of?
Foreign keys to dimensional tables
In a dimensional model, where is ninety-percent of the data typically located?
Fact tables
Which type of relationship exists between fact tables and dimensions in dimensional modeling?
One-to-many
What is the purpose of the measures in a fact table?
Represent actual business activity
Study Notes
Star Schema
- A star schema consists of a central fact table surrounded by dimension tables, connected via foreign keys.
- The fact table contains a large number of rows corresponding to observed facts and external links.
- The fact table contains descriptive attributes for decision analysis and query reporting, and foreign keys to link to dimension tables.
Fact Table
- Contains performance measures, operational metrics, and aggregated measures (e.g., sales volumes, customer retention rates, profit margins).
- Primarily addresses what the data warehouse supports for decision analysis.
- Contains two types of columns: keys and measures.
Keys in Fact Table
- Consist of a group of foreign keys (FK) that point to the primary keys of dimensional tables.
- Enable business analysis.
Measures in Fact Table
- Represent actual measures of business activity (e.g., sales revenue, order quantity).
- Each measurement has a grain, which is the level of detail in the measurement of an event (e.g., unit of measure, currency used, ending daily balance).
- Examples: SalesQuantity, SalesAmount, ReturnAmount, ReturnQuantity, DiscountAmount, DiscountQuantity, and TotalCost.
Dimension Tables
- Contain classification and aggregation information about the central fact rows.
- Contain attributes that describe the data contained within the fact table.
- Address how data will be analyzed and summarized.
- Have a one-to-many relationship with rows in the central fact table.
Dimension Characteristics
- Represented by one table in a star schema.
- De-normalized data structure.
- High level of data redundancy.
- Maintenance is difficult.
- Good for datamarts with simple relationships (1:1 or 1:many).
Multi-fact Star Models
- Examples of dimensional models depict a single fact table surrounded by dimensions, but reality requires multiple facts.
- Multiple facts are often necessary (e.g., sales, expenses, inventory, and other business events).
- Shared dimensions are referred to as conformed dimensions.
Fact Table Characteristics
- A fact is also referred to as an organizational performance measure.
- Fact contains redundancy.
- Ninety-percent of the data in a dimensional model is typically located in the fact tables.
- Key design concerns include minimizing and standardizing data, and making it consistent.
Test your knowledge on the differences between Star and Snowflake Schema types in database design. Identify key characteristics such as data structure, complexity, and normalization levels.
Make Your Own Quizzes and Flashcards
Convert your notes into interactive study material.
Get started for free
