Microsoft 70-463 Exam Practice Test Instant Access

Question 1

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?

Ascript component

Bcustom component

CXML source

Dscript task

Answer : A

Question 2

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?

AEnable logging to an XML file in the package control flow for the Onlnformation event. After 10 executions, view the XML file.

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

CAfter 10 executions, in SQL Server Management Studio, view the Execution Performance subsection of the All Executions report for the package.

DEnable logging to an XML file in the package control flow for the OnPostExecute event. After 10 executions, view the XML file.

Answer : C

Question 3

Answer : B

http://msdn.microsoft.com/en-us/library/ms403356.aspx

Question 4

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?

AMicrosoft SharePoint Foundation 2010 SP1

BSQL Server 2012 Enterprise (64-bit) x64 on the database server

CSQL Server 2012 Data Center (64-bit) x64 on the database server

DSQL Server 2008 Enterprise (64-bit) x64 on the database server

Answer : B

* Master Data Services is a new feature introduced in SQL Server 2008 R2 and further enhanced in SQL Server 2012.

* SQL Server 2012 Enterprise features include Master Data Services:

Note:

* Microsoft SQL Server Master Data Services is a Master Data Management (MDM) product from Microsoft, which will ship as a part of the Microsoft SQL Server database.Originally code-named Bulldog, Master Data Services is the rebranding of the Stratature MDM product titled +EDM, which Microsoft acquired in June 2007. Master Data Services is architecturally similar to +EDM, with increased integration with other Microsoft applications as well as some new features. Master Data Services first shipped with Microsoft SQL Server 2008 R2.

Question 5

You are designing a SQL Server Integration Services (SSIS) package that uses the Fuzzy Lookup transformation.

The reference data to be used in the transformation changes with every package execution.

Common language runtime (CLR) integration cannot be enabled on the SQL Server database where the reference table is located.

You need to configure the Fuzzy Lookup transformation in the most efficient manner.

What should you do?

AExecute the sp_FuzzyLookupTableMaintenanceInvoke stored procedure.

BSelect the GenerateNewIndex option in the Fuzzy Lookup Transformation Editor.

CExecute the sp_FuzzyLookupTableMaintenanceUninstall stored procedure.

DSelect the GenerateAndPersistNewIndex option in the Fuzzy Lookup Transformation Editor.

Answer : B

Question 6

You are designing an enterprise star schema that will consolidate data from three independent data marts. One of the data marts is hosted on SQL Azure.

Most of the dimensions have the same structure and content. However, the geography dimension is slightly different in each data mart.

You need to design a consolidated dimensional structure that will be easy to maintain while ensuring that all dimensional data from the three original solutions is represented.

What should you do?

ACreate a junk dimension for the geography dimension.

BImplement change data capture.

CCreate a conformed dimension for the geography dimension.

DCreate three geography dimensions.

Answer : C

Question 7

You are reviewing the design of a student dimension table in an existing data warehouse hosted on SQL Azure.

The current dimension design does not allow the retention of historical changes to student attributes such as ParentOccupation.

You need to redesign the dimension to enable the full historical reporting of changes to multiple student attributes including ParentOccupation.

What should you do?

AAdd CurrentValue and PreviousValue columns to the student dimension.

BEnable Snapshot Isolation on the data warehouse.

CAdd an IsCurrent column to the student dimension.

DAdd StartDate and EndDate columns to the student dimension.
Adding a start and end date will give you this ability as when a record is inserted and given a start and end date, you'll have the ability to determine when they were active therefore giving you a retention of historical changes

Answer : D

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