1/19
Looks like no tags are added yet.
What is a fact table?
A table that stores quantitative measures of a business event. Each row represents an occurrence of that event.
What are the key characteristics of a fact table?
Measures are typically numeric; it contains foreign keys to dimension tables; the grain must be clearly defined.
What are the three types of measures in a fact table?
Additive, semi-additive, and non-additive.
What is an additive measure?
A measure that can be summed across all dimensions.
What is a semi-additive measure?
A measure that can be summed across some dimensions, but not all dimensions.
What is a non-additive measure?
A measure that cannot be meaningfully summed across dimensions.
Can a fact table contain a transaction identifier?
Yes. A fact table may contain a transaction identifier in addition to foreign keys and measures.
Does a fact table contain only one numeric value?
No. The statement that a fact table contains only one numeric value is FALSE.
What is a dimension table?
A table that provides descriptive context—the who, what, where, when, and how—surrounding a business event.
What are the key characteristics of dimension attributes?
They are primarily descriptive and categorical and support filtering, grouping, and drill-down.
What type of keys should dimension tables use?
Surrogate keys rather than natural/business keys.
How are numeric values typically handled in dimensions?
Numeric values in dimensions are often converted into categorical ranges.
Do dimensions contain strategies or decisions under management control?
No. Dimensions do NOT contain strategies or decisions that are under management control.
What is a star schema?
One central fact table surrounded by denormalized dimension tables.
Why are dimensions denormalized in a star schema?
To optimize query performance at the cost of some data redundancy.
What type of primary key should every dimension have in a star schema?
A surrogate, system-generated integer primary key—not a natural/business key.
What typically forms the primary key of the fact table in a star schema?
A composite key made up of the foreign keys to the dimension tables, optionally including a degenerate dimension such as a transaction ID.
What is a constellation schema (galaxy schema/fact constellation)?
Multiple fact tables that share common dimension tables, enabling cross-subject analysis.
What is an example of a constellation schema?
A Sales fact table and Purchasing fact table sharing DimProduct and DimDate.
What is a snowflake schema?
A schema that normalizes dimension tables by factoring repeating groups into separate related tables, reducing redundancy but increasing joins and potentially hurting query performance.