Microsoft 70-463 Online Practice Questions and Exam Preparation

Microsoft 70-463 Online Questions & Answers

• Question 251:

  You are creating a sales data warehouse. When a product exists in the product dimension, you update the product name. When a product does not exist, you insert a new record.

  In the current implementation, the DimProduct table must be scanned twice, once for the insert and again for the update. As a result, inserts and updates to the DimProduct table take longer than expected.

  You need to create a solution that uses a single command to perform an update and an insert.

  How should you use a MERGE T-SQL statement to accomplish this goal? (To answer, drag the appropriate answer choice from the list of options to the correct location or locations in the answer area.

  You may need to drag the split bar between panes or scroll to view content.)

  Select and Place:

  

  

• Question 252:

  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?

  A. Install SQL Server 2012 Database Engine.
  B. Install Microsoft SharePoint Server 2010 Enterprise Edition with PowerPivot.
  C. Make sure the server has at least 4 GB of RAM.
  D. Install Microsoft Internet Explorer 6.0 SP1 or later.
  A. Install SQL Server 2012 Database Engine.

  ---

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

  You have a Master Data Services (MDS) in a SQL Server deployment. You need to deploy a model that contains data to the MDS repository. What should you use?

  A. the Integration Services Deployment Wizard
  B. the Master Data Services Configuration Manager tool
  C. the Model Deployment Wizard
  D. the MDSModelDeploy tool
  D. the MDSModelDeploy tool

  ---

  References: https://msdn.microsoft.com/en-us/library/ff486956(v=sql.120).aspx

• Question 254:

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

  A. Partition the destination fact table by date. Insert new data directly into the fact table and delete old data directly from the fact table.
  B. Partition the destination fact table by date. Use partition switching and staging tables both to remove old data and to load new data.
  C. Partition the destination fact table by customer. Use partition switching both to remove old data and to load new data into each partition.
  D. Partition 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.
  B. Partition the destination fact table by date. Use partition switching and staging tables both to remove old data and to load new data.

• Question 255:

  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.
  C. Add a domain for the Group column.

• Question 256:

  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 XP Command Shell.
  B. Enable CLR Integration.
  C. Enable OLE Automation.
  D. Start the SQL Server Browser service.
  E. Enable Cross Database Ownership Chaining
  F. Start the SQL Server Agent service
  G. Enable Ad Hoc Remote Queries.
  B. Enable CLR Integration.
  F. Start the SQL Server Agent service

  ---

  B: To create the SSISDB catalog in SQL Server Management Studio

  1.

  Open SQL Server Management Studio.

  2.

  Connect to the SQL Server Database Engine.

  3.

  In Object Explorer, expand the server node, right-click the Integration Services Catalogs node, and then click Create Catalog.

  4.

  Click Enable CLR Integration. The catalog uses CLR stored procedures. Etc.

  F: SSIS projects, packages, parameters, environments, and operational history are all stored in the SSISDB database so if you have hundreds of SSIS packages or packages that run every few minutes you could see how the database storing all the historical information would grow exponentially.

  Also included when you enable this feature is a SQL Server Agent Job called SSIS Server Maintenance job:

  

  References:

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

  https://www.mssqltips.com/sqlservertip/3307/managing-the-size-of-the-sql-server-ssis-catalog-database/

• Question 257:

  You are building a SQL Server Integration Services (SSIS) package to load data from all files that are automatically copied to a directory each night through an external FTP process.

  You need to load data from all copied files to a destination table in SQL Server.

  Which three steps 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:

  

  

• Question 258:

  You are the administrator of a server that hosts Data Quality Server for a large retail company. The server had a hardware failure during business hours.

  You need to restore the server that hosts Data Quality Server to another server. You have a recent backup of all the required databases.

  Which two actions should you perform? Each correct answer presents part of the solution.

  A. Restore the DQS_MAIN, DQS_PROJECTS, and DQS_STATING_DATA databases to another server as soon as possible.
  B. Execute the DQS_MAIN.internal_core.RestoreDQDatabases stored procedure with the appropriate parameter.
  C. Execute the DQS_MAIN.internal_core.InitServer stored procedure with the appropriate parameter.
  D. Restore only the DQS_MAIN and DQS_STAGING_DATA databases to another server as soon as possible.
  A. Restore the DQS_MAIN, DQS_PROJECTS, and DQS_STATING_DATA databases to another server as soon as possible.
  B. Execute the DQS_MAIN.internal_core.RestoreDQDatabases stored procedure with the appropriate parameter.

  ---

  1. Restore DQS_MAIN database.

  2.Restore the DQS_PROJECTS database.

  3.Restore the DQS_STAGING_DATA database.

  4. In the Query Editor window, copy the following SQL statements, and replace with the password that you provided during the DQS installation for the database master key:

  USE [DQS_MAIN]

  GO

  EXECUTE [internal_core].[RestoreDQDatabases] ''

  GO

  Press F5 to execute the statements. Check the Results pane to verify that the statements have executed successfully.

  References: https://docs.microsoft.com/en-us/sql/data-quality-services/backing-up-and-restoring-dqs-databases?view=sql-server-2017

• Question 259:

  You are developing a SQL Server Integration Services (SSIS) package. The package sources data from an HTML web page that lists product stock levels.

  You need to implement a data flow task that reads the product stock levels from the HTML web page.

  What should you do?

  A. Use an msi file to deploy the package on the server.
  B. Open a command prompt and run the dtutil/copy command.
  C. Add an OnError event handler to the SSIS project.
  D. Open a command prompt and run the gacutil command.
  B. Open a command prompt and run the dtutil/copy command.

• Question 260:

  You are designing a SQL Server Integration Services (SSIS) package to execute 12 Transact-SQL (T-SQL) statements on a SQL Azure database. The T-SQL statements may be executed in any order. The T-SQL statements have

  unpredictable execution times.

  You have the following requirements:

  The package must maximize parallel processing of the T-SQL statements.

  After all the T-SQL statements have completed, a Send Mail task must notify administrators.

  You need to design the SSIS package. 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: