nerdexam
Microsoft

70-467 · Question #39

You are designing a fact table in a SQL Server database. The fact table must meet the following requirements: - Include a columnstore index. - Allow users to choose up to 10 dimension tables and up to

The correct answer is C. Design a fact table with 10 dimensional key columns and 5 measure columns.. A fact table with 10 dimensional key columns and 5 measure columns correctly reflects the stated requirements of up to 10 dimensions and up to 5 facts, optimized for columnstore-based aggregation at scale.

Design a data warehousing solution

Question

You are designing a fact table in a SQL Server database. The fact table must meet the following requirements:

  • Include a columnstore index.
  • Allow users to choose up to 10 dimension tables and up to five facts

at one time.

  • Maximize performance of queries that aggregate measures by using any

of the 10 dimensions.

  • Support billions of rows.
  • Use the most efficient design strategy.

You need to design the fact table to meet the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

Options

  • ADesign a fact table with 5 dimensional key columns and 10 measure columns.
  • BDesign a fact table with 5 dimensional key columns and 10 measure columns.
  • CDesign a fact table with 10 dimensional key columns and 5 measure columns.
  • DDesign a fact table with 10 dimensional key columns and 5 measure columns.

How the community answered

(26 responses)
  • A
    4% (1)
  • B
    4% (1)
  • C
    81% (21)
  • D
    12% (3)

Why each option

A fact table with 10 dimensional key columns and 5 measure columns correctly reflects the stated requirements of up to 10 dimensions and up to 5 facts, optimized for columnstore-based aggregation at scale.

ADesign a fact table with 5 dimensional key columns and 10 measure columns.

Reversing the counts to 5 dimensional keys and 10 measure columns fails to accommodate all 10 dimension tables, leaving 5 dimensions without a foreign key join path in the fact table.

BDesign a fact table with 5 dimensional key columns and 10 measure columns.

Identical to choice A, this layout with 5 dimensional key columns and 10 measure columns contradicts the requirement of supporting up to 10 dimensions and misrepresents the number of measure facts.

CDesign a fact table with 10 dimensional key columns and 5 measure columns.Correct

The requirements explicitly state up to 10 dimension tables and up to 5 facts, so the fact table must contain exactly 10 foreign key columns (one per dimension) and 5 measure columns to model all relationships without redundancy. A columnstore index on this layout efficiently compresses and batch-processes columnar segments, enabling high-performance aggregation queries across billions of rows. This standard star schema design is the most efficient strategy because every dimension can be joined and every measure can be aggregated directly from the fact table.

DDesign a fact table with 10 dimensional key columns and 5 measure columns.

Although textually identical to C, this choice is not designated as the best answer in this question's answer key.

Concept tested: Star schema fact table design with columnstore index

Source: https://learn.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-data-warehouse

Topics

#columnstore index#fact table design#dimension keys#data warehousing

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-467 Practice