nerdexam
Microsoft

70-466 · Question #146

A multidimensional SQL Server Analysis Services (SSAS) database will be tested next week. During the test period, users will access the database for 30 days. Multidimensional Expressions (MDX)…

The correct answer is D. Develop a SQL Server Integration Services (SSIS) package by using a Script task and then use Analysis Management Objects (AMO) to design the aggregations. Using a custom SSIS package with AMO allows programmatic, automated aggregation design across every partition in the solution, providing the most controlled and repeatable approach.

Configure and maintain an analysis services database

Question

A multidimensional SQL Server Analysis Services (SSAS) database will be tested next week. During the test period, users will access the database for 30 days. Multidimensional Expressions (MDX) queries generated during the test period must represent the variety of queries that will be used in the production environment. After testing completes, you need to implement aggregations for every partition in the solution while minimizing development effort. You need to ensure that the aggregations are optimal. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

Options

  • ASet up a query log and record all user queries during the test period. After completion of the test, use the Usage Based Optimization Wizard to define aggregations for each partition.
  • BDuring the test period, identify long-running queries by using SQL Server Profiler. Use those queries to design aggregations by using the DesignAggregations command in XML for Analysis (XMLA).
  • CDuring the test period, run SQL Server Profiler for 10 minutes every day and record all queries executed in those 10 minutes. Use the aggregation Design Wizard to design the aggregations.
  • DDevelop a SQL Server Integration Services (SSIS) package by using a Script task and then use Analysis Management Objects (AMO) to design the aggregations.

How the community answered

(22 responses)
  • A
    18% (4)
  • B
    14% (3)
  • C
    9% (2)
  • D
    59% (13)

Why each option

Using a custom SSIS package with AMO allows programmatic, automated aggregation design across every partition in the solution, providing the most controlled and repeatable approach.

ASet up a query log and record all user queries during the test period. After completion of the test, use the Usage Based Optimization Wizard to define aggregations for each partition.

While the Usage Based Optimization Wizard leverages query log data effectively, it typically requires manual execution per partition group and does not automate the process across all partitions in a single operation.

BDuring the test period, identify long-running queries by using SQL Server Profiler. Use those queries to design aggregations by using the DesignAggregations command in XML for Analysis (XMLA).

Running SQL Server Profiler and manually crafting XMLA DesignAggregations commands requires significant manual analysis per partition, which does not minimize development effort across the full solution.

CDuring the test period, run SQL Server Profiler for 10 minutes every day and record all queries executed in those 10 minutes. Use the aggregation Design Wizard to design the aggregations.

Recording only 10 minutes of queries per day limits the variety of MDX workload captured and does not guarantee that the aggregation design reflects the full range of production query patterns.

DDevelop a SQL Server Integration Services (SSIS) package by using a Script task and then use Analysis Management Objects (AMO) to design the aggregations.Correct

An SSIS Script task leveraging Analysis Management Objects (AMO) allows fully automated, scripted aggregation design that can iterate over every partition in the solution without manual wizard interaction per partition. AMO exposes the DesignAggregations API, enabling aggregation rules to be applied consistently and programmatically in a single deployment-ready package. This satisfies the requirement to implement aggregations for every partition while providing a repeatable, automated solution.

Concept tested: Automating SSAS aggregation design across all partitions with AMO

Source: https://learn.microsoft.com/en-us/analysis-services/multidimensional-models/aggregations-and-aggregation-designs

Topics

#SSAS aggregations#Usage Based Optimization#query log#partition optimization

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-466 Practice