Microsoft 70-767 Online Practice Questions and Exam Preparation

Microsoft 70-767 Online Questions & Answers

• Question 111:

  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 developing a Microsoft SQL Server Integration Services (SSIS) package.

  You need to cleanse a data flow source by removing duplicate records based on approximate matches.

  Which SSIS Toolbox item should you use?

  A. CDC Control task
  B. CDC Splitter
  C. Union All
  D. XML task
  E. Fuzzy Grouping
  F. Merge
  G. Merge Join
  E. Fuzzy Grouping

  ---

  The Fuzzy Grouping transformation performs data cleaning tasks by identifying rows of data that are likely to be duplicates and selecting a canonical row of data to use in standardizing the data. Incorrect Answers:

  C: UNION ALL. Incorporates all rows into the results. This includes duplicates. If not specified, duplicate rows are removed. References: https://docs.microsoft.com/en-us/sql/integration-services/data-flow/transformations/fuzzy-grouping-transformation

• Question 112:

  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 implementing a Microsoft SQL Server data warehouse with a multi-dimensional data model.

  When testing a pilot version of the data warehouse, business users observe that the number of products in stock is inaccurate. The number of products in stock always increases and represents the total number of products that have ever

  been in stock.

  You need to correct the existing model and ensure that it reflects the number of in-stock products. You must not change the overall structure of the data model. What should you do?

  A. star schema
  B. snowflake schema
  C. conformed dimension
  D. slowly changing dimension (SCD)
  E. fact table
  F. semi-additive measure
  G. non-additive measure
  H. dimension table reference relationship
  H. dimension table reference relationship

• Question 113:

  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 a Microsoft SQL server that has Data Quality Services (DQS) installed. You need to review the completeness and the uniqueness of the data stored in the matching policy. Solution: You modify the weight of the domain in the

  matching rule.

  Does this meet the goal?

  A. Yes
  B. No
  A. Yes

  ---

  Use a matching rule, and use completeness and uniqueness data to determine what weight to give a field in the matching process.

  If there is a high level of uniqueness in a field, using the field in a matching policy can decrease the matching results, so you may want to set the weight for that field to a relatively small value. If you have a low level of uniqueness for a column,

  but low completeness, you may not want to include a domain for that column.

  References: https://docs.microsoft.com/en-us/sql/data-quality-services/create-a-matching-policy? view=sql-server-2017

• Question 114:

  You are designing a SQL Server Integration Services (SSIS) package that uses the Fuzzy Lookup transformation.

  The reference data to be used in the transformation changes with every package execution.

  Common language runtime (CLR) integration cannot be enabled on the SQL Server database where the reference table is located.

  You need to configure the Fuzzy Lookup transformation in the most efficient manner.

  What should you do?

  A. Execute the sp_FuzzyLookupTableMaintenanceInvoke stored procedure.
  B. Select the GenerateNewIndex option in the Fuzzy Lookup Transformation Editor.
  C. Execute the sp_FuzzyLookupTableMaintenanceUninstall stored procedure.
  D. Select the GenerateAndPersistNewIndex option in the Fuzzy Lookup Transformation Editor.
  B. Select the GenerateNewIndex option in the Fuzzy Lookup Transformation Editor.

• Question 115:

  You create a Microsoft SQL Server Integration Services (SSIS) package as shown in the SSIS Package exhibit. (Click the Exhibit button.)

  The package uses data from the Products table and the Prices table. Properties of the Prices source are shown in the OLE DB Source Editor exhibit (Click the Exhibit Button.) and the Advanced Editor for Prices exhibit (Click the Exhibit button.)

  

  

  

  You join the Products and Prices tables by using the ReferenceNr column.

  You need to resolve the error with the package.

  For each of the following statements, select Yes if the statement is true. Otherwise, select No.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  

  ---

  There are two important sort properties that must be set for the source or upstream transformation that supplies data to the Merge and Merge Join transformations:

  The Merge Join Transformation requires sorted data for its inputs.

  If you do not use a Sort transformation to sort the data, you must set these sort properties manually on the source or the upstream transformation.

  References: https://docs.microsoft.com/en-us/sql/integration-services/dataflow/transformations/sort-data-for-the-merge-and-merge-join-transformations

• Question 116:

  Occasionally a job that executes an existing SQL Server Integration Services (SSIS) package does not complete and nothing is processed.

  You need to ensure that package logging occurs. Your solution must minimize deployment and development efforts.

  What should you do?

  A. Create a reusable custom logging component.
  B. Use the gacutil command.
  C. Use the Project Deployment Wizard.
  D. Run the package by using the dtexec /rep /conn command.
  E. Add a data tap on the output of a component in the package data flow.
  F. Create an OnError event handler.
  G. Use the dtutil /copy command.
  H. Deploy the package by using an msi file.
  I. Run the package by using the dtexec /dumperror /conn command.
  J. Run the package by using the dtexecui.exe utility and the SQL Log provider.
  J. Run the package by using the dtexecui.exe utility and the SQL Log provider.

  ---

  http://msdn.microsoft.com/en-us/library/ms140246.aspx http://msdn.microsoft.com/en-us/library/hh231187.aspx

• Question 117:

  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 plan to deploy a Microsoft SQL server that will host a data warehouse named DB1.

  The server will contain four SATA drives configured as a RAID 10 array.

  You need to minimize write contention on the transaction log when data is being loaded to the database.

  Solution: You replace the SATA disks with SSD disks.

  Does this meet the goal?

  A. Yes
  B. No
  B. No

  ---

  A data warehouse is too big to store on an SSD.

  Instead you should place the log file on a separate drive.

  References:

  https://docs.microsoft.com/en-us/sql/relational-databases/policy-based-management/place-data-and-log-files-on-separate-drives?view=sql-server-2017

• Question 118:

  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 a Microsoft SQL server that has Data Quality Services (DQS) installed.

  You need to review the completeness and the uniqueness of the data stored in the matching policy.

  Solution: You create a matching rule.

  Does this meet the goal?

  A. Yes
  B. No
  B. No

  ---

  Use a matching rule, and use completeness and uniqueness data to determine what weight to give a field in the matching process.

  If there is a high level of uniqueness in a field, using the field in a matching policy can decrease the matching results, so you may want to set the weight for that field to a relatively small value. If you have a low level of uniqueness for a column,

  but low completeness, you may not want to include a domain for that column.

  References: https://docs.microsoft.com/en-us/sql/data-quality-services/create-a-matching-policy? view=sql-server-2017

• Question 119:

  You administer a Microsoft SQL Server Master Data Services (MDS) model. All model entity members have passed validation.

  The current model version should be committed to form a record of master data that can be audited and create a new version to allow the ongoing management of the master data.

  You lock the current version. You need to manage the model versions.

  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:

  

  

  ---

  Box 1: Validate the current version.

  In Master Data Services, validate a version to apply business rules to all members in the model version.

  You can validate a version after it has been locked.

  Box 2: Commit the current version.

  In Master Data Services, commit a version of a model to prevent changes to the model's members and their attributes. Committed versions cannot be unlocked.

  Prerequisites:

  Box 3: Create a copy of the current version.

  In Master Data Services, copy a version of the model to create a new version of it.

• Question 120:

  DRAG DROP

  A new SQL Server Integration Services (SSIS) project is deployed to the SSIS catalog.

  To troubleshoot some data issues, you must output the data streaming through several data flows into text files for further analysis. You have the list of data flow package paths and identification strings of the various task components that

  must be analyzed.

  You need to create these output files with the least amount of administrative and development effort.

  Which three stored procedures should you execute 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: