Microsoft 70-767 Online Practice Questions and Exam Preparation

Microsoft 70-767 Online Questions & Answers

• Question 311:

  You have a server named SQL1 that has SQL Server Integration Services (SSIS) installed. SQL1 has eight CPUs, 8 GB of RAM, RAID-1 storage, and a 10-gigabit Ethernet connection.

  A package named Package1 runs on SQL1. Package1 contains 10 data tasks. Package1 queries 3 GB of data from the source system.

  You review the performance statistics on SQL1 and discover that Package1 executes slower than expected.

  You need to identify the problem that is causing package1 to execute slowly. Which performance monitor counter data should you review?

  A. SQLServer:SSIS Pipeline 10.0:Buffers in use
  B. Processor\% Idle Time
  C. SQLServer:SSIS Pipeline 10.0:Buffers spooled
  D. SQLServer:SSIS Pipeline 10.0:Private buffer memory
  B. Processor\% Idle Time

• Question 312:

  You have a data warehouse that includes a table named [Fact]. [sales]. The table has four partitions. There are no foreign keys defined on the [Fact].[sales] table.

  You identify an issue with the first partition. You must reload the data for the first partition as quickly as possible.

  You need to prepare the partition for reloading data.

  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:

  

  

• Question 313:

  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 this series.

  You have a Microsoft SQL Server data warehouse instance that supports several client applications.

  The data warehouse includes the following tables: Dimension.SalesTerritory, Dimension.Customer, Dimension.Date, Fact.Ticket, and Fact.Order. The Dimension.SalesTerritory and Dimension.Customer tables are frequently updated. The Fact.Order table is optimized for weekly reporting, but the company wants to change it daily. The Fact.Order table is loaded by using an ETL process. Indexes have been added to the table over time, but the presence of these indexes slows data loading.

  All data in the data warehouse is stored on a shared SAN. All tables are in a database named DB1. You have a second database named DB2 that contains copies of production data for a development environment. The data warehouse has grown and the cost of storage has increased. Data older than one year is accessed infrequently and is considered historical.

  You have the following requirements:

  Implement table partitioning to improve the manageability of the data warehouse and to avoid the need to repopulate all transactional data each night. Use a partitioning strategy that is as granular as possible. Partition the Fact.Order table

  and retain a total of seven years of data. Partition the Fact.Ticket table and retain seven years of data. At the end of each month, the partition structure must apply a sliding window strategy to ensure that a new partition is available for the

  upcoming month, and that the oldest month of data is archived and removed.

  Optimize data loading for the Dimension.SalesTerritory, Dimension.Customer, and Dimension.Date tables.

  Incrementally load all tables in the database and ensure that all incremental changes are processed.

  Maximize the performance during the data loading process for the Fact.Order partition.

  Ensure that historical data remains online and available for querying. Reduce ongoing storage costs while maintaining query performance for current data.

  You are not permitted to make changes to the client applications.

  You need to implement the data partitioning strategy.

  How should you partition the Fact.Order table?

  A. Create 17,520 partitions.
  B. Use a granularity of two days.
  C. Create 2,557 partitions.
  D. Create 730 partitions.
  C. Create 2,557 partitions.

  ---

  We create on partition for each day. 7 years times 365 days is 2,555. Make that 2,557 to provide for leap years.

  From scenario: Partition the Fact.Order table and retain a total of seven years of data. Maximize the performance during the data loading process for the Fact.Order partition.

• Question 314:

  A company is designing a database storage strategy. The company wants to segment online transaction processing (OLTP) and analytic workloads.

  You need to design the workload.

  Which index types should you use? To answer, drag the appropriate index types to the correct workloads. Each index type 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:

  

  

• Question 315:

  You are developing a SQL Server Integration Services (SSIS) package to implement an incremental data load strategy. The package reads data from a source system that uses the SQL Server change data capture (CDC) feature.

  You have added a CDC Source component to the data flow to read changed data from the source system.

  You need to add a data flow transformation to redirect rows for separate processing of insert, update, and delete operations.

  Which data flow transformation should you use?

  A. Audit
  B. Merge Join
  C. Merge
  D. CDC Splitter
  D. CDC Splitter

  ---

  Explanation: The CDC splitter splits a single flow of change rows from a CDC source data flow into different data flows for Insert, Update and Delete operations Ref: http://msdn.microsoft.com/en-us/library/hh758656.aspx

• Question 316:

  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. The package design consists of two differently structured sources in a single data flow. The Sales source retrieves sales transactions from a SQL Server

  database, and the Product source retrieves product details from an XML file.

  You need to combine the two data flow sources into a single output dataset.

  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
  G. Merge Join

  ---

  The Merge Join transformation provides an output that is generated by joining two sorted datasets using a FULL, LEFT, or INNER join. For example, you can use a LEFT join to join a table that includes product information with a table that lists the country/region in which a product was manufactured. The result is a table that lists all products and their country/region of origin.

  References: https://docs.microsoft.com/en-us/sql/integration-services/data-flow/transformations/merge-join-transformation

• Question 317:

  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.

  Your company uses Microsoft SQL Server to deploy a data warehouse to an environment that has a SQL Server Analysis Services (SSAS) instance. The data warehouse includes the Fact.Order table as shown in the following table definition. The table has no indexes.

  

  You must minimize the amount of space that indexes for the Fact.Order table consume. You run the following queries frequently. Both queries must be able to use a columnstore index:

  

  You need to ensure that the queries complete as quickly as possible.

  Solution: You create a measure for the Fact.Order table.

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

  ---

  You should use a columnstore index.

  Columnstore indexes are the standard for storing and querying large data warehousing fact tables. This index uses column-based data storage and query processing to achieve gains up to 10 times the query performance in your data warehouse over traditional row-oriented storage.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-2017

• Question 318:

  You are designing a SQL Server Integration Services (SSIS) data flow to load sales transactions from a source system into a data warehouse hosted on Windows Azure SQL Database. One of the columns in the data source is named

  ProductCode.

  Some of the data to be loaded will reference products that need special processing logic in the data flow.

  You need to enable separate processing streams for a subset of rows based on the source product code.

  Which Data Flow transformation should you use?

  A. Multicast
  B. Conditional Split
  C. Destination Assistant
  D. Script Task
  B. Conditional Split

  ---

  Explanation: We use Conditional Split to split the source data into separate processing streams.

  A Script Component (Script Component is the answer to another version of this question) could be used but this is not the same as a Script Task.

• Question 319:

  You are designing an extract, transform, and load (ETL) solution that loads data into dimension tables. The ETL process involves many transformation steps.

  You need to ensure that the design can provide:

  Auditing information for compliance and business user acceptance Tracking and unique identification of records for troubleshooting and error correction

  What should you do?

  A. Create a SQL Common Language Runtime (SQLCLR) component that records all transformation steps in a Microsoft SharePoint list.
  B. Create a version control repository for the transformation steps in Team Foundation Server (TFS).
  C. Develop a custom data lineage solution.
  D. Develop a Data Quality Services (DQS) solution.
  C. Develop a custom data lineage solution.

• Question 320:

  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 Microsoft Azure SQL Data Warehouse instance that must be available six months a day for reporting.

  You need to pause the compute resources when the instance is not being used.

  Solution: You use SQL Server Configuration Manager. Does the solution meet the goal?

  A. Yes
  B. No
  B. No

  ---

  To pause a SQL Data Warehouse database, use any of these individual methods.

  Pause compute with Azure portal

  Pause compute with PowerShell

  Pause compute with REST APIs

  References:

  https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-manage- compute-overview