Microsoft 70-767 Online Practice Questions and Exam Preparation

Microsoft 70-767 Online Questions & Answers

• Question 31:

  You manage an inventory system that has a table named Products. The Products table has several hundred columns.

  You generate a report that relates two columns named ProductReference and ProductName from the Products table. The result is sorted by a column named QuantityInStock from largest to smallest.

  You need to create an index that the report can use.

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

  Hot Area:

  

  

• Question 32:

  You administer a Microsoft SQL Server 2016 server that has SQL Server Integration Services (SSIS) installed.

  You plan to deploy new SSIS packages to the server. The SSIS packages use the Project Deployment Model together with parameters and Integration Services environment variables.

  You need to configure the SQL Server environment to support these packages.

  What should you do?

  A. Create SSIS configuration files for the packages.
  B. Create an Integration Services catalog.
  C. Install Data Quality Services.
  D. Install Master Data services.
  B. Create an Integration Services catalog.

  ---

  Reference: http://msdn.microsoft.com/en-us/library/hh479588.aspx http://msdn.microsoft.com/en-us/library/hh213290.aspx http://msdn.microsoft.com/en-us/library/hh213373.aspx

• Question 33:

  HOTSPOT

  You have a data warehouse that is hosted in a SQL Server instance. The data in the data warehouse is loaded by running bcp.exe You discover that the transaction logs regularly fill up the local hard disk on the server and that a WRITELOG wait is present. You need to reduce the amount of disk space used to store the transaction logs and to remove the WRI7TLOG wait.

  Which two database property settings should you modify? To answer, select the appropriate settings in the answer area.

  Hot Area:

  

  

• Question 34:

  You are designing a data warehouse hosted on SQL Azure. The data warehouse currently includes the dimUser and dimDistrict dimension tables and the factSales fact table. The dimUser table contains records for each user permitted to run reports against the warehouse; and the dimDistrict table contains information about sales districts.

  The system is accessed by users from certain districts, as well as by area supervisors and users from the corporate headquarters.

  You need to design a table structure to ensure that certain users can see sales data for only certain districts. Some users must be permitted to see sales data from multiple districts.

  What should you do?

  A. Add a district column to the dimUser table.
  B. Partition the factSales table on the district column.
  C. Create a userDistrict table that contains primary key columns from the dimUser and dimDistrict tables.
  D. For each district, create a view of the factSales table that includes a WHERE clause for the district.
  C. Create a userDistrict table that contains primary key columns from the dimUser and dimDistrict tables.

• Question 35:

  You have a fact table in a data warehouse that stores financial data. The table contains eight column configured as shown in the following table.

  

  You need to identify a column that can be aggregated across all dimensions. Which column should you identify?

  A. OpeningPrice
  B. StockID
  C. NumberOfTrades
  D. MarketID
  C. NumberOfTrades

  ---

  Aggregates are sometimes referred to as pre-calculated summary data, since aggregations are usually precomputed, partially summarized data, that are stored in new aggregated tables. References: https://en.wikipedia.org/wiki/Aggregate_(data_warehouse)

• Question 36:

  You have a database named OnlineSales that contains a table named Customers. You plan to copy incremental changes from the Customers table to a data warehouse every hour.

  You need to enable change tracking for the Customers table.

  How should you complete the Transact-SQL statements? 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:

  

  

  ---

  Box 1: DATABASE [OnlineSales]

  Before you can use change tracking, you must enable change tracking at the database level. The following example shows how to enable change tracking by using ALTER DATABASE.

  ALTER DATABASE AdventureWorks2012

  SET CHANGE_TRACKING = ON

  (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

  Box 2: CHANGE_TRACKING = ON

  ALTER SET CHANGE_RETENTION

  Box 3: ALTER TABLE [dbo].[Customers]

  Change tracking must be enabled for each table that you want tracked. When change tracking is enabled, change tracking information is maintained for all rows in the table that are affected by a DML operation.

  The following example shows how to enable change tracking for a table by using ALTER TABLE.

  ALTER TABLE Person.Contact

  ENABLE CHANGE_TRACKING

  WITH (TRACK_COLUMNS_UPDATED = ON)

  Box 4: ENABLE CHANGE_TRACKING

  References: https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/enable-and-disable-change-tracking-sql-server

• Question 37:

  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 profile the data.

  Does this meet the goal?

  A. Yes
  B. No
  B. No

  ---

  Use a matching rule.

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

• Question 38:

  You are using SQL Server Data Tools to develop a SQL Server Integration Services (SSIS) project.

  The first package that you create in this project contains a package connection that accesses a Microsoft Excel file. Additional packages in the project must also access this file.

  You need to define and reuse the Excel file connection in all project packages.

  What should you do?

  A. Copy the package Connection Manager and paste it in the second package.
  B. Set the RetainSameConnection property of the package Connection Manager to True.
  C. Convert the package Connection Manager in the first package to a project Connection Manager.
  D. Convert the project to the Package Deployment model.
  C. Convert the package Connection Manager in the first package to a project Connection Manager.

• Question 39:

  You are designing a data warehouse that contains a customer dimension.

  The customer dimension contains the original customer attributes as well as the most recent set of updated attributes. When a customer is updated, the original customer attributes must be preserved.

  The customer dimension design must:

  Support a Type 3 Slowly Changing Dimension (SCD) process

  Minimize the total storage consumed

  You need to design a solution that meets these requirements.

  What should you do?

  A. Design two tables. One table will hold the original customer surrogate key and attributes. The other table will hold the most recent customer surrogate key and attributes.
  B. Design a table as a Type 2 SCD dimension that holds the surrogate key for each customer and its attributes. Limit the number of records per customer within the table to two.
  C. Design a table to hold the surrogate key for each customer and its attributes. Add a column to store an indicator that identifies the record as original data or recent data.
  D. Design a table to hold the surrogate key for each customer and its attributes as well as additional columns to store the original attributes.
  D. Design a table to hold the surrogate key for each customer and its attributes as well as additional columns to store the original attributes.

• Question 40:

  HOTSPOT

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

  You use a Data Profiling task to examine the data from a source system. You need to establish:

  The minimum and maximum dates for the datetime columns in the source data The minimum, maximum, and average values for numeric columns in the source data

  You need to use the appropriate profile type in the Data Profiling task.

  Which profile type should you use? (To answer, select the appropriate profile type in the answer area.)

  Hot Area: