High quality 70-767 Braindumps 2021

NEW QUESTION 1
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 have an on-premises Microsoft SQL Server instance and a Microsoft Azure SQL Data Warehouse instance. You move data from the on-premises database to the data warehouse once each day by using a SQL Server Integration Services (SSIS) package.
You observe that the package no longer completes within the allotted time. You need to determine which tasks are taking a long time to complete.
Solution: You alter the package to log the start and completion times for a task to a table in the on-premises SQL Server instance.
Does the solution meet the goal?

• A. Yes
• B. No

Answer: A

NEW QUESTION 2
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.
Your company uses Microsoft SQL Server to deploy a data warehouse to an environment that has a SQL Server Analysis Services (SSAS) instance. The data warehouse includes the Fact.Order table as shown in the following table definition. The table has no indexes.

You must minimize the amount of space that indexes for the Fact.Order table consume. You run the following queries frequently. Both queries must be able to use a columnstore index:

You need to ensure that the queries complete as quickly as possible.
Solution: You create one columnstore index that includes the [Order Date Key], [Tax Amount], and [Total Excluding Tax] columns.
Does the solution meet the goal?

• A. Yes
• B. No

Answer: A

Explanation: You should use a columnstore index.
Columnstore indexes are the standard for storing and querying large data warehousing fact tables. This index uses column-based data storage and query processing to achieve gains up to 10 times the query performance in your data warehouse over traditional row-oriented storage.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-serv

NEW QUESTION 3
You create a Microsoft SQL Server Integration Services (SSIS) package as shown in the SSIS Package exhibit. (Click the Exhibit button.)

The package uses data from the Products table and the Prices table. Properties of the Prices source are shown in the OLE DB Source Editor exhibit (Click the Exhibit Button.) and the Advanced Editor for Prices exhibit (Click the Exhibit button.)


You join the Products and Prices tables by using the ReferenceNr column. You need to resolve the error with the package.
For each of the following statements, select Yes if the statement is true. Otherwise, select No. NOTE: Each correct selection is worth one point.

Answer:

Explanation: There are two important sort properties that must be set for the source or upstream transformation that supplies data to the Merge and Merge Join transformations:
The Merge Join Transformation requires sorted data for its inputs.
If you do not use a Sort transformation to sort the data, you must set these sort properties manually on the source or the upstream transformation.
References:
https://docs.microsoft.com/en-us/sql/integration-services/data-flow/transformations/sort-data-for-the-merge-and-

NEW QUESTION 4
You have a data warehouse named DW1. All data files are located on drive E. You expect queries that pivot hundreds of millions of rows for each report. You need to modify the data files to minimize latency.
What should you do?

• A. Add more data files to DW1 on drive E.
• B. Add more data files to tempdb on drive E.
• C. Remove data files from tempdb
• D. Remove data files from DW1.

Answer: B

Explanation: The number of files depends on the number of (logical) processors on the machine. As a general rule, if the number of logical processors is less than or equal to eight, use the same number of data files as logical processors. If the number of logical processors is greater than eight, use eight data files and then if contention continues, increase the number of data files by multiples of 4 until the contention is reduced to acceptable levels or make changes to the workload/code.
References: https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database

NEW QUESTION 5
You manage Master Data Services (MDS). You plan to create entities and attributes and load them with the data. You also plan to match data before loading it into Data Quality Services (DQS).
You need to recommend a solution to perform the actions.
What should you recommend?

• A. MDS Add-in for Microsoft Excel
• B. MDS Configuration Manager
• C. Data Quality Matching
• D. MDS repository

Answer: A

Explanation: In the Master Data Services Add-in for Excel, matching functionality is provided by Data Quality Services (DQS). This functionality must be enabled to be used.
To enable Data Quality Services integration
Open Master Data Services Configuration Manager.
In the left pane, click Web Configuration.
On the Web Configuration page, select the website and web application.
In the Enable DQS Integration section, click Enable integration with Data Quality Services.
On the confirmation dialog box, click OK.
References:
https://docs.microsoft.com/en-us/sql/master-data-services/install-windows/enable-data-quality-services-integrati

NEW QUESTION 6
You plan to use the dtutil.exe utility with Microsoft SQL Server Integration Services (SSIS) to customize packages. You need to create a new package ID for package1 on Server1. Which dtutil.exe command should you run?

