Microsoft 70-767 Online Practice Questions and Exam Preparation

Microsoft 70-767 Online Questions & Answers

• Question 291:

  You are designing a data warehouse that uses SQL Server 2016.

  The data warehouse contains a table named factSales that stores product sales. The table has a clustered index on the primary key, four foreign keys to dimension tables, and an aggregate column for sales totals. All key columns use the int

  data type and the aggregate column uses the money data type.

  You need to increase the speed of data retrieval from the factSales table.

  Which index type should you add to the table?

  A. Clustered
  B. Semantic search
  C. Nonclustered
  D. XML
  C. Nonclustered

• Question 292:

  You are designing an enterprise star schema that will consolidate data from three independent data marts. One of the data marts is hosted on SQL Azure.

  Most of the dimensions have the same structure and content. However, the geography dimension is slightly different in each data mart.

  You need to design a consolidated dimensional structure that will be easy to maintain while ensuring that all dimensional data from the three original solutions is represented.

  What should you do?

  A. Create a junk dimension for the geography dimension.
  B. Implement change data capture.
  C. Create a conformed dimension for the geography dimension.
  D. Create three geography dimensions.
  C. Create a conformed dimension for the geography dimension.

• Question 293:

  You are completing the installation of the Data Quality Server component of SQL Server Data Quality Services (DQS).

  You need to complete the post-installation configuration.

  What should you do?

  A. Run the Data Quality Server Installer.
  B. Install the data providers that are used for data refresh.
  C. Run the dbimpexp.exe command.
  D. Install the Analysis Services OLE DB Provider.
  A. Run the Data Quality Server Installer.

• Question 294:

  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. Source Assistant
  B. Destination Assistant
  C. Audit
  D. Script Component
  D. Script Component

  ---

  Explanation: Script Component Transformation:

  The transformation that uses script to extract, transform, or load data.

  Note:

  * SQL Server Integration Services provides three different types of data flow components:

  sources, transformations, and destinations. Sources extract data from data stores such as tables and views in relational databases, files, and Analysis Services databases. Transformations modify, summarize, and clean data. Destinations

  load data into data stores or create in-memory datasets.

  Incorrect:

  Not A, Not B: No Data Transforms called Source Assistant or Destination Assistant Not C: Not related to auditing

• Question 295:

  HOTSPOT

  You are developing a SQL Server Integration Services (SSIS) package to load data into a SQL Server 2016 database.

  The name of the target instance is passed to the package via an Environment variable.

  The Environment variable provides the server name to the Project parameter named ServerName.

  The OLE DB Project Connection Manager has been parameterized.

  You need to configure the Connection Manager property to accept the parameter.

  Which property and parameter should you use? (To answer, configure the appropriate option or options in the dialog box in the answer area.)

  Hot Area:

  

  

• Question 296:

  CORRECT TEXT - (Topic 2)

  You are designing a package control flow. The package moves sales order data from a SQL Azure transactional database to an on-premise reporting database. The package will run several times a day, while new sales orders are being

  added to the transactional database. The current design of the package control flow is shown in the answer area.

  (Click the Exhibit button.)

  

  The Insert New Orders Data Flow task must meet the following requirements:

  -Usage of the tempdb database should not be impacted.

  -Concurrency should be maximized, while only reading committed transactions.

  -If the task fails, only that task needs to be rolled back.

  You need to configure the Insert New Orders Data Flow task to meet the requirements.

  How should you configure the transaction properties? (To answer, select the appropriate setting or settings in the answer area.)

  

  A. Check the answer below
  A. Check the answer below

  ---

  Answer: IsolationLevel = ReadCommited. TransactionOption = Required http://msdn.microsoft.com/en-us/library/ms137690.aspx http://msdn.microsoft.com/en-us/library/ms137749.aspx http://msdn.microsoft.com/enus/library/microsoft.sqlserver.dts.runtime.dtscontainer.isolationlevel.aspx http://msdn.microsoft.com/en-us/library/ms173763.aspx

• Question 297:

  You have a database named Sales and a data warehouse named DataW.

  From Sales, you plan to bulk insert to a table in DataW that is more than 2TB. The process will insert a minimum of 11 million rows simultaneously.

  You need to identify which data storage strategy must be used to minimize load times.

  Which data storage strategy should you identify?

  A. a Clustered Columnstore
  B. a file table.
  C. a durable In-Memory OLTP
  D. a page-compressed heap
  A. a Clustered Columnstore

• Question 298:

  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 need to ensure that the queries complete as quickly as possible.

  Solution: You create 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 299:

  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 multi-dimensional models.

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

  The data warehouse includes the following tables:

  

  You must implement a partitioning scheme for the fact. Transaction table to move older data to less expensive storage. Each partition will store data for a single calendar year, as shown in the exhibit (Click the Exhibit button.) You must align the partitions.

  

  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.

  End of repeated scenario

  You need to perform the first step to partition the fact .Transaction table.

  How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area.

  Hot Area:

  

  

• Question 300:

  You are editing a SQL Server Integration Services (SSIS) package that contains three Execute SQL tasks and no other tasks. The three Execute SQL tasks modify products in staging tables in preparation for a data warehouse load.

  The package and all three Execute SQL product tasks have their TransactionOption property set to Supported.

  You need to ensure that if any of the three Execute SQL product tasks fail, all three tasks will roll back their changes.

  What should you do?

  A. Change the TransactionOption property of the package to Required.
  B. Change the TransactionOption property of all three Execute SQL product tasks to Required.
  C. Move the three Execute SQL product tasks into a Foreach Loop container.
  D. Move the three Execute SQL product tasks into a Sequence container.
  A. Change the TransactionOption property of the package to Required.

  ---

  http://msdn.microsoft.com/en-us/library/ms137690.aspx http://msdn.microsoft.com/en-us/library/ms141144.aspx