[Free] Download New Updated (October 2016) Microsoft 70-463 Real Exam 121-130

Ensurepass

QUESTION 121

You are creating a SQL Server Integration Services (SSIS) package that implements a Type 3 Slowly

Changing Dimension (SCD). You need to add a task or component to the package that allows you

to implement the SCD logic. What should you use?

 

A.     a Data Conversion component

B.      an Execute SQL task that executes a MERGE statement on the database

C.      a Merge component

D.     an Expression task

 

Correct Answer: C

 

 

QUESTION 122

You are designing a data warehouse for a fresh food distribution business that stores sates by individual product. It stores sales targets by product category. Products are classified into subcategories and categories. Each product is included in only a single product subcategory, and each subcategory is included in only a single category. The data warehouse will be a data source for an Analysis Services cube. The data warehouse contains two fact tables:

 

Ÿ   factSales, used to record daily sales by product

Ÿ   factProductTarget, used to record the monthly sales targets by product category

 

Reports must be developed against the warehouse that reports product sales by product, category and subcategory, and product sales targets. You need to design the product dimension. The solution should use as few tables as possible while supporting all the requirements. What should you do?

 

A.       Create two product tables, dimProduct and dimProductCategory. Connect factSales to

dimProduct and factProductTarget to dimProductCategory with foreign key constraints.

Direct the cube developer to use key granularity attributes.

B.       Create one product table, dimProduct, which contains product detail, category, and

subcategory columns. Connect factSales to dimProduct with a foreign key constraint. Direct

the cube developer to use a non-key granularity attribute for factProductTarget.

C.       Create three product tables, dimProduct, dimProductCategory, and dimProductSubcategory,

and a fourth bridge table that joins products to their appropriate category and subcategory

table records with foreign key constraints. Direct the cube developer to use key granularity

attributes.

D.       Create three product tables, dimProduct, dimProductCategory, and dimProductSubcategory.

Connect factSales to all three product tables and connect factProductTarget to

dimProductCategory with foreign key constraints. Direct the cube developer to use key

granularity attributes.

 

Correct Answer: B

 

 

QUESTION 123

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.

 

clip_image002

 

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?

 

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

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

C.      Change the granularity of the fact table to month.

D.     Partition the fact table by productKey.

 

Correct Answer: C

 

 

QUESTION 124

DRAG DROP

A SQL Server Integration Services (SSIS) project has been deployed to the SSIS catalog. The project includes a project Connection Manager to connect to the data warehouse. The SSIS catalog includes two Environments:

 

Ÿ   Test

Ÿ   Production

 

Each Environment defines a single Environment Variable named ConnectionString of type string.

The value of each variable consists of the connection string to the test or production data warehouses. You need to execute deployed packages by using either of the defined Environments. Which three 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_image004

 

Correct Answer:

clip_image006

 

 

QUESTION 125

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 SQL Azure. 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?

 

A.     Audit

B.      Source Assistant

C.      Script Task

D.     Conditional Split

 

Correct Answer: D

 

 

QUESTION 126

You develop a SQL Server Integration Services (SSIS) package that imports SQL Azure data into a data warehouse every night. The SQL Azure 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 Exhaustive property to True.

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: B

 

 

QUESTION 127

You install a SQL Server 2012 database engine instance on a production server. A month later, you

install SQL Server 2012 Integration Services (SSIS). You must develop an SSIS project and deploy it

to the server by using the Project Deployment model. Operations log records that are outside the

configured retention period must be cleaned automatically. You need to create the SSIS catalog

on the production server and ensure that the operations log cleaning requirement is met. What

should you do? (Each correct answer presents part of the solution. Choose all that apply.)

 

A.     Enable CLR Integration.

B.      Enable FILESTREAM with Full Access.

C.      Enable the Resource Governor.

D.     Change the recovery mode of the msdb database to FULL.

E.      Change the Server-wide Default Logging Level in SSISDB to Verbose.

F.      Start the SQL Server Browser service.

G.     Start the SQL Server Agent service.

 

Correct Answer: AG

 

 

QUESTION 128

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 Configuration Manager

B. &
nbsp;   
SQL Server Agent

C.      SSIS Upgrade Wizard in SQL Server Management Studio

D.     SQL Server DTExecUI utility (dtexecui.exe)

 

Correct Answer: C

 

 

QUESTION 129

A SQL Server Integration Services (SSIS) package was deployed two weeks ago with the Project

Deployment Model. Sometimes the package is started as part of a multistep SQL job. At other

times, the package is started manually by a database administrator by using the Object Explorer

in SQL Server Management Studio. You need to identify the authenticated user responsible for

starting the package each time it executes. How can you find this information?

 

A.     In the SSISDB.[catalog], query the .[executions] view.

B.      In the SSISDB.[catalog] , query the [event_messages] view.

C.      In SQL Server Management Studio, view the SQL Agent Job History.

D.< /font>     In SQL Server Management Studio, view the SQL Agent Error Log.

E.      In SQL Server Management Studio, view the SQL Server Log.

 

Correct Answer: A

 

 

QUESTION 130

HOTSPOT

You administer a Microsoft SQL Server 2012 database. The database contains a table that has the following definition:

 

clip_image008

 

You want to export data from the table to a flat file by using the SQL Server Import and Export

Wizard. You need to ensure that the following requirements are met:

 

The first row of the file contains the first row of data.

Each record is of the same length.

The date follows the U.S. date format.

The file supports international characters.

 

What should you do?

 

To answer, simply select the option or options in the answer area that you would configure.

 

Hot Area:

clip_image010

 

Correct Answer:

clip_image012

 

Free VCE & PDF File for Microsoft 70-463 Real Exam

Instant Access to Free VCE Files: MCSE|MCSA|MCITP…
Instant Access to Free PDF Files: MCSE|MCSA|MCITP…