70-467 · Question #30
An existing cube dimension that has 30 attribute hierarchies is performing very poorly. You have the following requirements: - Implement drill-down browsing. - Reduce the number of attribute…
The correct answer is D. Remove as many attribute hierarchies as possible from the dimension. Removing unnecessary attribute hierarchies from a dimension directly reduces the aggregation structures SSAS must build and maintain, improving both processing time and query performance.
Question
An existing cube dimension that has 30 attribute hierarchies is performing very poorly. You have the following requirements:
- Implement drill-down browsing.
- Reduce the number of attribute hierarchies but ensure that the
information contained within them is available to users on demand.
- Optimize performance.
You need to redesign the cube dimension to meet the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
Options
- Aset the AggregateFunction property to Sum on all measures. Use the SCOPE statement in a
- BSet the AttributeHierarchyOptimizedState property to FullyOptimized on the attribute
- CCreate user-defined hierarchies. For the attributes sourced by the levels of the user-defined
- DRemove as many attribute hierarchies as possible from the dimension.
How the community answered
(18 responses)- A6% (1)
- B11% (2)
- C17% (3)
- D67% (12)
Why each option
Removing unnecessary attribute hierarchies from a dimension directly reduces the aggregation structures SSAS must build and maintain, improving both processing time and query performance.
Setting AggregateFunction to Sum and using SCOPE statements controls how measures roll up, not the number or cost of attribute hierarchies, and does not address dimension browsing performance.
Setting AttributeHierarchyOptimizedState to FullyOptimized instructs SSAS to build more complete aggregation structures, which increases rather than decreases processing overhead and storage.
Creating user-defined hierarchies alone does not reduce attribute hierarchy count unless the source attribute hierarchies are also disabled; without disabling them, all original structures remain and performance does not improve.
Each enabled attribute hierarchy in SSAS requires its own aggregations, indexes, and storage built during cube processing; with 30 attribute hierarchies, this overhead is significant. Removing unneeded attribute hierarchies eliminates that processing cost and reduces the in-memory footprint, directly addressing the performance requirement while the underlying attribute data remains accessible through remaining hierarchies or member properties.
Concept tested: SSAS dimension attribute hierarchy reduction for performance optimization
Source: https://learn.microsoft.com/en-us/analysis-services/multidimensional-models/attribute-properties-define-member-groups
Topics
Community Discussion
No community discussion yet for this question.