nerdexam
Microsoft

70-467 · Question #164

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 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.

Design BI data models

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.

How the community answered

(37 responses)
  • A
    5% (2)
  • B
    14% (5)
  • C
    22% (8)
  • D
    59% (22)

Why each option

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.

AUse the Aggregation Design Wizard to create aggregations for the current partitions.

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.

BCombine all the partitions into a single partition.

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.

CCombine all the partitions into a single partition.

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.

DUse the Usage-Based Optimization Wizard to create aggregations for the current partitions.Correct

The Usage-Based Optimization Wizard reads the SSAS query log to identify which dimension combinations are queried most frequently, then designs aggregations specifically for those patterns - directly leveraging the query log configuration mentioned in the scenario and targeting the exact queries that are running slowly.

Concept tested: SSAS Usage-Based Optimization Wizard with query logs

Source: https://learn.microsoft.com/en-us/analysis-services/multidimensional-models/usage-based-optimization-wizard

Topics

#SSAS aggregations#usage-based optimization#partition design#query performance

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-467 Practice