nerdexam
Microsoft

70-467 · Question #115

You need to recommend a partitioning strategy that meets the performance requirements for CUBE1. What should you include in the recommendation?

The correct answer is A. Create separate measure groups for each distinct count measure. In SSAS multidimensional cubes, each distinct count measure must reside in its own measure group to allow independent partitioning and avoid severe query performance degradation.

Design a data warehousing solution

Question

You need to recommend a partitioning strategy that meets the performance requirements for CUBE1. What should you include in the recommendation?

Options

  • ACreate separate measure groups for each distinct count measure.
  • BCreateone measure group for all distinct count measures.
  • CCreatea separate dimension for each distinct count attribute.
  • DCreateone dimension for all distinct count attributes.

How the community answered

(31 responses)
  • A
    48% (15)
  • B
    29% (9)
  • C
    6% (2)
  • D
    16% (5)

Why each option

In SSAS multidimensional cubes, each distinct count measure must reside in its own measure group to allow independent partitioning and avoid severe query performance degradation.

ACreate separate measure groups for each distinct count measure.Correct

Distinct count measures in SSAS cannot share a partition with other measure types because the storage engine must scan entire partitions to compute them accurately. Placing each distinct count measure in its own measure group allows each to have its own dedicated partition scheme, limiting the scan scope per query and dramatically improving performance.

BCreateone measure group for all distinct count measures.

Grouping all distinct count measures into a single measure group means all of them share the same partitions, causing every distinct count query to scan data irrelevant to the requested measure.

CCreatea separate dimension for each distinct count attribute.

Creating separate dimensions per distinct count attribute addresses dimensional modeling, not the partition and measure-group performance problem inherent to distinct count aggregations.

DCreateone dimension for all distinct count attributes.

Consolidating distinct count attributes into one dimension does not affect how the measure group is partitioned and therefore does not address the query performance requirement.

Concept tested: SSAS distinct count measure group partitioning strategy

Source: https://learn.microsoft.com/en-us/analysis-services/multidimensional-models/mdx/mdx-data-manipulation-manipulating-data

Topics

#SSAS#distinct count#measure groups#partitioning

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-467 Practice