[Free] Download New Updated (February 2016) Microsoft 70-458 Practice Tests 31-40

Ensurepass

QUESTION 31

You are creating a SQL Server Master Data Services (MDS) model for a company. The source data

for the company is stored in a single table that contains the manager-to-subordinate

relationships. You need to create a hierarchy representing the organizational structure of the

company. Which hierarchy type should you use?

 

A.     Natural

B.     Explicit

C.     Parent

D.     Recursive

Correct Answer: D

 

 

QUESTION 32

You are completing the installation of the Data Quality Server component of SQL Server Data

Quality Services (DQS). You need to complete the post-installation configuration. What should

you do?

 

A.     Run the Configuration component in the Data Quality Client.

B.     Install ADOMD.NET.

C.     Run the Data Quality Server Installer.

D.     Make the data available for DQS operations.

 

Correct Answer: C

 

 

QUESTION 33

You are the data steward for a Business Intelligence project. You must identify duplicate rows

stored in a SQL Server table and output discoveries to a CSV file. A Data Quality Services (DQS)

knowledge base has been created to support this project. You need to produce the CSV file with

the least amount of development effort. What should you do?

 

A.     Create an Integration Services package and use a Data Profiling transform.

B.     Create a custom .NET application based on the Knowledgebase class.

C.     Create a data quality project.

D.     Create a CLR stored procedure based on the Knowledgebase class.

E.      Create a Master Data Services (MDS) business rule.

 

Correct Answer: C

 

 

QUESTION 34

You are implementing the indexing strategy for a fact table in a data warehouse. The fact table is

named Quotes. The table has no indexes and consists of seven columns:

 

Ÿ   [ID]

Ÿ   [QuoteDate]

Ÿ   [Open]

Ÿ   [Close]

Ÿ   [High]

Ÿ   [Low]

Ÿ   [Volume]

 

Each of the following queries must be able to use a columnstore index:

Ÿ   SELECT AVG ([Close]) AS [AverageClose] FROM Quotes WHERE [QuoteDate] BETWEEN

‘20100101’ AND ‘20101231’.

Ÿ   SELECT AVG([High] – [Low]) AS [AverageRange] FROM Quotes WHERE [QuoteDate] BETWEEN

‘20100101’ AND ‘20101231’.

Ÿ   SELECT SUM([Volume]) AS [SumVolume] FROM Quotes WHERE [QuoteDate] BETWEEN

‘20100101’ AND ‘20101231’.

 

You need to ensure that the indexing strategy meets the requirements. The strategy must also

minimize the number and size of the indexes. What should you do?

 

A.       Create one columnstore index that contains [ID], [Close], [High], [Low], [Volume], and

[QuoteDate].

B.       Create three coiumnstore indexes:

One containing [QuoteDate] and [Close]

One containing [QuoteDate], [High], and [Low]

One containing [QuoteDate] and [Volume]

C.       Create one columnstore index that contains [QuoteDate], [Close], [High], [Low], and

[Volume].

D.       Create two columnstore indexes:

One containing [ID], [QuoteDate], [Volume], and [Close]

One containing [ID], [QuoteDate], [High], and [Low]

 

Correct Answer: C

 

 

QUESTION 35

You are designing a data warehouse with two fact tables. The first table contains sales per month

and the second table contains orders per day. Referential integrity must be enforced declaratively. You need to design a solution that can join a single time dimension to both fact tables. What

should you do?

 

A.     Join the two fact tables.

B.     Merge the fact tables.

C.     Create a time dimension that can join to both fact tables at their respective granularity.

D.     Create a surrogate key for the time dimension.

 

Correct Answer: C

 

 

 

 

 

 

 

QUESTION 36

DRAG DROP

You are building a SQL Server Integration Services (SSIS) package to load product data sourced

from a SQL Azure database to a data warehouse. Before the product data is loaded, you create a

batch record by using an Execute SQL task named Create Batch. After successfully loading the

product data, you use another Execute SQL task named Set Batch Success to mark the batch as

successful.

 

clip_image002

 

You need to create and execute an Execute SQL task to mark the batch as failed if either the

Create Batch or Load Products task fails. Which three steps 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.

 

Select and Place:

clip_image004

 

Correct Answer:

clip_image006

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

QUESTION 37

DRAG DROP

You are designing a SQL Server Integration Services (SSIS) package. The package moves

order-related data to a staging table named Order. Every night the staging data is truncated and

then all the recent orders from the online store database are inserted into the staging table. Your

package must meet the following requirements:

 

Ÿ   If the truncate operation fails, the package execution must stop and report an error.

Ÿ   If the Data Flow task that moves the data to the staging table fails, the entire refresh

operation must be rolled back.

Ÿ   For auditing purposes, a log entry must be entered in a SQL log table after each execution of

the Data Flow task.

 

The TransactionOption property for the package is set to Required. You need to design the

package to meet the requirements. How should you design the control flow for the package?

 

To answer, drag the appropriate setting from the list of settings to the correct location or

locations in the answer area.

 

Select and Place:

clip_image008

 

Correct Answer:

clip_image010

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

QUESTION 38

DRAG DROP

You develop a SQL Server Integration Services (SSIS) project by using the Project Deployment

model. The project contains many packages. It is deployed on a server named Development. The

project will be deployed to several servers that run SQL Server 2012. The project accepts one

required parameter. The data type of the parameter is a string. A SQL Agent job is created that

will call the master.dtsx package in the project. A job step is created for the SSIS package. The job

must pass the value of an SSIS Environment Variable to the project parameter. The value of the

Environment Variable must be configured differently on each server that runs SQL Server. The

value of the Environment Variable must provide the server name to the project parameter. You

need to configure SSIS on the Development1 server to pass the Environment Variable to the

package. Which four actions should you perform in sequence by using SQL Server Management

Studio?

 

To answer, move the appropriate actions from the list of actions to the answer area and arrange

them in the correct order.

 

Select and Place:

clip_image012

 

Correct Answer:

clip_image014

QUESTION 39

DRAG DROP

You are developing a SQL Server Integration Services (SSIS) package to insert new data into a data

mart. The package uses a Lookup transformation to find matches between the source and

destination. The data flow has the following requirements:

 

Ÿ   New rows must be inserted.

Ÿ   Lookup failures must be written to a flat file.

 

In the Lookup transformation, the setting for rows with no matching entries is set to Redirect

rows to no match output. You need to configure the package to direct data into the correct

destinations. How should you design the data flow outputs?

 

To answer, drag the appropriate transformation from the list of answer options to the correct

in the answer area.

 

Select and Place:

clip_image016

 

Correct Answer:

clip_image018

 

 

QUESTION 40

You are editing a SQL Server Integration Services (SSIS) package that contains three Execute SQL

tasks and no other tasks. The three Execute SQL tasks modify products in staging tables in

preparation for a data warehouse load. The package and all three Execute SQL product tasks have

their TransactionOption property set to Supported. You need to ensure that if any of the three

Execute SQL product tasks fail, all three tasks will roll back their changes. What should you do?

 

A.     Change the TransactionOption property of the package to Required.

B.     Change the TransactionOption property of all three Execute SQL product tasks to Required.

C.     Move the three Execute SQL product tasks into a Foreach Loop container.

D.     Move the three Execute SQL product tasks into a Sequence container.

 

Correct Answer: A

 

Fr
ee VCE & PDF File for Microsoft 70-458 Real Exam


Instant Access to Free VCE Files: MCSE|MCSA|MCITP…
Instant Access to Free PDF Files: MCSE|MCSA|MCITP…