New Updated Microsoft MCSE 70-465 Real Exam Questions and Answers Download 111-120

Ensurepass

QUESTION 111

You are modifying a SQL Server Analysis Service (SSAS) cube.

 

The cube consist of a single measure group that contains the following measures:

 

clip_image002Total Quantity On Hand

clip_image002[1]Average Quantity On Hand

 

The measure group has a single partition that uses the MOLAP storage mode.

 

You need to modify the cube design to ensure that the Total Quantity On Hand measure is updated in real-time and that Average Quantity On Hand measure is updated hourly.

 

What should you do?

 

A.

Change the storage mode of the partition to use proactive caching with minimum latency.

B.

Create an XMLA script that will process the cube and then use SQL Server Agent to execute the script continuously.

C.

Create
a new measure group for the Average Quantity On Hand measure. Configure the storage mode for the new measure group’s partition to ROLAP.

D.

Create a new measure group for the Total Quantity On Hand measure. Configure the storage mode for the new measure group’s partition to ROLAP.

 

Correct Answer: D

 

 

QUESTION 112

You create a materialized reference dimension between a dimension named Regions and a measure group named Orders.

 

You use a table named Customers as a bridge as shown in the exhibit. (Click the Exhibit button.)

 

clip_image003

 

You need to ensure that data is updated to reflect any changes in the relationship. The solution must NOT require that the entire cube be reprocessed.

 

What should you reprocess?

 

A.

The Sales partition

B.

The Regions d
imension

C.

The Customers dimension

D.

The Orders partition

 

Correct Answer: C

 

 

QUESTION 113

HOTSPOT

You are designing a dimension named Employee for a SQL Server Analysis Services multidimensional project. The Employee dimension contains a DateOfBirth attribute and a MaritalStatus attribute. You need to minimize the amount of time required to process the cube. What should you do? To answer, select the appropriate relationship type for each attribute in the answer area.

 

clip_image004

clip_image005

 

Correct Answer:

clip_image006

 

 

QUESTION 114

DRAG DROP

You are developing a SQL Server Analysis Services (SSAS) tabular project. You need to add a calculated column to a table in the model. Which three actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)

 

clip_image008

 

Correct Answer:

clip_image010

 

 

QUESTION 115

You are developing a BI Semantic Model (BISM) based on a simple and small dataset sourced from SQL Server. The data size and complexity of the data relationships will not change. The model will be used to produce reports in Power View. The reports will show the relationship between product sales and rainfall over time. You need to use an appropriate project type. Which project types should you use? (Each answer presents a complete solution. Choose all that apply.)

 

A.

A tabular project that uses the In-Memory query mode

B.

A tabular project that uses the DirectQuery query mode

C.

A multidimensional project that uses the MOLAP storage mode and proactive cache

D.

A multidimensional project that uses the ROLAP storage mode and columnstore indexes

E.

A PowerPivot workbook that is deployed to Microsoft SharePoint Server 2010

 

Correct Answer: ABE

 

 

QUESTION 116

You work in the Business Intelligence (BI) department of a multinational company.

 

To share its sales data between the various subsidiaries, the company has requested a new corporate BI solution that meets the following requirements:

 

clip_image002[2]The solution must use SQL Server Analysis Services (SSAS) multidimensional or tabular Business Intelligence Semantic Model (BISM).

clip_image002[3]The model must incrementally add 10 million fact rows of sales data per month.

clip_image002[4]The model must be translated to English, German, Chinese, or Spanish based on users’ locale.

clip_image002[5]The model must be able to contain the most recent 36 months of data, in order to let users query the data.

 

You need to select the appropriate model type and partitioning strategy to meet the requirements.

 

What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

 

A.

Create and deploy a BISM multidimensional model with one partition for all of the data.

B.

Create and deploy a BISM tabular model with one partition for each of the 36 months.

C.

Create and deploy a BISM multidimensional model with one partition for each of the 36 months.

D.

Create and deploy a BISM tabular model with one partition for all of the data.

 

Correct Answer: C

 

 

QUESTION 117

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.

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.

B.

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

C.

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.

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.

 

Correct Answer: D

 

 

QUESTION 118

HOTSPOT

You are developing a SQL Server Analysis Services (SSAS) cube for the accounts department. You create a measure group named Exchange Rate that consists of measures pertaining to currency exchange rates. One of the measures in this group is named Average Rate and it will be used to report the average currency exchange rate over time. Currently the AggregationFunction property for the Average Rate measure is set to Sum. You need to ensure that Average Rate measure reports the average of the currency exchange rate over time. Which value should you select for the AggregationFunction property for the Average Rate measure? To answer, select the appropriate setting in the answer area.

 

clip_image011

 

 

clip_image012

 

Correct Answer:

clip_image013

 

 

 

QUESTION 119

HOTSPOT

You are developing a SQL Server Analysis Services (SSAS) cube. Revenue must be compared to a goal and described by a status and a trend. Revenue, goal, status, and trend will be defined by Multidimensional Expressions (MDX) expressions. You need to add the Revenue measure.

Which tab should you select? (To answer, select the appropriate tab in the answer area.)

 

clip_image014

 

Correct Answer:

clip_image015

 

 

QUESTION 120

You are designing a SQL Server Analysis Services (SSAS) cube based on a Microsoft Azure SQL Database data warehouse. You need to implement a degenerate dimension. What should you do?

 

A.

Use the fact table as the data source for the dimension.

B.

Create snowflake dimension tables based on normalized views of the fact table in the data source.

C.

Create a junk dimension table based on the fact table in the data source.

D.

Add a surrogate key to the fact table and use it as the degenerate dimension key.

 

Correct Answer: A

 

Instant Access to Download Latest Complete Collection of Microsoft MCSE 70-466 Real Exam

Try Microsoft MCSE 70-466 Free Demo