[2020 Discount] Real material for Exam 70-767: Latest and Updated Microsoft 70-767 exam dumps

How to pass the 70-767 exam? No doubt you need the latest 70-767 dumps-https://www.pass4itsure.com/70-767.html MCSA 70-767 Exam Questions & Answers (389).I know the best way to lead your Microsoft exam 70–767,Pass4itsure is recommended for everyone and you will get 100% of your score on the Microsoft 70-767 exam.

Get 12% Discount Coupon On This [2020PASS] ~ Microsoft 70-767 Dumps

Use the coupon code “2020PASS” and enjoy the discount on your Implementing a Data Warehouse using SQL preparation material.Pass4itsure is offering a discount of 12% on the preparation material of all the Implementing a Data Warehouse using SQL 70-767 exam simulator.

[2020] New 70-767 Test and 70-767 Dumps with Actual 70-767 PDF Questions

Exam 70-767 Implementing a Data Warehouse using SQL
Published: October 18, 2016
Languages: English
Audiences: IT professionals
Technology: SQL Server 2016/2017/2019
Credit toward certification: MCSA, MCSE
For more information you can visit https://www.microsoft.com/en-us/learning/exam-70-767.aspx

You may also like…

[2018 Hottest] Best Quality Microsoft 70-461 Dumps Microsoft SQL Server Exam Video PDF Free Download

Microsoft MCSA 70-767 PDF Dumps Free Download

Latest 70-767 PDF Dumps: https://drive.google.com/open?id=1CyxzLVMoosxvSdnoolsGzfUZZh-Ivx83

New Video Training: Implementing a Data Warehouse using SQL 70-767

Microsoft 70-767 Exam Dumps Practice Test Online

QUESTION 1
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 ensure that the package records the current Log Sequence Number (LSN) in the source database before
the package begins reading source tables.
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
Correct Answer: A
The CDC Control task is used to control the life cycle of change data capture (CDC) packages. It handles CDC package
synchronization with the initial load package, the management of Log Sequence Number (LSN) ranges that are
processed in a run of a CDC package.
References: https://docs.microsoft.com/en-us/sql/integration-services/control-flow/cdc-control-task

QUESTION 2
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.
Hot Area:

Correct Answer:

When you configure the Fuzzy Grouping transformation, you can specify the comparison algorithm that the
transformation uses to compare rows in the transformation input. If you set the Exhaustive property to true, the
transformation compares every row in the input to every other row in the input. This comparison algorithm may produce
more accurate results, but it is likely to make the transformation perform more slowly unless the number of rows in the
input is small. References: https://docs.microsoft.com/en-us/sql/integration-services/data-flow/transformations/fuzzygrouping-transformation

QUESTION 3

You are developing a Master Data Management (MDM) solution for a company by using Microsoft SQL Server
Integration Services (SSIS), SQL Server Master Data Services (MDS), and SQL Server Data Quality Services (DQS).
You have an MDS model named Geography that contains the entities described in the following table.

You define a domain-based attribute in the State entity that references the CountryRegion entity. You define another
domain-based attribute in the city entity that references the State and CountryRegion entities. A single derived hierarchy
named Geography supports navigation between the CountryRegion, State, and City levels. Subscription views exist for
all entities. The subscription views have the same name as the entity on which they are based.
You initialize each entity member. New City entity members are imported daily based on customer city values in a
Customer Relationship Management (CRM) database. The CRM database is a SQL Server relational database. When
new cities are imported from the CRM database, the state codes must be standardized to those already defined in the
State entity.
In the CRM database, sales managers and assigned to countries/regions. A sales manager may be assigned to one or
more countries/regions. A country/region may have one or more assigned sales managers. The CRM database contains
a table named ManagerCountryRegion that stores a row for each manager-country/region relationship.
You create the following MDS users and map each user to an Active Directory Domain Services (AD DS) user account:
User1, User2, and User3. Both User1 and User2 belong to the Explorer functional area.

