16 tips on How to 70-463 Test Like a Badass [1 to 16]

Exam Code: 70-463 (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 Exam.

2016 May 70-463 Study Guide Questions:

Q1. 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 and maximizing data flow performance. 

What should you do? 

A. Use an SSIS Script task that uses the custom assembly to parse the text data when inserting it. 

B. Use an SSIS Script transformation that uses the custom assembly to parse the text data when inserting it. 

C. Create a SQL Common Language Runtime (SQLCLR) function that uses the custom assembly to parse the text data, deploy it in the Windows Azure SQL Database database, and use it when inserting data. 

D. Create a SQL Common Language Runtime (SQLCLR) stored procedure that uses the custom assembly to parse the text data, deploy it in the Windows Azure SQL Database database, and use it when inserting data. 

Answer: B 


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

The package uses custom functionality that accesses a SQL Server database. The custom 

functionality must be implemented by using Language Integrated Query (LINQ). 

You need to ensure that the LINQ code can be debugged at design time. 

What should you select from the SSIS Toolbox? (To answer, drag the appropriate item to the correct location in the answer area.) 


Answer: 



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

Answer: A,D 


Q4. You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.) 



Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table. You need to assign the appropriate constraints and table properties to ensure data integrity and visibility. On which column in the Employee table should you use an identity specification to include a seed of 1,000 and an increment of 1? 

A. DateHired 

B. DepartmentID 

C. EmployeeID 

D. EmployeeNum 

E. FirstName 

F. JobTitle 

G. LastName 

H. MiddleName 

I. ReportsToID 

Answer: C 


Q5. You are creating a Data Quality Services (DQS) solution. You must provide statistics on the accuracy of the data. 

You need to use DQS profiling to obtain the required statistics. 

Which DQS activity should you use? 

A. Cleansing 

B. Knowledge Discovery 

C. SQL Profiler 

D. Matching Rule Definition 

Answer: A 


70-463  practice test

Updated 70-463 test engine:

Q6. You are designing a SQL Server Integration Services (SSIS) project that uses the Project Deployment Model. 

The project will contain 10 packages. All the packages must connect to the same data source and destination. 

You need to define and reuse the connection managers in all the packages by using the least development effort. 

What should you do? 

A. Implement parent package variables. 

B. Copy and paste connection managers from one package to the others. 

C. Implement project connection managers. 

D. Implement package connection managers. 

Answer: C 


Q7. You are implementing a SQL Server Integration Services (SSIS) 2012 package that loads data from various flat files and a Windows Azure SQL Database database. 

Daily transactions must be loaded into a staging database. All the SSIS tasks will use the CurrentDate variable as the transaction date. 

You need to set the CurrentDate variable to the date stored in a control table of the Windows Azure SQL Database database when the package starts. You need to achieve this goal by using the least amount of development effort. 

What should you use to set the variable? 

A. an Expression task 

B. an Execute SQL task 

C. a Script component 

D. a Script task 

Answer: B 


Q8. You are designing a fact table in a SQL Server database. 

The fact table must meet the following requirements: 

Include a columnstore index. 

Allow users to choose up to 10 dimension tables and up to five facts at one time. 

Maximize performance of queries that aggregate measures by using any of the 10 

dimensions. 

Support billions of rows. 

Use the most efficient design strategy. 

You need to design the fact table to meet the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.) 

A. Design a fact table with 5 dimensional key columns and 10 measure columns. Place the columnstore index on the dimensional key columns. 

B. Design a fact table with 5 dimensional key columns and 10 measure columns. Place the columnstore index on the measure columns. 

C. Design a fact table with 10 dimensional key columns and 5 measure columns. Place the columnstore index on the dimensional key columns and the measure columns. 

D. Design a fact table with 10 dimensional key columns and 5 measure columns. Place the columnstore index on only the measure columns. 

Answer: C 


Q9. You develop a SQL Server Integration Services (SSIS) package that imports Windows Azure SQL Database data into a data warehouse every night. 

The Windows Azure SQL Database 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 similarity threshold to 0.40. 

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

Explanation: * Similarity threshold Set the similarity threshold at the component level by using the slider. The closer the value is to 1, the closer the resemblance of the lookup value to the source value must be to qualify as a match. Increasing the threshold can improve the speed of matching since fewer candidate records need to be considered. 

Incorrect: 

* Maximum number of matches to output per lookup 

Specify the maximum number of matches the transformation can return for each input row. 

The default is 1. 


Q10. You are designing a package control flow. The package moves sales order data from a SQL Azure transactional database to an on-premise reporting database. The package will run several times a day, while new sales orders are being added to the transactional database. 

The current design of the package control flow is shown in the answer area. (Click the Exhibit button.) 


The Insert New Orders Data Flow task must meet the following requirements: 

Usage of the tempdb database should not be impacted. 

