For optimizing a complex semantic model and reducing the number of joins, which tables should be denormalized?

• A. dimension tables on the same level of granularity
• B. fact tables on the same level of granularity
• C. role-playing dimension tables
• D. Snowflaked dimension tables

Answer: (D)

Reducing the number of joins comes from flattening normalized dimensions so that attributes needed in queries can be read from a single table rather than multiple related ones. Snowflaked dimension tables are the normalized form of a dimension, split into sub-dimensions and linked together. By denormalizing these into one wide dimension, you eliminate the need to join across several sub-tables to assemble all the attributes that a query may request. This directly cuts the number of joins the query planner must perform, often speeding up complex semantic-model queries. Other options don’t address the underlying normalized structure as effectively: combining dimensional tables at the same granularity still leaves the deeper normalization intact; denormalizing fact tables or role-playing dimensions doesn’t tackle the multi-table dimension hierarchy in the same way.