• A. dtutil.exe /FILE c:repositorypackagel.dtsx /DestServer Server! /COPY SQL;package1.dtsx
• B. dtutil.exe /I /FILE c:repositorypackagel.dtsx
• C. dtutil.exe /SQL package1 /COPY OTS;c:repositorypackage1.dtsx
• D. dtutil.exe /SQL package1 /DELETE

Answer: A

NEW QUESTION 7
A database has tables named Table1, Table2, and Table3.
Table1 has a foreign key relationship with Table2.
Table2 has a foreign key relationship with Table3.
Table1 does not have a direct relationship with Table3.
You need to recommend an appropriate dimension usage relationship. What should you recommend?

• A. many-to-one relationship
• B. referenced relationship
• C. regular dimension relationship
• D. fact relationship

Answer: B

Explanation: A reference dimension relationship between a cube dimension and a measure group exists when the key column for the dimension is joined indirectly to the fact table through a key in another dimension table, as shown in the following illustration.

NEW QUESTION 8
You deploy a Microsoft Server database that contains a staging table named EmailAddress_Import. Each night, a bulk process will import customer information from an external database, cleanse the data, and then insert it into the EmailAddress table. Both tables contain a column named EmailAddressValue that stores the email address.
You need to implement the logic to meet the following requirements:
Email addresses that are present in the EmailAddress_Import table but not in the EmailAddress table must be inserted into the EmailAddress table.
Email addresses that are not in the EmailAddress_Import but are present in the EmailAddress table must be deleted from the EmailAddress table.
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.

Answer:

Explanation: Box 1: EmailAddress
The EmailAddress table is the target. Box 2: EmailAddress_import
The EmailAddress_import table is the source. Box 3: NOT MATCHED BY TARGET
Box 4: NOT MATCHED BY SOURCE
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql

NEW QUESTION 9
You need to load data from a CSV file to a table.
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: Each correct selection is worth one point.

Answer:

Explanation: The Merge transformation combines two sorted datasets into a single dataset. The rows from each dataset are inserted into the output based on values in their key columns.
By including the Merge transformation in a data flow, you can merge data from two data sources, such as tables and files.
References:
https://docs.microsoft.com/en-us/sql/integration-services/data-flow/transformations/merge-transformation?view

NEW QUESTION 10
You are developing a Microsoft SQL Server Master Data Services (MDS) solution.
The model contains an entity named Product. The Product entity has three user-defined attributes named Category, Subcategory, and Price, respectively.
You need to ensure that combinations of values stored in the Category and Subcategory attributes are unique. What should you do?

• A. Create an attribute group that consists of the Category and Subcategory attribute
• B. Publish a business rule for the attribute group.
• C. Publish a business rule that will be used by the Product entity.
• D. Create a derived hierarchy based on the Category and Subcategory attribute
• E. Use the Category attribute as the top level for the hierarchy.
• F. Set the value of the Attribute Type property for the Category and Subcategory attributes toDomainbased.

Answer: B

Explanation: In Master Data Services, business rule actions are the consequence of business rule condition evaluations. If a condition is true, the action is initiated.
The Validation action "must be unique": The selected attribute must be unique independently or in combination with defined attributes.

NEW QUESTION 11
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?

• A. the Slowly Changing Dimension transformation
• B. the Conditional Split transformation
• C. the Merge transformation
• D. the Data Conversion transformation
• E. an Execute SQL task
• F. the Aggregate transformation
• G. the Lookup transformation

Answer: B

Explanation: The Conditional Split transformation can route data rows to different outputs depending on the content of the data. The implementation of the Conditional Split transformation is similar to a CASE decision structure in a programming language. The transformation evaluates expressions, and based on the results, directs the data row to the specified output. This transformation also provides a default output, so that if a row matches no expression it is directed to the default output.
References:
https://docs.microsoft.com/en-us/sql/integration-services/data-flow/transformations/conditionalsplit-
Transformation

NEW QUESTION 12
You are testing a Microsoft SQL Server Integration Services (SSIS) package. The package includes the Control Flow task shown in the Control Flow exhibit (Click the Exhibit button) and the Data Flow task shown in the Data Flow exhibit. (Click the Exhibit button.)


You declare a variable named Seed as shown in the Variables exhibit. (Click the Exhibit button.) The variable is changed by the Script task during execution.

You need to be able to interrogate the value of the Seed variable after the Script task completes execution. For each of the following statements, select Yes if the statement is true. Otherwise, select No.

