070-767 無料問題集「Microsoft Implementing a SQL Data Warehouse」

You have a data warehouse that includes three databases named DB1, DB2, and DB3 respectively.
The data warehouse no longer meets the needs of users.
You plan to modify the design of the data warehouse. The dimensions in the databases have the following requirements:
* DB1 must support overwriting history.
* DB2 must support preserving all history.
* DB3 must support preserving a version of history.
You need to design the slowly changing dimensions (SCDs) for the databases.
For each database, which SCD type should you use? To answer, select the appropriate SCD types in the answer area.

You have a data warehouse that includes a table named [Fact]. [sales]. The table has four partitions. There are no foreign keys defined on the [Fact].[sales] table.
You identify an issue with the first partition. You must reload the data for the first partition as quickly as possible.
You need to prepare the partition for reloading data.
How should you complete the Transact-SQL statement? 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.

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?

You need to recommend a storage solution for a data warehouse that minimizes load times. The solution must provide availability if a hard disk fails.
Which RAID configuration should you recommend for each type of database file? To answer, drag the appropriate RAID configurations to the correct database file types. Each RAID configuration 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 designing a data transformation process using Microsoft SQL Server Integration Services (SSIS).
You need to ensure that every row is compared with every other row during transformation.
What should you configure? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.

You are developing a Microsoft SQL Server Integration Services (SSIS) package. You create a data flow that has the following characteristics:
* The package moves data from the table [source].Tabid to DW.Tablel.
* All rows from [source].Table1 must be captured in DW.Tablel for error.Tablel.
* The table error.Tablel must accept rows that fail upon insertion into DW.Tablel due to violation of nullability or data type errors such as an invalid date, or invalid characters in a number.
* The behavior for the Error Output on the "OLE DB Destination" object is Redirect.
* The data types for all columns in [sourceJ.Tablel are VARCHAR. Null values are allowed.
* The Data access mode for both OLE DB destinations is set to Table or view - fast load.


Use the drop-down menus to select the answer choice that answers each question.

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.
Business users observe that the value displayed for the profit margin is the average profit margin across all products. The profit margin must be calculated per product.
You need to modify the existing model without changing the overall structure of the data model.
What should you use?

You have a database named DB1. You create a Microsoft SQL Server Integration Services (SSIS) package that incrementally imports data from a table named Customers. The package uses an OLE DB data source for connections to DB1. The package defines the following variables.

To support incremental data loading, you create a table by running the following Transact-SQL segment:

You need to create a DML statements that updates the LastKeyByTable table.
How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in the dialog box in the answer area.

You have a Microsoft SQL Server Integration Services (SSIS) package that includes the control flow shown in the following diagram.

You need to choose the enumerator for the Foreach Loop container.
Which enumerator should you use?

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 create a set of Microsoft SQL Server Integration Services (SSIS) packages to support an ETL process.
You need to deploy the SSIS packages and meet the following requirements:
* Maximize ease of use for backup processes.
* Manage security of packages from a single place.
* Maintain versioning of packages.
* Implement parameters to assign values to packages.
* Use stored procedures to run SSIS packages.
* Use SQL Server Agent to run packages.
Solution: You use the Package Deployment model. You save the packages to the file system.
Does this meet the goal?