Microsoft Implementing a SQL Data Warehouse - 070-767 Exam Practice Test

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 loading data from an OLTP database to a data warehouse. The database contains a table named Sales.
Sales contains details of records that have a type of refund and records that have a type of sales.
The data warehouse design contains a table for sales data and a table for refund data.
Which component should you use to load the data to the warehouse?

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 chokes, 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:

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.
Users report that they are becoming confused about which city table to use for various queries. You plan to create a new schema named Dimension and change the name of the dbo.diamension_city table to Diamension.city. Data loss is not permissible, and you must not leave traces of the old table in the data warehouse.
The fact. Transaction table has measures named RawCost and Totaisale that calculate the wholesale cost of materials. You plan to create a measure that calculates the profit margin based on the two existing measures.
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.

You must improve performance for queries against the fact.Transaction table. You must implement appropriate indexes and enable the Stretch Database capability.
End of repeated scenario
You need to create the ProfitMargin measure for the fact. Transaction table.
How should you complete the MDX statement? To answer, select the appropriate MDX segments in the answer area.

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?

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 Management Studio (SSMS).
Does the solution meet the goal?

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?

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 have a database named DB1 that has change data capture enabled.
A Microsoft SQL Server Integration Services (SSIS) job runs once weekly. The job loads changes from DB1 to a data warehouse by querying the change data capture tables.
A new version of that integration Services package is released that introduces several errors in the loading process.
You need to roll back the Integration Services package to the previous version. Which stored procedure should you execute?

You are designing an indexing strategy for a data warehouse. The data warehouse contains a table named Table1. Data is bulk inserted into Table1.
You plan to create the indexes configured as shown in the following table.

Which type of index should you use to minimize the query times of each index? To answer, drag the appropriate index types to the correct indexes. 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: 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 plan to deploy a Microsoft SQL server that will host a data warehouse named DB1.
The server will contain four SATA drives configured as a RAID 10 array.
You need to minimize write contention on the transaction log when data is being loaded to the database.
Solution: You configure the server to automatically delete the transaction logs nightly.
Does this meet the goal?

You are developing a Microsoft SQL Server Integration Services (SSIS) package to incrementally load new and changed records from a data source.
The SSIS package must load new records into Table1 and updated records into Table1_Updates. After loading records, the package must call a Transact-SQL statement to process updated rows according to existing business logic.
You need to complete the design of the SSIS package.
Which tasks should you use? To answer, drag the appropriate SSIS objects to the correct targets. Each SSIS object 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.

You are developing a Microsoft SQL Server Integration Services (SSIS) package. You enable the SSIS log provider for the Windows event log. You configure the package to use the ScriptTaskLogEntry event. You create a custom Script task.
You need to ensure that when the script completes, it writes the execution status to the event log on the server that hosts SSIS.
Which code segment should you add to the Script task?

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.
You are importing data from databases at retail stores into a central data warehouse. All stores use the same database schema.
The query being executed against the retail stores is shown below:

The data source property named IsSorted is set to . The output of the transform must be sorted.
You need to add a component to the data flow.
Which SSIS Toolbox item should you use?

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.
When testing a pilot version of the data warehouse, business users observe that the number of products in stock is inaccurate. The number of products in stock always increases and represents the total number of products that have ever been in stock.
You need to correct the existing model and ensure that it reflects the number of in-stock products. You must not change the overall structure of the data model.
What should you do?

You manage a Data Quality Services (DQS) knowledge base.
You need to ensure that DQS will create a synonym relationship between identical tags.
What should you create?

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