Users must be able to complete the tasks described in the following table. You need to complete the development of the
SSIS data flow to load cities into the City staging table.
Which transformation type should you use for Component 3?
A. Import Column
B. CDC Splitter
C. Conditional Split
D. Slowly Changing Dimension (SCD)
E. Lookup
Correct Answer: A
“When new cities are imported from the CRM database, the state codes must be standardized to those already defined
in the State entity.”
The Import Column transformation reads data from files and adds the data to columns in a data flow. This
transformation has one input, one output, and one error output.
References:
https://docs.microsoft.com/en-us/sql/integration-services/data-flow/transformations/import-column-transformation

QUESTION 4
You are the data steward for a Business Intelligence project.
You must identify duplicate rows stored in a SQL Server table and output discoveries to a CSV file. A Data Quality
Services (DQS) knowledge base has been created to support this project.
You need to produce the CSV file with the least amount of development effort.
What should you do?
A. Create an Integration Services package and use a Data Profiling transform.
B. Create a custom .NET application based on the Knowledgebase class.
C. Create a data quality project.
D. Create a CLR stored procedure based on the Knowledgebase class.
E. Create a Master Data Services (MDS) business rule.
Correct Answer: C
Reference: http://msdn.microsoft.com/en-us/library/hh213052.aspx

QUESTION 5
You are troubleshooting an existing SQL Server Integration Services (SSIS) package. On several occasions, the
package execution does not finish and no data seems to have been transferred.
You need to ensure that package logging occurs. Your solution must minimize deployment and development efforts.
What should you do?
A. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server.
B. Use an msi file to deploy the package on the server.
C. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe
utility.
D. Create a reusable custom logging component and use it in the SSIS project.
E. Open a command prompt and run the dtutil /copy command.
F. Configure the SSIS solution to use the Project Deployment Model.
G. Add an OnError event handler to the SSIS project.
H. Open a command prompt and run the gacutil command.
I. Open a command prompt and run the dtexec /dumperror /conn command.
J. Open a command prompt and run the dtexec /rep /conn command.
K. Configure the output of a component in the package data flow to use a data tap.
Correct Answer: C
http://msdn.microsoft.com/en-us/library/ms141212.aspx http://www.mssqltips.com/sqlservertip/2450/ssis-packagedeployment-model-in-sql-server- 2016-part-1-of-2/ http://www.mssqltips.com/sqlservertip/2450/ssis-packagedeployment-model-in-sql-server- 2016-part-2-of-2/

QUESTION 6
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 a derived hierarchy based on the category and subcategory attributes. Use the category attribute as the top
level for the hierarchy.
B. Publish two business rules, one for each of the Category and Subcategory attributes.
C. Set the value of the Attribute Type property for the Category and Subcategory attributes to Domain-based.
D. Create a custom index that will be used by the Product entity.
Correct Answer: A

QUESTION 7
You create a Master Data Services (MDS) model that manages the master data for a Product dimension. The Product
dimension has the following properties:
1.All the members of the Product dimension have a product type, a product subtype, and a unique product name.
2.Each product has a single product type and a single product subtype.
3.The product type has a one-to-many relationship to the product subtype.
You need to ensure that the relationship between the product name, the product type, and the product subtype is
maintained when products are added to or updates in the database.
What should you add to the model?
A. a subscription view
B. a derived hierarchy
C. a recursive hierarchy
D. an explicit hierarchy
Correct Answer: B
A Master Data Services derived hierarchy is derived from the domain-based attribute relationships that already exist
between entities in a model.
You can create a derived hierarchy to highlight any of the existing domain-based attribute relationships in the model.
Incorrect Answers:
C: In Master Data Services, a recursive hierarchy is a derived hierarchy that includes a recursive relationship. A
recursive relationship exists when an entity has a domain-based attribute based on the entity itself.
D: In Master Data Services, an explicit hierarchy organizes members from a single entity in any way you specify. The
structure can be ragged and unlike derived hierarchies, explicit hierarchies are not based on domain-based attribute
relationships.
References: https://docs.microsoft.com/en-us/sql/master-data-services/derived-hierarchies-master-data-services

QUESTION 8
You manage an inventory system that has a table named Products. The Products table has several hundred columns.
You generate a report that relates two columns named ProductReference and ProductName from the Products table.
The result is sorted by a column named QuantityInStock from largest to smallest.
You need to create an index that the report can use.
How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in
the answer area.
Hot Area:

