A BI team is designing a reporting model with one central sales fact table holding measures such as quantity and revenue, surrounded by date, store, product and customer tables. Each surrounding table is a single denormalized table joined directly to the fact table. Which design is this?
Select an answer to reveal the explanation.
Short Explanation
Picture the fact table in the middle with the dimension tables sticking out around it like the points of a star. If those dimensions were split into further sub-tables, it'd branch out like a snowflake instead. The clue you want here is single denormalized dimensions joined straight to the fact.
Full Explanation
A star schema is the common OLAP design in which a central fact table stores numeric measures, such as quantity and revenue, together with foreign keys to dimension tables, such as date, store, product and customer. Each dimension is a single denormalized table, so a typical analytical query joins the fact table directly to a handful of dimensions and aggregates the measures, which keeps queries simple and fast for reporting tools. A snowflake schema also uses fact and dimension tables, but normalizes dimensions into several related tables, for example product to category to department, adding joins; the stem rules this out by stating each dimension is a single table joined directly to the fact. A third normal form OLTP schema minimizes redundancy for transactional writes and spreads data across many related tables; it is not organized around a fact table of measures. A key-value data model stores opaque values retrieved by key and does not support relational joins between facts and dimensions. Exam caveat: star schemas deliberately accept redundancy in dimensions to reduce joins, which is the opposite trade-off from OLTP normalization. Operational check: confirm every dimension joins to the fact table through one key, with no dimension-to-dimension joins required.