Microsoft 70-463 Exam Practice Test Instant Access

Question 1

Answer : B

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

Question 2

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

You need to design a package to change a variable value during package execution by using the least amount of development effort.

What should you use?

AExpression task

BData Cleansing transformation

CFuzzy Lookup transformation

DTerm Lookup transformation

EData Profiling task

Answer : A

Question 3

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

The package uses a data flow task to source data from a SQL Server database for loading into a dimension table in a data warehouse.

You need to create a separate data flow path for data that has been modified since it was last processed.

Which data flow components should you use to identify modified data? (Each correct answer presents a complete solution. Choose all that apply.)

AMulticast

BData Conversion

CLookup

DSlowly Changing Dimension

EAggregate

Answer : A, C

A: The transformation that distributes data sets to multiple outputs.

The transformation that distributes data sets to multiple outputs.

C: Lookup Transformation

The transformation that looks up values in a reference table using an exact match.

Note:

* SQL Server Integration Services provides three different types of data flow components: sources, transformations, and destinations. Sources extract data from data stores such as tables and views in relational databases, files, and Analysis Services databases. Transformations modify, summarize, and clean data. Destinations load data into data stores or create in-memory datasets.

Incorrect:

Not B: Data Conversion Transformation

The transformation that converts the data type of a column to a different data type.

Not D: Slowly Changing Dimension Transformation

The transformation that configures the updating of a slowly changing dimension.

Not E: The Aggregate transformation applies aggregate functions, such as Average, to column values and copies the results to the transformation output. Besides aggregate functions, the transformation provides the GROUP BY clause, which you can use to specify groups to aggregate across.

Question 4

You are designing a data warehouse hosted on Windows Azure SQL Database. The data warehouse currently includes the dimUser and dimRegion dimension tables and the factSales fact table. The dimUser table contains records for each user permitted to run reports against the warehouse, and the dimRegion table contains information about sales regions.

The system is accessed by users from certain regions, as well as by area supervisors and users from the corporate headquarters.

You need to design a table structure to ensure that certain users can see sales data for only certain regions. Some users must be permitted to see sales data from multiple regions.

What should you do?

AFor each region, create a view of the factSales table that includes a WHERE clause for the region.

BCreate a userRegion table that contains primary key columns from the dimUser and dimRegion tables.

CAdd a region column to the dimUser table.

DPartition the factSales table on the region column.

Answer : B

Question 5

Answer : E

Question 6

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?

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

BConfigure the package to send you an email upon completion that includes information about the duration of the package. After 10 executions, view the emails.

CEnable logging to the Application Event Log in the package control flow for the OnInformation event. After 10 executions, view the Application Event Log.

DEnable logging to the Application Event Log in the package control flow for the OnPostExecute event. After 10 executions, view the Application Event Log.

Answer : A

The All Executions Report displays a summary of all Integration Services executions that have been performed on the server. There can be multiple executions of the sample package. Unlike the Integration Services Dashboard report, you can configure the All Executions report to show executions that have started during a range of dates. The dates can span multiple days, months, or years.

The report displays the following sections of information.

* Filter

Shows the current filter applied to the report, such as the Start time range.

* Execution Information

Shows the start time, end time, and duration for each package execution. You can view a list of the parameter values that were used with a package execution, such as values that were passed to a child package using the Execute Package task.

Question 7

Answer : J

References:

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

http://msdn.microsoft.com/en-us/library/ms180378(v=sql.110).aspx

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