Ensurepass

QUESTION 61

You are creating a SQL Server Master Data Services (MDS) model for a company. The source data for the company is stored in a single table that contains the manager-to-subordinate relationships. You need to create a hierarchy representing the organizational structure of the company. Which hierarchy type should you use?

 

A.     Natural

B.      Explicit

C.      Parent

D.     Recursive

 

Correct Answer: D

 

 

QUESTION 62

You develop a SQL Server Integration Services (SSIS) package that imports SQL Azure data into a data warehouse every night. The SQL Azure 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 Exhaustive property to True.

B.      Change the similarity threshold to 0.55.

C.      Change the similarity threshold to 0.40.

D.     Increase the maximum number of matches per lookup.

 

Correct Answer: B

 

 

QUESTION 63

You develop a SQL Server Integration Services (SSIS) package that imports SQL Azure data into a data warehouse every night. The SQL Azure 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.     Decrease the maximum number of matches per lookup.

B.      Change the similarity threshold to 0.55.

C.      Change the Exhaustive property to True.

D.     Increase the maximum number of matches per lookup.

 

Correct Answer: B

 

 

QUESTION 64

You are designing a SQL Server Integration Services (SSIS) package that uploads a file to a table named Orders in a SQL Azure database. The company’s auditing policies have the following requirements:

 

Ÿ   An entry must be written to a dedicated SQL Server log table named OrderLog.

Ÿ   The entry must be written as soon as the file upload task completes.

 

You need to meet the company’s policy requirements. Which event handler should you use?

 

A.     OnProgress

B.      Onlnformation

C.      OnPostExecute

D.     OnComplete

 

Correct Ans
wer: C

 

 

QUESTION 65

You are developing a SQL Server Integration Services (SSIS) package that imports data from a relational database to a data warehouse. You are importing data from a relational table named Projects. The table has change data capture enabled on all columns. You need to process only the most recent values from rows that have been inserted or updated since the previous execution of the package. Which query should you use as the data source?

 

A.     SELECT * FROM cdc.fn_cdc_get_all_changes_Projects (@from_lsn, @to_lsn, N’ all update

old’)

B.      SELECT * FROM cdc.fn_cdc_get_all_changes_Projects (@from_lsn, @to_lsn, N’ all’)

C.      SELECT * FROM cdc.fn_cdc_get_net_changes_Projects (@from_lsn, @to_lsn)

D.     SELECT * FROM cdc.Projects_CT WHERE @from_lsn >= _$start_lsn AND @to_lsn <

_$start_lsn

 

Correct Answer: C

 

 

 

 

 

QUESTION 66

You are designing a SQL Server Integration Services (SSIS) data flow to load sales transactions from a source system into a data warehouse hosted on SQL Azure. One of the columns in the data source is named ProductCode. Some of the data to be loaded will reference products that need special processing logic in the data flow. You need to enable separate processing streams for a subset of rows based on the source product code. Which data flow transformation should you use?

 

A.     Source Assistant

B.      Audit

C.      Destination Assistant

D.     Script Component

 

Correct Answer: D

 

 

QUESTION 67

You are designing a SQL Server Integration Services (SSIS) package that uses the Fuzzy Lookup transformation. The reference data to be used in the transformation does not change. You need to reuse the Fuzzy Lookup match index to increase performance and reduce maintenance. What should you do?

 

A.     Select the GenerateAndPersistNewIndex option in the Fuzzy Lookup Transformation Editor.

B.      Select the GenerateNewIndex option in the Fuzzy Lookup Transformation Editor.

< p class="MsoListParagraph" style="cursor: auto; text-justify: inter-ideograph; margin: 0cm 0cm 0pt 18pt; line-height: normal; text-indent: -18pt; mso-char-indent-count: 0; mso-list: l4 level1 lfo8" align="justify">C.      Select the DropExistingMatchlndex option in the Fuzzy Lookup Transformation Editor.

D.     Execute the sp_FuzzyLookupTableMaintenanceUninstall stored procedure.

E.      Execute the sp_FuzzyLookupTableMaintenanceInvoke stored procedure.

 

Correct Answer: A

 

 

QUESTION 68

You are creating a SQL Server Integration Services (SSIS) package to retrieve product data from two different sources. One source is hosted in a SQL Azure database. Each source contains products for different distributors. Products for each distributor source must be combined for insertion into a single product table destination. You need to select the appropriate data flow transformation to meet this requirement. Which transformation types should you use? (Each correct answer presents a complete solution. Choose all that apply.)

 

A.     Multicast

B.      Merge Join

C.      Term Extraction

D.     Union All

E.      Merge

Correct Answer: DE

 

 

QUESTION 69

You are implementing the indexing strategy for a fact table in a data warehouse. The fact table is named Quotes. The table has no indexes and consists of seven columns:

 

Ÿ   [ID]

Ÿ   [QuoteDate]

Ÿ   [Open]

Ÿ   [Close]

Ÿ   [High]

Ÿ   [Low]

Ÿ   [Volume]

 

Each of the following queries must be able to use
a columnstore index:

 

SELECT AVG ([Close]) AS [AverageClose]

FROM Quotes

WHERE [QuoteDate] BETWEEN ‘20100101’ AND ‘20101231’.

 

SELECT AVG([High] – [Low]) AS [AverageRange]

FROM Quotes

WHERE [QuoteDate] BETWEEN ‘20100101’ AND ‘20101231’.

 

SELECT SUM([Volume]) AS [SumVolume]

FROM Quotes

WHERE [QuoteDate] BETWEEN ‘20100101’ AND ‘20101231’

 

You need to ensure that the indexing strategy meets the requirements. The strategy must also minimize the number and size of the indexes. What should you do?

 

A.       Create one columnstore index that contains [ID], [Close], [High], [Low], [Volume], and

[QuoteDate].

B.       Create three coiumnstore indexes:

Ÿ   One containing [QuoteDate] and [Close]

Ÿ   One containing [QuoteDate], [High], and [Low]

Ÿ   One containing [QuoteDate] and [Volume]

C.       Create one columnstore index that contains [QuoteDate], [Close], [High], [Low], and

[Volume].

D.       Create two columnstore indexes:

Ÿ   One containing [ID], [QuoteDate], [Volume], and [Close]

Ÿ   One containing [ID], [QuoteDate], [High], and [Low]

 

Correct Answer: C

 

 

QUESTION 70

You are creating a SQL Server Master Data Services (MDS) model. This model is used to store a master list of products. An attribute must be added to the Product entity to define the sales manager responsible for each product. You need to create an attribute in the Product entity that prevents users from entering invalid sales manager values. Which type of attribute should you create?

 

A.     Derived

B.      Domain-based

C.      User-defined

D.     Parent

E.      Explicit

F.      Recursive

 

Correct Answer: B

 

Free VCE & PDF File for Microsoft 70-463 Real Exam

Instant Access to Free VCE Files: MCSE|MCSA|MCITP…
Instant Access to Free PDF Files: MCSE|MCSA|MCITP…