70-767

70-767 practice questions, page 5 of 6, with answer review and explanations.

Question 21

Your company has a Microsoft SQL Server data warehouse instance. The human resources department assigns all employees a unique identifier. You plan to store this identifier in a new table named Employee. You create a new dimension to store information about employees by running the following Transact-SQL statement: You have not added data to the dimension yet. You need to modify the dimension to implement a new column named [EmployeeKey]. The new column must use unique values. How should you complete the Transact-SQL statements? To answer, select the appropriate Transact-SQL segments in the answer area. Hot Area:

Question 22

You deploy a Microsoft Azure SQL Data Warehouse instance. The instance must be available eight hours each day. You need to pause Azure resources when they are not in use to reduce costs. What will be the impact of pausing resources? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Hot Area:

Question 23

You have a Microsoft SQL Server Data Warehouse instance that uses SQL Server Analysis Services (SSAS). The instance has a cube containing data from an on-premises SQL Server instance. A measure named Measure1 is configured to calculate the average of a column. You plan to change Measure1 to a full additive measure and create a new measure named Measure2 that evaluates data based on the first populated row. You need to configure the measures. What should you do? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Hot Area:

Question 24

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 a database administrator for an e-commerce company that runs an online store. The company has three databases as described in the following table. You plan to load at least one million rows of data each night from DB1 into the OnlineOrder table. You must load data into the correct partitions using a parallel process. You create 24 Data Flow tasks. You must place the tasks into a component to allow parallel load. After all of the load processes compete, the process must proceed to the next task. You need to load the data for the OnlineOrder table. What should you use?

  1. Lookup transformation
  2. Merge transformation
  3. Merge Join transformation
  4. MERGE statement
  5. Union All transformation
  6. Balanced Data Distributor transformation
  7. Sequential container
  8. Foreach Loop container

Question 25

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 a database administrator for an e-commerce company that runs an online store. The company has the databases described in the following table. Each day, you publish a Microsoft Excel workbook that contains a list of product names and current prices to an external website. Suppliers update pricing information in the workbook. Each supplier saves the workbook with a unique name. Each night, the Products table is deleted and refreshed from MDS by using a Microsoft SQL Server Integration Services (SSIS) package. All files must be loaded in sequence. You need to add a data flow in an SSIS package to perform the Excel files import in the data warehouse. What should you use?

  1. Lookup transformation
  2. Merge transformation
  3. Merge Join transformation
  4. MERGE statement
  5. Union All transformation
  6. Balanced Data Distributor transformation
  7. Sequential container
  8. Foreach Loop container