Ensurepass

QUESTION 171

HOTSPOT

You are developing a SQL Server Integration Services (SSIS) package to implement an incremental

data load strategy. The package reads rows from a source system and compares them to rows in a

destination system. New rows will be inserted and changed rows will be updated. You have used

a Lookup transformation and a Conditional Split transformation. The Lookup transformation joins

the source and destination table on the business key, and includes all columns from the

destination table in the data flow output. The Conditional Split transformation inspects the

destination columns and directs data flow to either insert new records or update existing records.

You need to configure the Lookup transformation to ensure that all records flow to the

Conditional Split transformation, regardless of whether the rows match an existing row in the

destination table. Which setting should you select?

 

To answer, select the appropriate option in the answer area.

 

Hot Area:

clip_image001

 

Correct Answer:

clip_image002

 

 

QUESTION 172

HOTSPOT

You are using the Master Data Services (MDS) Add-in for Excel to configure the entities in a model. The model consists of two entities: one named Customer and one named State. You opened the

Customer entity. Currently, data stewards can enter any text value in the Customer entity’s State

attribute. You must restrict the entry of values in the State attribute to members defined in the

State entity. You need to configure the State attribute of the Customer entity. Which command

should you use?

 

To answer, select the appropriate command in the answer area.

 

Hot Area:

clip_image003

 

Correct Answer:

clip_image004

 

 

QUESTION 173

You develop a SQL Server Integration Services (SSIS) package that imports Windows Azure SQL

Database data into a data warehouse every night. The Windows Azure SQL Database data

contains many misspellings and variations of abbreviations. To import the data, a developer used

the Fuzzy Lookup transformation to choose the closest matching string from a reference table of

allowed values. The number of rows in the reference table is very large. If no acceptable match is

found, the Fuzzy Lookup transformation passes a null value. The current setting for the Fuzzy

Lookup similarity threshold is 0.50. Many values are incorrectly matched. You need to ensure that

more accurate matches are made by the Fuzzy Lookup transformation without degrading

performance. What should you do?

 

A.      Change the similarity threshold to 0.40.

B.      Decrease the maximum number of matches per lookup.

C.      Change the similarity threshold to 0.85.

D.      Increase the maximum number of matches per lookup.

 

Correct Answer: C

 

 

QUESTION 174

You maintain a SQL Server Integration Services (SSIS) package. The package was developed by

using SQL Server 2008 Business Intelligence Development Studio (BIDS). The package includes

custom scripts that must be upgraded. You need to upgrade the package to SQL Server 2012.

Which tool should you use?

 

A.      SQL Server Integration Services Deployment Wizard

B.      SQL Server Configuration Manager

C.      SSIS Upgrade Wizard in SQL Server Management Studio

D.      SSIS Upgrade Wizard in SQL Server 2008 BIDS

 

Correct Answer: C

 

 

QUESTION 175

You are preparing to install SQL Server 2012 Master Data Services (MDS). You need to ensure that

the database requirements are met. What should you install?

 

A.      Microsoft SharePoint Foundation 2010 SP1

B.      SQL Server 2012 Enterprise (64-bit) x64 on the database server

C.      SQL Server 2012 Data Center (64-bit) x64 on the database server

D.      SQL Server 2008 Enterprise (64-bit) x64 on the database server

 

Correct Answer: B

 

 

QUESTION 176

You are installing the Data Quality Server component of Data Quality Services. You need to

provision the hardware and install the software for the server that runs the Data Quality Server.

You must ensure that the minimum Data Quality Server prerequisites are met. What should you

do?

 

A.      Install SQL Server 2012 Database Engine.

B.      Install Microsoft SharePoint Server 2010 Enterprise Edition with PowerPivot.

C.      Make sure the server has at least 4 GB of RAM.

D.      Install Microsoft Internet Explorer 6.0 SP1 or later.

 

Correct Answer: A

 

QUESTION 177

You are implementing a SQL Server Integration Services (SSIS) package that imports Microsoft

Excel workbook data into a Windows Azure SQL Database. The package has been deployed to a

