Microsoft 70-767 Online Practice Questions and Exam Preparation

Microsoft 70-767 Online Questions & Answers

• Question 361:

  Your company manufactures several types of products. The company has a production tracking application that stores the following data about the products: The production date The cost of production The names of the products The amount of waste created The number of products produced The name of the facility where the products are produced You are designing a data warehouse for the data. You add a Date dimension. You need to ensure that you can create a composite primary key for the fact table.

  Which two columns should you add to the new dimension tables? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

  A. Cost of Production
  B. Amount Produced
  C. Waste Amount
  D. Product Name
  E. Facility Name
  D. Product Name
  E. Facility Name

• Question 362:

  Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while

  others might not have a correct solution.

  After you answer a question in this sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

  You have a data warehouse that stores information about products, sales, and orders for a manufacturing company. The instance contains a database that has two tables named SalesOrderHeader and SalesOrderDetail. SalesOrderHeader

  has 500,000 rows and SalesOrderDetail has 3,000,000 rows.

  Users report performance degradation when they run the following stored procedure:

  

  You need to optimize performance.

  Solution: You run the following Transact-SQL statement:

  

  Does the solution meet the goal?

  A. Yes
  B. No
  A. Yes

  ---

  You can specify the sample size as a percent. A 5% statistics sample size would be helpful.

  References: https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data- warehouse-tables-statistics

• Question 363:

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

  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.
  A. Use an SSIS Script task that uses the custom assembly to parse the text data when inserting it.

• Question 364:

  DRAG DROP

  You are building a fact table in a data warehouse.

  The table must have a columnstore index. The table cannot be partitioned.

  You need to design the fact table and load it with data.

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

  DRAG DROP

  You are developing a SQL Server Integration Services (SSIS) package to insert new data into a data mart. The package uses a Lookup transformation to find matches between the source and destination. The data flow has the following requirements:

  New rows must be inserted.

  Lookup failures must be written to a flat file.

  In the Lookup transformation, the setting for rows with no matching entries is set to Redirect rows to no match output. You need to configure the package to direct data into the correct destinations. How should you design the data flow outputs? (To answer, drag the appropriate transformation from the list of answer options to the correct location in the answer area.)

  Select and Place:

  

  

• Question 366:

  DRAG DROP

  You manage a data warehouse. You have a source table named dbo.Table1.

  You need to implement Change Data Capture (CDC) for the queries.

  

  Which CDC object should you use for each query? To answer, drag the appropriate objects to the correct queries. Each object may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to

  view content.

  NOTE: Each correct selection is worth one point.

  Select and Place:

  

  

  ---

  References: https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-net-changes-capture-instance-transact-sql?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-all-changes-capture-instance-transact-sql?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-cdc-map-lsn-to-time-transact-sql?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/cdc-fn-cdc-get-net-changes-capture-instance-transact-sql?view=sql-server-ver15

• Question 367:

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

  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. Create a custom SSIS source component that encapsulates the 32-bit driver and compile it in 64-bit mode.
  B. Install a 64-bit ACE driver and execute the package by using the 64-bit run-time option.
  C. Execute the package by using the 32-bit run-time option.
  D. Replace the SSIS Excel source with a SSIS Flat File source.
  C. Execute the package by using the 32-bit run-time option.

  ---

  Explanation: * See step 3 below.

  To publish an Excel worksheet to Azure SQL Database, your package will contain a Data Flow Task, Excel Source task, and ADO NET Destination.

  1) Create an SSIS project.

  2) Drop a Data Flow Task onto the Control Flow design surface, and double click the Data Flow Task.

  3) Drop an Excel Source onto the Data Flow design surface. Note When using the Excel Source task on a 64-bit machine, set Run64BitRuntime to False.

  *

  Incorrect:

  Not D: The Flat File source reads data from a text file. The text file can be in delimited, fixed width, or mixed format.

• Question 368:

  You are installing SQL Server Data Quality Services (DQS).

  You need to give specific users access to the Data Quality Server.

  Which SQL Server application should you use?

  A. SQL Server Configuration Manager
  B. SQL Server Data Tools
  C. SQL Server Management Studio
  D. Data Quality Client
  C. SQL Server Management Studio

  ---

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

• Question 369:

  DRAG DROP

  You administer a large and complex SQL Server Integration Services (SSIS) solution in the SSIS catalog.

  You are instructed to execute a package by using PowerShell. You need to create the correct PowerShell command.

  How should you place the four code segments in sequence? (To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order.)

  Select and Place:

  

  

• Question 370:

  DRAG DROP

  You are editing a SQL Server Integration Services (SSIS) package that contains a task with a sensitive property.

  You need to create a project parameter and configure it so that its value is encrypted when it is deployed to the SSIS catalog.

  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:

  

  

  ---

  You are building a SQL Server Integration Services (SSIS) package to load product data sourced from a SQL Azure database to a data warehouse. Before the product data is loaded, you create a batch record by using an Execute SQL task

  named Create Batch.

  After successfully loading the product data, you use another Execute SQL task named Set Batch Success to mark the batch as successful.