Microsoft 70-463 Implementing a Data Warehouse with Microsoft SQL Server 2012/2014 Exam Practice Test

Page: 1 / 14
Total 270 questions
Question 1

You are implementing a SQL Server Integration Services (SSIS) package that imports Microsoft Excel workbook data into a Microsoft Azure SQL Database database. The package has been deployed to a 64-bit production SQL Server.

The package fails when executed on the production server.

You need to ensure that the package can load the 32-bit Excel workbook data without errors.

What should you do?



Answer : D

If the computer has a 32-bit version of Office, then you have to install the 32-bit version of the components, and you also have to ensure that you run the package in 32-bit mode.

References:

https://docs.microsoft.com/en-us/sql/integration-services/connection-manager/connect-to-an-excel-workbook


Question 2

You have a SQL Server Integration Services (SSIS) package that is configured as shown in the exhibit. (Click the Exhibit button.)

You need to recommend changes to the package to reduce load times.

Which two changes should you recommend? Each correct answer presents part of the solution.



Answer : B, E

If the OLE DB destination uses a fast-load data access mode, you can specify the following fast load options in the user interface, OLE DB Destination Editor, for the destination:

References:

https://docs.microsoft.com/en-us/sql/integration-services/data-flow/ole-db-destination?view=sql-server-2017#fast-load-options


Question 3

You have a server named SQL1 that has SQL Server Integration Services (SSIS) installed. SQL1 has eight CPUs, 8 GB of RAM, RAID-10 storage, and a 10-gigabit Ethernet connection.

A package named Package1 runs on SQL1. Package1 contains 10 data tasks. Package1 queries 3 GB of data from the source system.

You review the performance statistics on SQL1 and discover that Package1 executes slower than expected.

You need to identify the problem that is causing Package1 to execute slowly.

Which performance monitor counter data should you review?



Answer : B

The Processor: % User Time value ''Corresponds to the percentage of time that the processor spends on executing user processes such as SQL Server.''

In other words, this is the percentage of processor non-idle time spent on user processes

If the Processor:% User Time high values are caused by SQL Server, the reason can be non-optimal SQL Server configuration, excessive querying, schema issues, etc. Further troubleshooting requires finding the process or batch that uses so much processor time

References:

https://www.sqlshack.com/sql-server-processor-performance-metrics-part-2-processor-user-time-processor-privilege-time-total-times-thread-metrics/


Question 4

You are the administrator for a Master Data Services (MDS) environment.

A user who is not an administrator must be able to access Master Data Manager.

You need to assign permissions to the user.

Which function should you use?



Answer : D

In the User and Group Permissions functional area, administrators can grant permission to functional areas, to attributes (on the Models tab), and to members (on the Hierarchy Members tab). Overlapping permissions are resolved to determine a user's permission to each individual attribute.

References:

https://docs.microsoft.com/en-us/sql/master-data-services/user-and-group-permissions-functional-area-master-data-manager?view=sql-server-2017


Question 5

You have a database named Sales and a data warehouse named DataW.

From Sales, you plan to bulk insert to a table in DataW that is more than 2 TB. The process will insert a minimum of 11 million rows simultaneously.

You need to identify which data storage strategy must be used to minimize load times.

Which data storage strategy should you identify?



Answer : D


Question 6

In your ETL process, there are three external processes that need to be executed in sequence, but you do not want to stop execution if any of them fails.

Can this be achieved by using precedence constraints? If so, which precedence constraints can be used?



Answer : B

References:

https://msdn.microsoft.com/en-us/library/cc505848.aspx


Question 7

Reporting from a Star schema is simpler than reporting from a normalized online transactional processing

(OLTP) schema.

What are the reasons for wanting simpler reporting? (Choose all that apply.)



Answer : A, D

References:

http://www.vertabelo.com/blog/technical-articles/data-warehouse-modeling-star-schema-vs-snowflake-schema


Page:    1 / 14   
Total 270 questions