production server that runs Windows Server 2008 R2 and SQL Server 2012. The package fails

when executed on the production server. You need to ensure that the package can load the Excel

workbook data without errors. You need to use the least amount of administrative effort to

achieve this goal. What should you do?

 

A.      Install a 64-bit ACE driver and execute the package by using the 64-bit run-time option.

B.      Enable Address Windowing Extensions (AWE) for the local SQL Server instance.

C.      Replace the SSIS Excel source with a SSIS Flat File source.

D.      Install a 64-bit ACE driver and replace the Excel source with an OLE DB source.

 

Correct Answer: D

 

 

QUESTION 178

DRAG DROP

Your company is evaluating the data cleansing capabilities of SQL Server Data Quality Services

(DQS). A stand-alone server will be used to host DQS, as well as all related services necessary to

run this service. You need to install the DQS components on the server by using the minimum

amount of steps possible. You also need to grant permissions to a login named DataStewards to

enable DataStewards to edit and execute DQS projects. Which four 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.

 

Select and Place:

clip_image006

 

Correct Answer:

clip_image008

 

 

QUESTION 179

HOTSPOT

You are developing a SQL Server Integration Services (SSIS) package. You use a Data Profiling task

to examine the data from a source system. You need to establish:

 

Ÿ   The minimum and maximum dates for the datetime columns in the source data.

Ÿ   The minimum, maximum, and average values for numeric columns in the source data.

 

You need to use the appropriate profile type in the Data Profiling task. Which profile type should

you use?

 

To answer, select the appropriate profile type in the answer area.

 

Hot Area:

clip_image009

Correct Answer:

clip_image010

 

 

QUESTION 180

HOTSPOT

You are developing a SQL Server Integration Services (SSIS) package. The data source for the data

flow task is a table that has been configured as a change data capture (CDC) table. You are using a

CDC Source component to obtain the CDC data. The data source will be polled once per hour. The

data is updated with multiple important status changes per minute. For each captured data

change, the before and after values must be included. You need to configure the CDC Source

component. Which CDC processing mode should you select?

 

To answer, configure the appropriate option in the dialog box in the answer area.

 

Hot Area:

clip_image011

 

Correct Answer:

clip_image012

QUESTION 181

HOTSPOT

You are developing a SQL Server Integration Services (SSIS) package. OnError and OnWarning

events must be logged for viewing in the built-in SSIS reports by using SQL Server Management

Studio. You need to execute the package and minimize the number of event types that are logged. Which setting should you use?

 

To answer, change the appropriate setting in the answer area.

 

Hot Area:

clip_image013

 

Correct Answer:

clip_image014

 

 

QUESTION 182

You develop a SQL Server Integration Services (SSIS) package in a project by using the Project Deployment Model. It is regularly executed within a multi-step SQL Server Agent job. You make changes to the package that should improve performance. You need to establish if there is a trend in the durations of the next 10 successful executions of the package. You need to use the least amount of administrative effort to achieve this goal. What should you do?

 

A.      After 10 executions, view the job history for the SQL Server Agent job.

B.      After 10 executions, in SQL Server Management Studio, view the Execution Performance

subsection of the All Executions report for the project.

C.      Enable logging to the Application Event Log in the package control flow for the On

information event. After 10 executions, view the Application Event Log.

D.      Enable logging to an XML file in the package control flow for the OnPostExecute event. After

10 executions, view the XML file.

 

Correct Answer: B

 

 

 

 

 

QUESTION 183

You are performance tuning a SQL Server Integration Services (SSIS) package to load sales data

from a source system into a data warehouse that is hosted on Windows Azure SQL Database. The

package contains a data flow task that has 10 source-to-destination execution trees. Only five of

the source-to-destination execution trees are running in parallel. You need to ensure that all the

execution trees run in parallel. What should you do?

 

A.      Add OnError and OnWarning event handlers.

B.      Create a new project and add the package to the project.

C.      Query the ExecutionLog table.

D.      Add an Execute SQL task to the event handlers.

 

Correct Answer: A

 

 

QUESTION 184

