Microsoft 70-463 Exam Practice Test Instant Access

Question 1

Your team is creating SQL Server Integration Services (SSIS) packages that have several dependencies. The packages use parameters for configuration purposes.

Your company's IT policies include the following change control requirements:

After a package is ready for deployment, your team must hand over the process to junior IT personnel.

The process must guarantee that when a package has been validated, that same package (and all its dependencies) is deployed to production.

The process must be repeatable and reliable and must be executed with the least administrative and training effort by junior IT personnel.

You need to use the most appropriate deployment unit to satisfy the company policies, while minimizing issues such as incorrect version or configuration.

Which type of deployment unit should you use?

Adtsx

BSSIS deployment manifest

Cmsi

Dispac

Answer : D

Question 2

You are reviewing the design of an existing fact table named factSales, which is loaded incrementally from a SQL Azure database by a SQL Server Integration Services (SSIS) package each hour. The fact table has approximately 4 billion rows and is dimensioned by product, sales date, and sales time of day.

The database administrator is concerned about the rapid growth of the database and users experience poor reporting performance against this database. Reporting requirements have recently changed and the only remaining report that uses this fact table reports sales by product name, sale month, and sale year. No other reports will be created against this table.

You need to reduce the report processing time and minimize the growth of the database.

What should you do?

ACreate an indexed view over the fact table to sum orderTotal by month.

BCreate a view over the fact table to sum orderTotal by month.

CChange the granularity of the fact table to month.

DPartition the fact table by productKey.

Answer : C

Question 3

You are designing an extract, transform, load (ETL) process for loading data from a SQL Server database into a large fact table in a data warehouse each day with the prior day's sales data.

The ETL process for the fact table must meet the following requirements:

Load new data in the shortest possible time.

Remove data that is more than 36 months old.

Ensure that data loads correctly.

Minimize record locking.

Minimize impact on the transaction log.

You need to design an ETL process that meets the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

APartition the destination fact table by date. Insert new data directly into the fact table and delete old data directly from the fact table.

BPartition the destination fact table by date. Use partition switching and staging tables both to remove old data and to load new data.

CPartition the destination fact table by customer. Use partition switching both to remove old data and to load new data into each partition.

DPartition the destination fact table by date. Use partition switching and a staging table to remove old data. Insert new data directly into the fact table.

Answer : B

Question 4

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?

AInstall SQL Server 2012 Database Engine.

BInstall Microsoft SharePoint Server 2010 Enterprise Edition with PowerPivot.

CMake sure the server has at least 4 GB of RAM.

DInstall Microsoft Internet Explorer 6.0 SP1 or later.

Answer : A

Data Quality Server Minimum System Requirements

* SQL Server 2012 Database Engine.

* Memory (RAM):

Minimum: 2 GB

Recommended: 4 GB or more

Note: SQL Server Data Quality Services (DQS) is a new feature in SQL Server 2012 that contains the following two components: Data Quality Server and Data Quality Client.

Question 5

You are designing a SQL Server Integration Services (SSIS) data flow to load sales transactions from a source system into a data warehouse hosted on Windows Azure SQL Database. One of the columns in the data source is named ProductCode.

Some of the data to be loaded will reference products that need special processing logic in the data flow.

You need to enable separate processing streams for a subset of rows based on the source product code.

Which Data Flow transformation should you use?

AScript Component

BAudit

CDestination Assistant

DData Conversion

Answer : A

The Script component can be used as a source, a transformation, or a destination. This component supports one input and multiple outputs. Depending on how the component is used, it supports either an input or outputs or both. The script is invoked by every row in the input or output.

If used as a source, the Script component supports multiple outputs.

If used as a transformation, the Script component supports one input and multiple outputs.

If used as a destination, the Script component supports one input.

Question 6

Answer : G

Question 7

In your SSIS solution, you need to load a large set of rows into the database as quickly as possible.

The rows are stored in a delimited text file, and only one source column needs its data type converted from String (used by the source column) to Decimal (used by the destination column).

What control flow task would be most suitable for this operation?

AThe File System task would be perfect in this case, because it can read data from files and can be configured to handle data type conversions.

BThe Bulk Insert task would be the most appropriate, because it is the quickest and can handle data type conversions.

CThe data flow task would have to be used, because the data needs to be transformed before it can be loaded into the table.

DNo single control flow task can be used for this operation, because the data needs to be extracted from the source file, transformed, and then loaded into the destination table.At least three different tasks would have to be used--the Bulk Insert task to load the data into a staging database, a Data Conversion task to convert the data appropriately, and finally, an Execute SQL task to merge the transformed data with existing destination data.

Answer : C

References:

https://docs.microsoft.com/en-us/sql/integration-services/control-flow/data-flow-task

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