Microsoft
70-467 · Question #164
70-467 Question #164: Real Exam Question with Answer & Explanation
The correct answer is D: Use the Usage-Based Optimization Wizard to create aggregations for the current partitions.. When SSAS query logs have been configured and queries are running slowly with no aggregations defined, the Usage-Based Optimization Wizard is the correct tool because it analyzes recorded query patterns to design targeted aggregations.
Question
You are redesigning a SQL Server Analysis Services (SSAS) database that contains a cube named Sales. Before the initial deployment of the cube, partition design was optimized for processing time. The cube currently includes five partitions named FactSalesl through FactSales5. Each partition contains from 1 million to 2 million rows. The FactSales5 partition contains the current year's information. The other partitions contain information from prior years; one year per partition. Currently, no aggregations are defined on the partitions. You remove fact rows that are more than five years old from the fact table in the data source and configure query logs on the SSAS server. Several queries and reports are running very slowly. You need to optimize the partition structure and design aggregations to improve query performance and minimize administrative overhead. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
Options
- AUse the Aggregation Design Wizard to create aggregations for the current partitions.
- BCombine all the partitions into a single partition.
- CCombine all the partitions into a single partition.
- DUse the Usage-Based Optimization Wizard to create aggregations for the current partitions.
Explanation
When SSAS query logs have been configured and queries are running slowly with no aggregations defined, the Usage-Based Optimization Wizard is the correct tool because it analyzes recorded query patterns to design targeted aggregations.
Common mistakes.
- A. The Aggregation Design Wizard creates aggregations based on statistical analysis of the cube structure rather than actual query patterns recorded in the query log, so it does not take advantage of the configured query logging.
- B. Combining all partitions into a single partition eliminates partition pruning and can increase processing time and memory pressure, directly contradicting the goal of improving query performance.
- C. This choice is identical to choice B and has the same problem - merging all partitions removes the ability to scope queries and processing to specific partitions, degrading rather than improving performance.
Concept tested. SSAS Usage-Based Optimization Wizard with query logs
Community Discussion
No community discussion yet for this question.