Microsoft 70-463 Online Practice Questions and Exam Preparation

Microsoft 70-463 Online Questions & Answers

• Question 101:

  You have a database named Sales and a data warehouse named DataW.

  From Sales, you plan to bulk insert to a table in DataW that is more than 2 TB. The process will insert a minimum of 11 million rows simultaneously.

  You need to identify which data storage strategy must be used to minimize load times.

  Which data storage strategy should you identify?

  A. a page-compressed heap
  B. non-durable staging table
  C. a file table
  D. a durable In-Memory OLTP
  B. non-durable staging table

  ---

  Incorrect Answers:

  D: In-Memory OLTP can significantly improve the performance of transaction processing, data ingestion and data load, and transient data scenarios. It does not fit a data warehouse scenario. References: http://henkvandervalk.com/how-to-load-data-fast-into-sql-server-2016

• Question 102:

  You work as a senior database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment.

  You are running a training exercise for Microsoft SQL Server 2012 junior developers. You are discussing an event handler provided by Integration Services that is raised by an executable to establish if it should stop running.

  Which of the following is the event handler you are discussing?

  A. The OnPostExecute event handler
  B. The OnQueryCancel event handler
  C. The OnError event handler
  D. The OnProgress event handler
  B. The OnQueryCancel event handler

• Question 103:

  You work as a database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment.

  You are in the process of creating a SQL Server Integration Services (SSIS) package. The package will be configured upload a file to a table in a SQL Azure database. You need to make sure that the code includes an event handler that

  makes use of an executable to raise the event when its verification is completed.

  Which of the following is the event handler that you should use?

  A. The OnPostExecute event handler.
  B. The OnTaskFailed event handler.
  C. The OnPreExecute event handler.
  D. The OnPostValidate event handler.
  D. The OnPostValidate event handler.

• Question 104:

  You work as a database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment.

  You are in the process of creating a SQL Server Integration Services (SSIS) project. The project must be included in the SSIS catalog. You need to make use of a stored procedure that creates an environment reference for a project in the

  SSIS catalog.

  Which of the following actions should you take?

  A. You should consider making use of the catalog.create_execution stored procedure.
  B. You should consider making use of the catalog.create_environment_variable stored procedure.
  C. You should consider making use of the catalog.create_environment_reference stored procedure.
  D. You should consider making use of the catalog.add_data_tap stored procedure.
  C. You should consider making use of the catalog.create_environment_reference stored procedure.

• Question 105:

  You need to extract data from delimited text files. What connection manager type would you choose?

  A. A Flat File connection manager
  B. An OLE DB connection manager
  C. An ADO.NET connection manager
  D. A File connection manager
  A. A Flat File connection manager

  ---

  References: http://smallbusiness.chron.com/import-csv-ssis-46849.html

• Question 106:

  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 two data flow components should you use to identify modified data? (Each correct answer presents a complete solution. Choose all that apply.)

  A. Multicast
  B. Data Conversion
  C. Lookup
  D. Slowly Changing Dimension
  E. Aggregate
  C. Lookup
  D. Slowly Changing Dimension

  ---

  C: Lookup Transformation

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

  D: Slowly Changing Dimension

  The Slowly Changing Dimension transformation provides the following functionality for managing slowly changing dimensions:

  

  Matching incoming rows with rows in the lookup table to identify new and existing rows.

  

  Identifying incoming rows that contain changes when changes are not permitted.

  

  Identifying inferred member records that require updating.

  

  Identifying incoming rows that contain historical changes that require insertion of new records and the updating of expired records.

  

  Detecting incoming rows that contain changes that require the updating of existing records, including expired ones.

  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.

  References:

  https://docs.microsoft.com/en-us/sql/integration-services/data-flow/transformations/lookup-transformation

  https://docs.microsoft.com/en-us/sql/integration-services/data-flow/transformations/slowly-changing-dimension-transformation

• Question 107:

  A SQL Server Integration Services (SSIS) package on a computer is failing periodically in production. The package was created less than one year ago and was deployed to the SSIS catalog.

  Sometimes the package is started on a SQL Agent schedule; sometimes the package is started manually by an SSIS developer 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 failed in the past.

  Where can you find this information?

  A. the SQL Server Log
  B. the SSISDB.[catalog].[executions] view
  C. the SSISDB.[catalog].[event_messages] view
  D. the SQL Agent Job History
  E. the SQL Agent Error Log
  B. the SSISDB.[catalog].[executions] view

• Question 108:

  A SQL Server Integration Services (SSIS) package currently downloads sales data from a Microsoft Azure SQL Database database.

  To improve sales data accuracy, exchange rates must be downloaded daily from a public HTTP web service instead of from a weekly flat file.

  You need to implement the change to the existing package while minimizing the development effort.

  What should you use to call the web service to retrieve the daily exchange rates?

  A. a Script task
  B. a Web Service task
  C. a Script component
  D. a Web Service source
  B. a Web Service task

  ---

  References: https://docs.microsoft.com/en-us/sql/integration-services/control-flow/web-service-task?view=sql-server-2017

  https://www.mssqltips.com/sqlservertip/3272/example-using-web-services-with-sql-server-integration-services/

• Question 109:

  You are validating whether a SQL Server Integration Services (SSIS) package named Master.dtsx in the SSIS catalog is executing correctly.

  You need to display the number of rows in each buffer passed between each data flow component of the 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:

  

  

• Question 110:

  You are reviewing the design of a customer dimension table in an existing data warehouse hosted on SQL Azure.

  The current dimension design does not allow the retention of historical changes to customer attributes such as Postcode.

  You need to redesign the dimension to enable the full historical reporting of changes to multiple customer attributes including Postcode.

  What should you do?

  A. Add StartDate and EndDate columns to the customer dimension.
  B. Add an IsCurrent column to the customer dimension.
  C. Enable Snapshot Isolation on the data warehouse.
  D. Add CurrentValue and PreviousValue columns to the customer dimension.
  A. Add StartDate and EndDate columns to the customer dimension.

  ---

  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