Answer:

Explanation: References:
https://docs.microsoft.com/en-us/sql/integration-services/variables-window

NEW QUESTION 13
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.

Answer:

Explanation:

NEW QUESTION 14
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 the following line-of-business solutions:
ERP system
Online WebStore
Partner extranet
One or more Microsoft SQL Server instances support each solution. Each solution has its own product catalog. You have an additional server that hosts SQL Server Integration Services (SSIS) and a data warehouse. You populate the data warehouse with data from each of the line-of-business solutions. The data warehouse does not store primary key values from the individual source tables.
The database for each solution has a table named Products that stored product information. The Products table in each database uses a separate and unique key for product records. Each table shares a column named ReferenceNr between the databases. This column is used to create queries that involve more than once solution.
You need to load data from the individual solutions into the data warehouse nightly. The following requirements must be met:
If a change is made to the ReferenceNr column in any of the sources, set the value of IsDisabled to True and create a new row in the Products table.
If a row is deleted in any of the sources, set the value of IsDisabled to True in the data warehouse. Solution: Perform the following actions:
Enable the Change Tracking feature for the Products table in the three source databases.
Query the CHANGETABLE function from the sources for the deleted rows.
Set the IsDIsabled column to True on the data warehouse Products table for the listed rows. Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: We must check for updated rows, not just deleted rows.
References: https://www.timmitchell.net/post/2021/01/18/getting-started-with-change-tracking-in-sql-server/

NEW QUESTION 15
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. CDC Control task
• B. CDC Splitter
• C. Union All
• D. XML task
• E. Fuzzy Grouping
• F. Merge
• G. Merge Join

Answer: E

Explanation: The Fuzzy Grouping transformation performs data cleaning tasks by identifying rows of data that are likely to be duplicates and selecting a canonical row of data to use in standardizing the data.

NEW QUESTION 16
You have a data quality project that focuses on the Products catalog for the company. The data includes a product reference number.
The product reference should use the following format: Two letters followed by an asterisk and then four or five numbers. An example of a valid number is XX*55522. Any reference number that does not conform to the format must be rejected during the data cleansing.
You need to add a Data Quality Services (DQS) domain rule in the Products domain. Which rule should you use?

• A. value matches pattern ZA*9876[5]
• B. value matches pattern AZ[*]1234[5]
• C. value matches regular expression AZ[*]1234[5]
• D. value matches pattern [a-zA-Z][a-zA-Z]*[0-9][0-9] [0-9][0-9] [0-9]?

Answer: A

Explanation: For a pattern matching rule:
Any letter (A…Z) can be used as a pattern for any letter; case insensitive Any digit (0…9) can be used as a pattern for any digit
Any special character, except a letter or a digit, can be used as a pattern for itself Brackets, [], define optional matching
Example: ABC:0000
This rule implies that the data will contain three parts: any three letters followed by a colon (:), which is again followed by any four digits.

NEW QUESTION 17
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.

Answer:

Explanation: Box 1:
The default setting is SUM (fully additive). Box 2:
FirstNonEmpty: The member value is evaluated as the value of its first child along the time dimension that contains data.
References:
https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models/define-semiadditive-behavior

NEW QUESTION 18
You have a Microsoft SQL Server Integration Services (SSIS) package that contains a Data Flow task as shown in the Data Flow exhibit. (Click the Exhibit button.)

You install Data Quality Services (DQS) on the same server that hosts SSIS and deploy a knowledge base to manage customer email addresses. You add a DQS Cleansing transform to the Data Flow as shown in the Cleansing exhibit. (Click the Exhibit button.)

You create a Conditional Split transform as shown in the Splitter exhibit. (Click the Exhibit button.)

You need to split the output of the DQ5 Cleansing task to obtain only Correct values from the EmailAddress column. For each of the following statements, select Yes if the statement is true. Otherwise, select No.

Answer:

Explanation:

NEW QUESTION 19
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 are configuring a Microsoft SQL server named ow1 for a new data warehouse. The server contains eight drives and eight processor cores. Each drive uses a separate physical disk.
You need to configure storage for the tempdb database. The solution must minimize the amount of time it takes to process daily ETL jobs.
Solution: You configure eight files for the tenpdb database. You place the files on a drive that contains the operating system files.
Does this meet the goal?

• A. Yes
• B. No

Answer: B