Online Access Free 070-466 Practice Test

You are creating a new report in SQL Server Report Builder. You add a Microsoft Azure SQL Database data source. Then you add a dataset that has four fields named Year, Country, Category, and Sales.
You must design a matrix as shown in the following table.

The category rows (the first three rows as shown in the diagram) must present total sales amount by category. The country rows {the next six rows as shown in the diagram) must present total sales amount by country. The total row must present the total sales for each year.
You add a matrix to the report. You add a grouping of the Category field on the rows and a grouping of the Year field on the columns.
You need to add the countries on the rows of the matrix.
Which Row Group option should you select when you add the group?

• A.Adjacent Above
• B.Parent Group
• C.Adjacent Below
• D.Child Group

You need to recommend a solution to meet the requirements for the
ManufacturingIssues.rdl report.
What is the best solution that you should include in the recommendation? More than one answer choice may achieve the goal. Choose the BEST answer.

• A.Add a dataset to the report that uses an ad hoc SQL statement. Configure the dataset to include the parameters required for the different views. Add a dataset for each parameter created. Configure each parameter to use the values in the dataset.
• B.Add a dataset to the report that uses an ad hoc SQL statement. Configure the dataset to include the parameters required for the different views. Update each parameter to use a set of values from Report Designer.
• C.Add a dataset to the report that uses an ad hoc SQL statement. Configure the dataset to include the parameters required for the different views. Use the default display for the parameters.
• D.Add a dataset to the report that uses a stored procedure. Configure the dataset to include the parameters required for the different views. Update each parameter to use a set of values from Report Designer.

HOTSPOT
You are developing a SQL Server Analysis Services (SSAS) cube that contains the data for a running team.
The data warehouse used by the cube contains the time durations of laps run by each runner on the team. The time durations are stored in seconds as an integer.
You need to build the following two measures in the cube:
* A measure named Measure1 that must contain the average time duration of the laps run by each runner.
* A measure named Measure2 that must contain the lap-time duration and the name of the runner who ran the fastest lap.
What should you do? To answer, select the appropriate Aggregation Function property for each measure in the answer area.

Correct Answer:

Explanation:

Note: For Measure1 we use Average, and for Measure2 Min to get the fastest time.

You are modifying a SQL Server Analysis Services (SSAS) cube that aggregates order data from a Microsoft Azure SQL Database database. The existing database contains a customer dimension.
The marketing team has requested that customer marketing categories be added to the database.
The marketing categories must meet the following requirements:
* A customer member must be able to belong to multiple category members.
* A category member must be able to group several customer members.
* The marketing team must be able to create new categories every month in the data source.
You need to implement the appropriate solution to meet the requirements while ensuring that the amount of development and maintenance time is minimized.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

• A.Add an attribute hierarchy for each marketing category to the customer dimension.
  Configure each hierarchy to have two members named Yes and No.
• B.Create a dimension named Marketing Category Name and then configure a regular relationship.
• C.Create a dimension named Marketing Category Name and then configure a many-to- many relationship.
• D.Add an attribute hierarchy named Marketing Category Name to the customer dimension.

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

• A.During 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).
• B.During 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.
• C.Set 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.
• 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.