Microsoft 70-767 Online Practice Questions and Exam Preparation

Microsoft 70-767 Online Questions & Answers

• Question 281:

  You are developing a Microsoft SQL Server Master Data Services (MDS) solution.

  The model contains an entity named Product. The Product entity has three user-defined attributes named Category, Subcategory, and Price, respectively.

  You need to ensure that combinations of values stored in the Category and Subcategory attributes are unique.

  What should you do?

  A. Create two custom indexes, one for each of the Category and Subcategory attributes.
  B. Publish a business rule that will be used by the Product entity.
  C. Create a derived hierarchy based on the Category and Subcategory attributes. Use the Category attribute as the top level for the hierarchy.
  D. Set the value of the Attribute Type property for the Category and Subcategory attributes to Domain-based.
  A. Create two custom indexes, one for each of the Category and Subcategory attributes.

  ---

  References: https://docs.microsoft.com/en-us/sql/master-data-services/custom-index-master-data-services?view=sql-server-ver15

• Question 282:

  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 section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

  You have an on-premises Microsoft SQL Server instance and a Microsoft Azure SQL Data Warehouse instance. You move data from the on-premises database to the data warehouse once each day by using a SQL Server Integration

  Services (SSIS) package.

  You observe that the package no longer completes within the allotted time.

  You need to determine which tasks are taking a long time to complete.

  Solution: You alter the package to log the start and completion times for a task to a table in the on-premises SQL Server instance.

  Does the solution meet the goal?

  A. Yes
  B. No
  A. Yes

  ---

  References: https://docs.microsoft.com/en-us/sql/integration-services/performance/integration-services-ssis-logging?view=sql-server-ver15

• Question 283:

  You need to load data from a CSV file to a table.

  How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment 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:

  

  

  ---

  Example:

  BULK INSERT Sales.Orders

  FROM '\\SystemX\DiskZ\Sales\data\orders.csv'

  WITH ( FORMAT='CSV');

• Question 284:

  You are developing a Microsoft SQL Server Master Data Services (MDS) solution.

  The model contains an entity named Product. The Product entity has three user-defined attributes named category. Subcategory, and Price, respectively.

  You need to ensure that combinations of values stored in the category and subcategory attributes are unique.

  What should you do?

  A. Create a derived hierarchy based on the category and subcategory attributes. Use the category attribute as the top level for the hierarchy.
  B. Publish two business rules, one for each of the Category and Subcategory attributes.
  C. Set the value of the Attribute Type property for the Category and Subcategory attributes to Domain-based.
  D. Create a custom index that will be used by the Product entity.
  A. Create a derived hierarchy based on the category and subcategory attributes. Use the category attribute as the top level for the hierarchy.

• Question 285:

  Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.

  You are a database administrator for an e-commerce company that runs an online store. The company has three databases as described in the following table.

  

  You plan to load at least one million rows of data each night from DB1 into the OnlineOrder table. You must load data into the correct partitions using a parallel process.

  You create 24 Data Flow tasks. You must place the tasks into a component to allow parallel load. After all of the load processes compete, the process must proceed to the next task.

  You need to load the data for the OnlineOrder table. What should you use?

  A. Lookup transformation
  B. Merge transformation
  C. Merge Join transformation
  D. MERGE statement
  E. Union All transformation
  F. Balanced Data Distributor transformation
  G. Sequential container
  H. Foreach Loop container
  H. Foreach Loop container

  ---

  The Parallel Loop Task is an SSIS Control Flow task, which can execute multiple iterations of the standard Foreach Loop Container concurrently. References: http://www.cozyroc.com/ssis/parallel-loop-task

• Question 286:

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

  To process complex scientific data originating from a SQL Azure database, a custom task component is added to the project.

  You need to ensure that the custom component is deployed on a test environment correctly.

  What should you do?

  A. Add an OnError event handler to the SSIS project.
  B. Open a command prompt and run the gacutil command.
  C. Configure the SSIS solution to use the Project Deployment Model.
  D. Open a command prompt and run the dtexec /dumperror /conn command.
  E. Configure the output of a component in the package data flow to use a data tap.
  F. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility.
  G. Open a command prompt and run the dtexec /rep /conn command.
  H. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server.
  I. Use an msi file to deploy the package on the server.
  J. Open a command prompt and run the dtutil /copy command.
  B. Open a command prompt and run the gacutil command.

  ---

  Reference: http://msdn.microsoft.com/en-us/library/ms403356.aspx

• Question 287:

  You are developing a project that contains multiple SQL Server Integration Services (SSIS) packages. The packages will be deployed to the SSIS catalog. One of the steps in each package accesses an FTP site to download sales transaction

  data.

  You create project parameters to store the username and password that are used to access the FTP site.

  You need to ensure that the username and password values are encrypted when they are deployed.

  What should you do?

  A. Set the Sensitive property of the parameters to True.
  B. Set the ProtectionLevel property of the package to EncryptSensitiveWithUserKey.
  C. Change the parameters to package parameters.
  D. Change the project to the Legacy Deployment model.
  A. Set the Sensitive property of the parameters to True.

• Question 288:

  DRAG DROP

  You administer a Microsoft SQL Server database. You want to import data from a text file to the database. You need to ensure that the following requirements are met:

  Data import is performed by using a stored procedure.

  Data is loaded as a unit and is minimally logged.

  Which data import command and recovery model should you choose? (To answer, drag the appropriate data import command or recovery model to the appropriate location or locations in the answer area. Each data import command or recovery model 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.)

  Select and Place:

  

  

• Question 289:

  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.

  Which data flow source should you use?

  A. Raw File source
  B. XML source
  C. Custom source component
  D. Flat File source
  C. Custom source component

• Question 290:

  Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is

  exactly the same in each question in the series.

  Start of repeated scenario

  Contoso. Ltd. has a Microsoft SQL Server environment that includes SQL Server Integration Services (SSIS), a data warehouse, and SQL Server Analysis Services (SSAS) Tabular and multidimensional models.

  The data warehouse stores data related to your company sales, financial transactions and financial budgets All data for the data warenouse originates from the company's business financial system.

  The data warehouse includes the following tables:

  

  The company plans to use Microsoft Azure to store older records from the data warehouse. You must modify the database to enable the Stretch Database capability.

  Users report that they are becoming confused about which city table to use for various queries. You plan to create a new schema named Dimension and change the name of the dbo.du_city table to Diamension.city. Data loss is not

  permissible, and you must not leave traces of the old table in the data warehouse.

  Pal to create a measure that calculates the profit margin based on the existing measures.

  You must improve performance for queries against the fact.Transaction table. You must implement appropriate indexes and enable the Stretch Database capability.

  End of repeated scenario

  You need to resolve the problems reported about the dia__city table.

  How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment 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.

  Select and Place: