Microsoft 70-767 Online Practice Questions and Exam Preparation

Microsoft 70-767 Online Questions & Answers

• Question 381:

  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 Azure SQL Data Warehouse instance. You run the following Transact-SQL statement:

  

  The query fails to return results.

  You need to determine why the query fails.

  Solution: You run the following Transact-SQL statements:

  

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

  ---

  We must use Label, not QueryID in the WHERE clause.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-pdw-exec-requests-transact-sql?view=aps- pdw-2016-au7

• Question 382:

  DRAG DROP

  A SQL Server Integration Services (SSIS) project has been deployed to the SSIS catalog. The project includes a project Connection Manager to connect to the data warehouse. The SSIS catalog includes two Environments:

  Development

  QA

  Each Environment defines a single Environment Variable named ConnectionString of type string. The value of each variable consists of the connection string to the development or QA data warehouses.

  You need to be able to execute deployed packages by using either of the defined Environments.

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

  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 are importing data from databases at retail stores into a central data warehouse. All stores use the same database schema.

  The query being executed against the retail stores is shown below:

  

  The data source property named IsSorted is set to True. The output of the transform must be sorted.

  You need to add a component to the data flow.

  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
  C. Union All

• Question 384:

  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 have a database named DB1 that has change data capture enabled.

  A Microsoft SQL Server Integration Services (SSIS) job runs once weekly. The job loads changes from DB1 to a data warehouse by querying the change data capture tables.

  You remove the Integration Services job.

  You need to stop tracking changes to the database. The solution must remove all the change data capture configurations from DB1.

  Which stored procedure should you execute?

  A. catalog.deploy_project
  B. catalog.restore_project
  C. catalog.stop.operation
  D. sys.sp.cdc.addjob
  E. sys.sp.cdc.changejob
  F. sys.sp_cdc_disable_db
  G. sys.sp_cdc_enable_db
  H. sys.sp_cdc.stopJob
  F. sys.sp_cdc_disable_db

• Question 385:

  You are the data steward for a Business Intelligence project.

  You must identify duplicate rows stored in a SQL Server table and output discoveries to a CSV file. A Data Quality Services (DQS) knowledge base has been created to support this project.

  You need to produce the CSV file with the least amount of development effort.

  What should you do?

  A. Create an Integration Services package and use a Fuzzy Lookup transform.
  B. Create a Master Data Services (MDS) model.
  C. Create a Data Quality Project.
  D. Create an Integration Services package and use a DQS Cleansing transform.
  E. Create an Integration Services package and use a Fuzzy Grouping transform.
  C. Create a Data Quality Project.

  ---

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

• Question 386:

  You manage Master Data Services (MDS). You plan to create entities and attributes and load them with the data. You also plan to match data before loading it into Data Quality Services (DQS).

  You need to recommend a solution to perform the actions.

  What should you recommend?

  A. MDS Add-in for Microsoft Excel
  B. MDS Configuration Manager
  C. Data Quality Matching
  D. MDS repository
  A. MDS Add-in for Microsoft Excel

  ---

  In the Master Data Services Add-in for Excel, matching functionality is provided by Data Quality Services (DQS). This functionality must be enabled to be used.

  1.

  To enable Data Quality Services integration

  2.

  Open Master Data Services Configuration Manager.

  3.

  In the left pane, click Web Configuration.

  4.

  On the Web Configuration page, select the website and web application.

  5.

  In the Enable DQS Integration section, click Enable integration with Data Quality Services.

  6.

  On the confirmation dialog box, click OK.

  References: https://docs.microsoft.com/en-us/sql/master-data-services/install-windows/enable-data-quality-services-integration-with-master-data-services

• Question 387:

  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 seven source-to-destination execution trees.

  Only three 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 7.
  B. Set the MaxConcurrentExcecutables property of the package to 7.
  C. Create seven 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 seven times.
  A. Set the EngineThreads property of the data flow task to 7.

• Question 388:

  DRAG DROP

  Your company is evaluating the data cleansing capabilities of SQL Server Data Quality Services (DQS).

  A stand-alone server will be used to host DQS, as well as all related services necessary to run this service.

  You need to install the DQS components on the server by using the minimum amount of steps possible. You also need to grant permissions to a login named DataStewards to enable DataStewards to edit and execute DQS projects. Which four 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 389:

  You are designing the data warehouse to import data from three different environments. The sources for the data warehouse will be loaded every hour.

  Scenario A includes tables in a Microsoft Azure SQL Database:

  Millions of updates and inserts occur per hour

  A periodic query of the current state of rows that have changed is needed.

  The change detection method needs to be able to ignore changes to some columns in a table.

  The source database is a member of an AlwaysOn Availability group.

  Scenario B includes tables with status update changes:

  Tracking the duration between workflow statuses.

  All transactions must be captured, including before/after values for UPDATE statements.

  To minimize impact to performance, the change strategy adopted should be asynchronous.

  Scenario C includes an external source database:

  Updates and inserts occur regularly.

  No changes to the database should require code changes to any reports or applications.

  Columns are added and dropped to tables in the database periodically. These schema changes should not require any interruption or reconfiguration of the change detection method chose.

  Data is frequently queried as the entire row appeared at a past point in time.

  All tables have primary keys.

  You need to load each data source. You must minimize complexity, disk storage, and disruption to the data sources and the existing data warehouse.

  Which change detection method should you use for each scenario? To answer, drag the appropriate loading methods to the correct scenarios. Each source 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:

  

  

  ---

  Box A: System-Versioned Temporal Table

  System-versioned temporal tables are designed to allow users to transparently keep the full history of changes for later analysis, separately from the current data, with the minimal impact on the main OLTP workload.

  Box B: Change Tracking

  Box C: Change Data Capture

  Change data capture supports tracking of historical data, while that is not supported by change tracking.

  References:

  https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/track-data-changes-sql-server

  https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-table-usage-scenarios

• Question 390:

  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