70-463 Exam Questions - Online Test

70-463 Premium VCE File

Learn More 100% Pass Guarantee - Dumps Verified - Instant Download
150 Lectures, 20 Hours

Exam Code: 70 463 pdf (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Implementing a Data Warehouse with Microsoft SQL Server 2012
Certification Provider: Microsoft
Free Today! Guaranteed Training- Pass 70 463 pdf Exam.

Q61. You are developing a SQL Server Integration Services (SSIS) project to read and write data from a Windows Azure SQL Database database to a server that runs SQL Server 2012. 

The connection will be used by data flow tasks in multiple SSIS packages. The address of the target Windows Azure SQL Database database will be provided by a project parameter. 

You need to create a solution to meet the requirements by using the least amount of administrative effort. 

What should you do? 

A. Add a SQLMOBILE connection manager to each package. 

B. Add an ADO.NET project connection manager. 

C. Add a SQLMOBILE project connection manager. 

D. Add an ADO.NET connection manager to each data flow task. 

E. Add a SQLMOBILE connection manager to each data flow task. 

F. Add an ADO.NET connection manager to each package. 

Answer: A 

Q62. You are a data warehouse developer responsible for developing data cleansing processes. 

Duplicate employees exist in an employee dimension. 

You need to map, discover, and manage domain values based on the employee 

dimension. 

Which Data Quality Services (DQS) option should you use? (To answer, select the appropriate option in the answer area.) 

Answer: 

Q63. You are implementing a SQL Server Integration Services (SSIS) package that imports Microsoft Excel workbook data into a Windows Azure SQL Database database. The package has been deployed to a production server that runs Windows Server 2008 R2 and SQL Server 2012. 

The package fails when executed on the production server. 

You need to ensure that the package can load the Excel workbook data without errors. You need to use the least amount of administrative effort to achieve this goal. 

What should you do? 

A. Install a 64-bit ACE driver and execute the package by using the 64-bit run-time option. 

B. Enable Address Windowing Extensions (AWE) for the local SQL Server instance. 

C. Replace the SSIS Excel source with a SSIS Flat File source. 

D. Install a 64-bit ACE driver and replace the Excel source with an OLE DB source. 

Answer: D 

Explanation: XLSX files, the new file type in Excel 2007/2010 can’t be open with Excel Source/Destination in SSIS. To use XLSX in SSIS we need to install ACE driver and use OLE DB Source/Destination to read read/write data in XLSX files. 

ACE is a set of components that facilitate the transfer of data between existing Microsoft Office files such as Microsoft Office Access (*.mdb and *.accdb) files and Microsoft Office Excel 7 (*.xls, *.xlsx, and *.xlsb) files to Microsoft SQL Server. If the SQL Server or your development box does not have ACE driver then install that 1st. Note: 

* The most efficient way to link to SQL Server is using an OLEDB connection manager. 

* Address Windowing Extensions (AWE) is a Microsoft Windows application programming interface that allows a 32-bit software application to access more physical memory than it has virtual address space. 

* Address Windowing Extensions (AWE) is a set of extensions that allows an application to quickly manipulate physical memory greater than 4GB. Certain data-intensive applications, such as database management systems and scientific and engineering software, need access to very large caches of data. In the case of very large data sets, restricting the cache to fit within an application's 2GB of user address space is a severe restriction. 

Incorrect: Not B: 

Q64. 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. Change the similarity threshold to 0.55. 

C. Change the similarity threshold to 0.40. 

D. Increase the maximum number of matches per lookup. 

Answer: B 

Explanation: 

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

Q65. 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? 

A. dtsx 

B. SSISdeploymentmanifest 

C. msi 

D. ispac 

Answer: D 

Q66. To facilitate the troubleshooting of SQL Server Integration Services (SSIS) packages, a logging methodology is put in place. 

The methodology has the following requirements: 

The deployment process must be simplified. 

All the logs must be centralized in SQL Server. 

Log data must be available via reports or T-SQL. 

Log archival must be automated. 

You need to configure a logging methodology that meets the requirements while minimizing the amount of deployment and development effort. 

What should you do? 

A. Open a command prompt and run the gacutil command. 

B. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility. 

C. Add an OnError event handler to the SSIS project. 

D. Use an msi file to deploy the package on the server. 

E. Configure the output of a component in the package data flow to use a data tap. 

F. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server. 

G. Open a command prompt and run the dtexec /rep /conn command. 

H. Open a command prompt and run the dtutil /copy command. 

I. Open a command prompt and run the dtexec /dumperror /conn command. 

J. Configure the SSIS solution to use the Project Deployment Model. 

K. Create a reusable custom logging component and use it in the SSIS project. 

Answer: J 

Explanation: 

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

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

Q67. 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? 

A. Add CurrentValue and PreviousValue columns to the student dimension. 

B. Enable Snapshot Isolation on the data warehouse. 

C. Add an IsCurrent column to the student dimension. 

D. Add StartDate and EndDate columns to the student dimension. 

Answer: D 

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

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

The package contains several tasks that must repeat until an expression evaluates to 

FALSE. 

You need to add and configure a container to enable this design. 

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,) 

Answer: 

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

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.) 

Answer: 

Q70. 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. 

Answer: B