Microsoft 70-463 Online Practice Questions and Exam Preparation

Microsoft 70-463 Online Questions & Answers

• Question 51:

  You manage a SQL Server Master Data Services (MDS) environment.

  A new application requires access to the product data that is available in the MDS repository.

  You need to design a solution that gives the application access to the product data with the least amount of development effort.

  What should you do?

  A. Create a Subscription View in MDS.
  B. Access the product entity tables in the MDS database directly.
  C. Use SQL Server Integration Services (SSIS) to extract the data and put it in a staging database.
  D. Use change data capture on the product entity tables.
  A. Create a Subscription View in MDS.

  ---

  You create a subscription view to export Master Data Services data to a subscribing system such as SQL Server. You use the subscribing system to view the data in the Master Data Services database.

  References: https://docs.microsoft.com/en-us/sql/master-data-services/overview-exporting-data-master-data-services?view=sql-server-2017

• Question 52:

  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.
  D. Install a 64-bit ACE driver and replace the Excel source with an OLE DB source.

  ---

  XLSX files, the new file type in Excel2007/2010 cannot be opened 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.

• Question 53:

  You are performance tuning a SQL Server Integration Services (SSIS) package to load sales data from a source system into a data warehouse that is hosted on Windows Azure SQL Database. The package contains a data flow task that has

  10 source-to-destination execution trees.

  Only five of the source-to-destination execution trees are running in parallel.

  You need to ensure that all the execution trees run in parallel.

  What should you do?

  A. Set the EngineThreads property of the data flow task to 10.
  B. Set the MaxConcurrentExcecutables property of the package to 10.
  C. Create 10 data flow tasks that contain one source-to-destination execution tree each.
  D. Place the data flow task in a For Loop container that is configured to execute 10 times.
  A. Set the EngineThreads property of the data flow task to 10.

• Question 54:

  You are developing a SQL Server Integration Services (SSIS) package to implement an incremental data load strategy. The package reads rows from a source system and compares them to rows in a destination system. New rows will be inserted and changed rows will be updated.

  You have used a Lookup transformation and a Conditional Split transformation. The Lookup transformation joins the source and destination table on the business key, and includes all columns from the destination table in the data flow output.

  The Conditional Split transformation inspects the destination columns and directs data flow to either insert new records or update existing records.

  You need to configure the Lookup transformation to ensure that all records flow to the Conditional Split transformation, regardless of whether the rows match an existing row in the destination table. Which setting should you select? (To answer, select the appropriate option in the answer area.)

  Hot Area:

  

  

• Question 55:

  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. You have been instructed to make sure that the package implements a Slowly Changing Dimension (SCD) that creates new fields in the dimension record.

  Which of the following is the change type that you should use?

  A. Type 1
  B. Type 2
  C. Type 3
  D. Type 4
  C. Type 3

• Question 56:

  You are editing a SQL Server Integration Services (SSIS) package that contains three Execute SQL tasks and no other tasks. The three Execute SQL tasks modify products in staging tables in preparation for a data warehouse load.

  The package and all three Execute SQL product tasks have their TransactionOption property set to Supported.

  You need to ensure that if any of the three Execute SQL product tasks fail, all three tasks will roll back their changes.

  What should you do?

  A. Change the TransactionOption property of the package to Required.
  B. Change the TransactionOption property of all three Execute SQL product tasks to Required.
  C. Move the three Execute SQL product tasks into a Foreach Loop container.
  D. Move the three Execute SQL product tasks into a Sequence container.
  A. Change the TransactionOption property of the package to Required.

  ---

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

• Question 57:

  You have a data warehouse that contains all of the sales data for your company.

  You need to design a SQL Server Integration Services (SSIS) package that reports the average value for one of the columns in a data flow.

  What should you add to the package?

  A. Data Profiling tasks that have a Column Value Distribution Profile
  B. Data Profiling tasks that have a Column Null Profile
  C. Data Profiling tasks that have a Column Statistics Profile
  D. the Data Mining Query task
  C. Data Profiling tasks that have a Column Statistics Profile

  ---

  References: https://docs.microsoft.com/en-us/sql/integration-services/control-flow/data-profiling-task

• Question 58:

  You need to extract data from a table in a SQL Server 2012 database. What connection manager types can you use? (Choose all that apply.)

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

  ---

  References: https://technet.microsoft.com/en-us/library/ms141013(v=sql.110).aspx https://docs.microsoft.com/en-us/sql/integration-services/data-flow/extract-data-by-using-the-odbc-source https://msdn.microsoft.com/en-us/library/ms254937(v=vs.100).aspx

• Question 59:

  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. You have decided to make use of Script Task.

  Which of the following is TRUE with regards to Script Task?

  A. It creates and evaluates expressions that set variable values at runtime.
  B. It provides code to perform functions that are not available in the built-in tasks and transformations that SQL Server Integration Services provides.
  C. It runs an application or batch file as part of a SQL Server Integration Services package workflow.
  D. It allows you to run SQL statements during package execution.
  B. It provides code to perform functions that are not available in the built-in tasks and transformations that SQL Server Integration Services provides.

• Question 60:

  You work as a database administrator 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. ABC.com has a dimensional database with conformed dimensions and surrogate keys.

  What is the reason for making use of conformed dimensions?

  A. It prevents a dimension from being used in multiple data marts.
  B. It links dimension and fact tables.
  C. It allows for a dimension to be used in multiple data marts.
  D. It is used to guarantee uniqueness and to improve performance.
  C. It allows for a dimension to be used in multiple data marts.