Online Access Free 70-767 Exam Questions

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?

• A.star schema
• B.non-additive measure
• C.semi-additive measure
• D.dimension table reference relationship

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.

Correct Answer:

Explanation

Box 1: RAID 5
RAID 5 is the similar to that of RAID 0 provided that the number of disks is the same. However, due to the fact that it is useless to read the parity data, the read speed is just (N-1) times faster but not N times as in RAID
0.
Box 2: RAID 10
Always place log files on RAID 1+0 (or RAID 1) disks. This provides better protection from hardware failure, and better write performance.
Note: In general RAID 1+0 will provide better throughput for write-intensive applications. The amount of performance gained will vary based on the HW vendor's RAID implementations. Most common alternative to RAID 1+0 is RAID 5. Generally, RAID 1+0 provides better write performance than any other RAID level providing data protection, including RAID 5.

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 need to cleanse a data flow source by removing duplicate records based on approximate matches.
Which SSIS Toolbox item should you use?

• A.Merge Join
• B.CDC Control task
• C.Union All
• D.Fuzzy Grouping
• E.CDC Splitter
• F.XML task
• G.Merge

You manage a data warehouse in a Microsoft SQL Server instance. Company employee information is imported from the human resources system to a table named Employee in the data warehouse instance. The Employee table was created by running the query shown in the Employee Schema exhibit. (Click the Exhibit button.)

The personal identification number is stored in a column named EmployeeSSN. All values in the EmployeeSSN column must be unique.
When importing employee data, you receive the error message shown in the SQL Error exhibit. (Click the Exhibit button.).

You determine that the Transact-SQL statement shown in the Data Load exhibit in the cause of the error.
(Click the Exhibit button.)

You remove the constraint on the EmployeeSSN column. You need to ensure that values in the EmployeeSSN column are unique.
For each of the following statements, select Yes if the statement is true. Otherwise, select No.
NOTE: Each correct selection is worth one point.

Correct Answer:

Explanation

With the ANSI standards SQL:92, SQL:1999 and SQL:2003, an UNIQUE constraint must disallow duplicate non-NULL values but accept multiple NULL values.
In the Microsoft world of SQL Server however, a single NULL is allowed but multiple NULLs are not.
From SQL Server 2008, you can define a unique filtered index based on a predicate that excludes NULLs.
References:
https://stackoverflow.com/questions/767657/how-do-i-create-a-unique-constraint-that-also-allows-nulls

You manage Master Data Services (MDS).
You need to create a new entity with the following requirements:
* Maximize the performance of the MDS system.
* Ensure that the Entity change logs are stored.
You need to configure the Transaction Log Type setting. Which type should you use?

• A.Attribute
• B.Full
• C.None
• D.Member
• E.Simple