Concurrency should be maximized, while only reading committed transactions. 

If the task fails, only that task needs to be rolled back. 

You need to configure the Insert New Orders Data Flow task to meet the requirements. 


How should you configure the transaction properties? To answer, select the appropriate setting or settings in the answer area. 

Select “IsolationLeve as Choas” 

Answer: 



certleader.com

High value 70-463 :

Q11. You are developing a SQL Server Integration Services (SSIS) project that contains a project Connection Manager and multiple packages. 

All packages in the project must connect to the same database. The server name for the database must be set by using a parameter named ServerParam when any package in the project is executed. 

You need to develop this project with the least amount of development effort. 

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

A. Set the Sensitive property of the parameter to True. 

B. Edit each package Connection Manager. Set the ServerName property to @[$Project::ServerParam]. 

C. Edit the project Connection Manager in Solution Explorer. Set the ServerName property to @[$Project::ServerParam]. 

D. Create a project parameter named ServerName. 

E. Create a package parameter named ServerName in each package. 

F. Set the Required property of the parameter to True. 

Answer: B,D,F 

Explanation: B: From question: " The server name for the database must be set by using a parameter named ServerParam when any package in the project is executed." 

D: SSIS 2012 has introduced the concept of Project level connection managers. An SSIS project is generally more than one package. To simplify lives, the SSIS team now allows for the sharing of common resources across projects, connection managers being one of those resources. 

F: When a parameter is marked as required, a server value or execution value must be specified for that parameter. Otherwise, the corresponding package does not execute. Although the parameter has a default value at design time, it will never be used once the project is deployed. 

Note: 

* Integration Services (SSIS) parameters allow you to assign values to properties within packages at the time of package execution. You can create project parameters at the project level and package parameters at the package level. Project parameters are used to supply any external input the project receives to one or more packages in the project. Package parameters allow you to modify package execution without having to edit and redeploy the package. 

Reference: Integration Services (SSIS) Parameters 


Q12. You are using the Knowledge Discovery feature of the Data Quality Services (DQS) client application to modify an existing knowledge base. 

In the mapping configuration, two of the three columns are mapped to existing domains in the knowledge base. The third column, named Group, does not yet have a domain. 

You need to complete the mapping of the Group column. 

What should you do? 

A. Map a composite domain to the source column. 

B. Create a composite domain that includes the Group column. 

C. Add a domain for the Group column. 

D. Add a column mapping for the Group column. 

Answer: C 


Q13. You are adding a new capability to several dozen SQL Server Integration Services (SSIS) packages. 

The new capability is not available as an SSIS task. Each package must be extended with the same new capability. 

You need to add the new capability to all the packages without copying the code between packages. 

What should you do? 

A. Use the Expression task. 

B. Use the Script task. 

C. Develop a custom task. 

D. Use the Script component, 

E. Develop a custom component. 

Answer: C 

Explanation: References: 

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


Q14. 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. SQL Server Agent 

C. SSIS Upgrade Wizard in SQL Server Management Studio 

D. SQL Server DTExecUI utility (dtexecui.exe) 

Answer: C 

Explanation: 

Use the SSIS Package Upgrade Wizard to migrate packages that were developed in SQL Server 2005 Integration Services (SSIS) and SQL Server 2008 Integration Services (SSIS) to the package format that the current release of Integration Services uses. 

Reference: SSIS Upgrade Wizard (SSIS Package Upgrade Wizard) 


Q15. You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse hosted on SQL Azure. 

The package uses a Foreach container to process text files found in a folder. The package must be deployed to a single server by using the Project Deployment model. 

Multiple SQL Server Agent jobs call the package. Each job is executed on a different schedule. 

Each job passes a different folder path to the package. 

You need to configure the package to accept the folder path from each job. 

Which package configuration should you use? 

A. Parent Package Variable 

B. XML Configuration File 

C. Environment Variable 

D. .dtsConfig file 

E. Registry Entry 

Answer: C 


Q16. You are designing a SQL Server Integration Services (SSIS) solution that will load multiple Online Transactional Processing (OLTP) data sources into a SQL Server data mart. 

You have the following requirements: 

. Ensure that the process supports the creation of an exception report that details possible duplicate key values, null ratios within columns, and column-length distributions of values. 

. Ensure that users can generate the exception report in an XML format. . Use the minimum development effort. 

You need to design the SSIS solution to meet the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.) 

A. Use a Data Profiling task. Use a Data Flow task to extract the XML output of the Data Profiling task into a SQL Server table. Query the table to view the exceptions. 

B. Use Data Flow tasks to process the clean data. 

C. Use a Data Profiling task. Read the exceptions in Data Profile Viewer. 

D. Design a stored procedure that examines data for common dirty data patterns. Use an Execute SQL task. 

Answer: C 



see more 70-463 dumps