You are the administrator of a server that hosts Data Quality Server for a large retail company.

The server had a hardware failure during business hours. You need to restore the server that

hosts Data Quality Server to another server. You have a recent backup of all the required

databases. What should you do? (Each correct answer presents part of the solution. Choose all

that apply.)

 

A.      Restore the DQS_MAIN, DQS_PROJECTS, and DQS_STAGING_DATA databases to another

server as soon as possible.

B.      Execute the DQS_MAIN.internal_core.RestoreDQDatabases stored procedure with the

appropriate parameter.

C.      Restore only the DQS_MAIN and DQS_STAGING_DATA databases to another server as soon

as possible.

D.      Execute the DQS_MAIN.internal_core.InitServer stored procedure with the appropriate

parameter.

 

Correct Answer: BD

 

 

QUESTION 185

You are designing an extract, transform, and load (ETL) solution that loads data into dimension

tables. The ETL process involves many transformation steps. You need to ensure that the design

can provide:

 

Ÿ   Auditing information for compliance and business user acceptance

Ÿ   Tracking and unique identification of records for troubleshooting and error correction

 

What should you do?

 

A.      Create a SQL Common Language Runtime (SQLCLR) component that records all

transformation steps in a Microsoft SharePoint list.

B.      Create a version control repository for the transformation steps in Team Foundation Server

(TFS).

C.      Develop a custom data lineage solution.

D.      Develop a Data Quality Services (DQS) solution.

 

Correct Answer: C

 

 

QUESTION 186

To support the implementation of new reports, Active Directory data will be downloaded to a

SQL Server database by using a SQL Server Integration Services (SSIS) 2012 package. The

following requirements must be met:

 

Ÿ   All the user information for a given Active Directory group must be downloaded to a SQL

Server table.

Ÿ   The download process must traverse the Active Directory hierarchy recursively.

 

You need to configure the package to meet the requirements by using the least development

effort. What should you use?

 

A.      script component

B.      custom component

C.      XML source

D.      script task

 

Correct Answer: A

 

 

QUESTION 187

HOTSPOT

You are a data warehouse developer responsible for developing data cleansing processes.

Duplicate employees exist in an employee dimension. You need to map, discover, and manage

domain values based on the employee dimension. Which Data Quality Services (DQS) option

should you use?

 

To answer, select the appropriate option in the answer area.

 

Hot Area:

clip_image015

 

Correct Answer:

clip_image016

 

 

QUESTION 188

HOTSPOT

You are developing a SQL Server Integration Services (SSIS) package. The package contains a user

defined variable named ©Queue which has an initial value of 10. The package control flow

contains many tasks that must repeat execution until the ©Queue variable equals 0. You need to

enable the tasks to be grouped together for repeat execution. Which item should you add to the

package?

 

To answer, select the appropriate item in the answer area.

 

Hot Area:

clip_image017

 

Correct Answer:

clip_image018

 

 

 

 

 

 

 

 

 

QUESTION 189

You are creating a Data Quality Services (DQS) solution. You must provide statistics on the

accuracy of the data. You need to use DQS profiling to obtain the required statistics. Which DQS

activity should you use?

 

A.      Cleansing

B.      Matching

C.      Knowledge Discovery

D.      Matching Policy

 

Correct Answer: A

 

 

QUESTION 190

You are developing a SQL Server Integration Services (SSIS) project to read and write data from a

Windows Azure SQL Database to a server that runs SQL Server 2012. The connection will be used

by data flow tasks in multiple SSIS packages. The address of the target Windows Azure SQL

Database will be provided by a project parameter. You need to create a solution to meet the

requirements by using the least amount of administrative effort. What should you do?

 

A.      Add a SQLMOBILE connection manager to each package.

B.      Add an ADO.NET project connection manager.

C.      Add a SQLMOBILE project connection manager.

D.      Add an ADO.NET connection manager to each data flow task.

E.       Add a SQLMOBILE connection manager to each data flow task.

F.       Add an ADO.NET connection manager to each package.

 

Correct Answer: A

 

Comments are closed.