Correct Answer:

QUESTION 9
Occasionally a job that executes an existing SQL Server Integration Services (SSIS) package does not complete and
nothing is processed.
You need to ensure that package logging occurs. Your solution must minimize deployment and development efforts.
What should you do?
A. Create a reusable custom logging component.
B. Use the gacutil command.
C. Use the Project Deployment Wizard.
D. Run the package by using the dtexec /rep /conn command.
E. Add a data tap on the output of a component in the package data flow.
F. Create an OnError event handler.
G. Use the dtutil /copy command.
H. Deploy the package by using an msi file.
I. Run the package by using the dtexec /dumperror /conn command.
J. Run the package by using the dtexecui.exe utility and the SQL Log provider.
K. Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration.
Correct Answer: J
http://msdn.microsoft.com/en-us/library/ms140246.aspx http://msdn.microsoft.com/en-us/library/hh231187.aspx

QUESTION 10
You are completing the installation of the Data Quality Server component of SQL Server Data Quality Services (DQS).
You need to complete the post-installation configuration.
What should you do?
A. Install the Analysis Services OLE DB Provider.
B. Run the DQSInstaller.exe command.
C. Run the Configuration component in the Data Quality Client.
D. Make the data available for DQS operations.
Correct Answer: B

QUESTION 11
You maintain a SQL Server Integration Services (SSIS) package. The package was developed by using SQL Server
2008 Business Intelligence Development Studio (BIDS).
The package includes custom scripts that must be upgraded.
You need to upgrade the package to SQL Server 2016.
Which tool should you use?
A. SQL Server dtexec utility (dtexec.exe)
B. SQL Server DTExecUI utility (dtexecui.exe)
C. SSIS Upgrade Wizard in SQL Server Data Tools
D. SQL Server Integration Services Deployment Wizard
Correct Answer: C
Explanation: Use the SSIS Package Upgrade Wizard to upgrade SQL Server 2005 Integration Services (SSIS)
packages and SQL Server 2008 Integration Services (SSIS) packages to the package format for the current (2016)
release of SQL Server Integration Services.

QUESTION 12
You are designing a data warehouse that contains a customer dimension.
The customer dimension contains the original customer attributes as well as the most recent set of updated attributes.
When a customer is updated, the original customer attributes must be preserved.
The customer dimension design must:
Support a Type 3 Slowly Changing Dimension (SCD) process
Minimize the total storage consumed
You need to design a solution that meets these requirements.
What should you do?
A. Design two tables. One table will hold the original customer surrogate key and attributes. The other table will hold the
most recent customer surrogate key and attributes.
B. Design a table as a Type 2 SCD dimension that holds the surrogate key for each customer and its attributes. Limit
the number of records per customer within the table to two.
C. Design a table to hold the surrogate key for each customer and its attributes. Add a column to store an indicator that
identifies the record as original data or recent data.
D. Design a table to hold the surrogate key for each customer and its attributes as well as additional columns to store
the original attributes.
Correct Answer: D

QUESTION 13
You are developing a SQL Server Integration Services (SSIS) package to implement an incremental data load strategy.
The package reads rows from a source system and compares them to rows in a destination system. New rows will be
inserted and changed rows will be updated.
You have used a Lookup transformation and a Conditional Split transformation. The Lookup transformation joins the
source and destination table on the business key, and includes all columns from the destination table in the data flow
output.
The Conditional Split transformation inspects the destination columns and directs data flow to either insert new records
or update existing records.
You need to configure the Lookup transformation to ensure that all records flow to the Conditional Split transformation,
regardless of whether the rows match an existing row in the destination table.
Which setting should you select? (To answer, select the appropriate option in the answer area.)
Hot Area:

Correct Answer:

That may be helpful:

Recommend books

Recommend courses

Summarize:

The fresh material helps you to pass 70-767 Exam very easily in first try. https://www.pass4itsure.com/70-767.html The 70-767 Braindumps question answers are deigned according to real exam pattern.Success!