Microsoft 70-767 Online Practice Questions and Exam Preparation

Microsoft 70-767 Online Questions & Answers

• Question 91:

  You are reviewing the design of a student dimension table in an existing data warehouse hosted on SQL Azure.

  The current dimension design does not allow the retention of historical changes to student attributes such as ParentOccupation.

  You need to redesign the dimension to enable the full historical reporting of changes to multiple student attributes including ParentOccupation. What should you do?

  A. Add CurrentValue and PreviousValue columns to the student dimension.
  B. Enable Snapshot Isolation on the data warehouse.
  C. Add an IsCurrent column to the student dimension.
  D. Add StartDate and EndDate columns to the student dimension.
  D. Add StartDate and EndDate columns to the student dimension.

  ---

  Explanation: Adding a start and end date will give you this ability as when a record is inserted and given a start and end date, you'll have the ability to determine when they were active therefore giving you a retention of historical changes

• Question 92:

  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. You have a fact table that includes sales data for all products. The model includes a dimension named Geography that stores all

  geographies. You create a dimension that has a foreign key and provides the ability to analyze sales by the following sales channels: Internet or retail store.

  You need to update the data model to allow business users to analyze Internet sales by geography without changing 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
  D. slowly changing dimension (SCD)

• Question 93:

  You are developing a data flow transformation to merge two data sources. One source contains product data and the other source contains data about the country in which the product was manufactured. Both data sources contain a two-character CountryCode column and both use SQL Server. Both data sources contain an ORDER BY clause to sort the data by the CountryCode column in ascending order.

  You use a Merge Join transformation to join the data.

  

  You need to ensure that the Merge Join transformation works correctly without additional transformations. What should you do? (Each answer presents a part of the solution. Choose all that apply.)

  A. set the appropriate SortKeyPosition properties on the data sources.
  B. set the ValidateExternalMetaData property on the Merge Join transformation to True.
  C. set the IsSorted property on both data sources.
  D. Set the MaxBuffersPerlnput property on the Merge Join transformation to 2.
  A. set the appropriate SortKeyPosition properties on the data sources.
  C. set the IsSorted property on both data sources.

  ---

  http://msdn.microsoft.com/en-us/library/ms141775.aspx http://msdn.microsoft.com/en-us/library/ms137653.aspx http://siddhumehta.blogspot.com/2009/05/validateexternalmetadata-property.html http://msdn.microsoft.com/en-us/library/ ms135950.aspx

• Question 94:

  You administer a Microsoft SQL Server 2016 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit.

  

  Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table. You need to assign the appropriate constraints and table properties to ensure data integrity and visibility. On which column in the Employee table should you create a unique constraint?

  A. DateHired
  B. DepartmentID
  C. EmployeelD
  D. EmployeeNum
  E. FirstName
  F. JobTitle
  G. LastName
  H. MiddleName
  I. ReportsToID
  D. EmployeeNum

• Question 95:

  You develop a SQL Server Integration Services (SSIS) package that imports Windows Azure SQL Database data into a data warehouse every night.

  The Windows Azure SQL Database data contains many misspellings and variations of abbreviations. To import the data, a developer used the Fuzzy Lookup transformation to choose the closest-matching string from a reference table of

  allowed values. The number of rows in the reference table is very large.

  If no acceptable match is found, the Fuzzy Lookup transformation passes a null value.

  The current setting for the Fuzzy Lookup similarity threshold is 0.50.

  Many values are incorrectly matched.

  You need to ensure that more accurate matches are made by the Fuzzy Lookup transformation without degrading performance.

  What should you do?

  A. Change the similarity threshold to 0.40.
  B. Decrease the maximum number of matches per lookup.
  C. Change the similarity threshold to 0.85.
  D. Increase the maximum number of matches per lookup.
  C. Change the similarity threshold to 0.85.

  ---

  Explanation: * Similarity threshold Set the similarity threshold at the component level by using the slider. The closer the value is to 1, the closer the resemblance of the lookup value to the source value must be to qualify as a match. Increasing the threshold can improve the speed of matching since fewer candidate records need to be considered.

• Question 96:

  You are developing a Microsoft SQL Server Integration Services (SSIS) package that loads a data warehouse. You need to inspect the data that is being processed by the package. What should you do first?

  A. Set a break point on the Control Flow path.
  B. Enable SQL Trace.
  C. Enable logging on the Data Flow path.
  D. Enable a data viewer on the Data Flow path.
  A. Set a break point on the Control Flow path.

• Question 97:

  DRAG DROP

  You are developing a SQL Server Integration Services (SSIS) package that downloads data from a Windows Azure SQL Database database.

  A stored procedure will be called in an Execute SQL task by using an ODBC connection.

  This stored procedure has only the @CustomerID parameter of type INT.

  A project parameter named CustID will be mapped to the stored procedure parameter @CustomerID.

  You need to ensure that the value of the CustID parameter is passed to the @CustomerID stored procedure parameter.

  In the Parameter Mapping tab of the Execute SQL task editor, how should you configure the parameter? (To answer, drag the appropriate option or options to the correct location or locations in the answer area.)

  Select and Place:

  

  

• Question 98:

  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 data warehouse that stores information about products, sales, and orders for a manufacturing company. The instance contains a database that has two tables named SalesOrderHeader and SalesOrderDetail. SalesOrderHeader has 500,000 rows and SalesOrderDetail has 3,000,000 rows.

  Users report performance degradation when they run the following stored procedure:

  

  You need to optimize performance.

  Solution: You run the following Transact-SQL statement:

  

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

  ---

  100 out of 500,000 rows is a too small sample size.

  References: https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data- warehouse-tables-statistics

• Question 99:

  DRAG DROP

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

  The package contains an ADO object source variable that holds a result set that was returned by a stored procedure execution.

  You need to add and configure a container that will execute several tasks for each row in the ADO object source variable.

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

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

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

  The product 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 product surrogate key and attributes. The other table will hold the most recent product surrogate key and attributes.
  B. Design a table as a Type 2 SCD dimension that holds the surrogate key for each product and its attributes. Limit the number of records per product within the table to two.
  C. Design a table to hold the surrogate key for each product and its attributes as well as additional columns to store the original attributes.
  D. Design a table to hold the surrogate key for each product and its attributes. Add a column to store an indicator that identifies the record as original data or recent data.
  C. Design a table to hold the surrogate key for each product and its attributes as well as additional columns to store the original attributes.