Microsoft 70-767 Online Practice Questions and Exam Preparation

Microsoft 70-767 Online Questions & Answers

• Question 201:

  You are deploying a new SQL Server Integration Services (SSIS) package to five servers. The package must meet the following requirements:

  A. Add an OnError event handler to the SSIS project.
  B. Use an msi file to deploy the package on the server.
  C. Open a command prompt and run the gacutil command.
  D. Open a command prompt and run the dtutil /copy command.
  E. Open a command prompt and run the dtexec /rep /conn command.
  F. Open a command prompt and run the dtexec /dumperror /conn command.
  G. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility.
  H. Create a reusable custom logging component and use it in the SSIS project.
  I. Configure the SSIS solution to use the Project Deployment Model.
  J. Configure the output of a component in the package data flow to use a data tap.

  D. Open a command prompt and run the dtutil /copy command.

• Question 202:

  You are administering SQL Server Integration Services (SSIS) permissions on a production server that runs SQL Server 2016.

  Quality Assurance (QA) testers in the company must have permission to perform the following tasks on specific projects:

  

  View and validate projects and packages

  

  View Environments and Environment variables

  

  Execute packages

  You need to grant the minimum possible privileges to the QA testers.

  What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

  A. In the SSISDB database, add QA Tester logons to the ssis_admin role.
  B. In the msdb database, add QA Tester logons to the db_ssisoperator role.
  C. Grant Modify permission in the projects to the QA Tester logons.
  D. Grant Read permission in the SSIS catalog folder, the projects, and the Environments to the QA Tester logons.
  E. Grant Execute permission in the projects to the QA Tester logons.
  F. In the msdb database, add QA Tester logons to the db_ssisItduser role.
  B. In the msdb database, add QA Tester logons to the db_ssisoperator role.
  D. Grant Read permission in the SSIS catalog folder, the projects, and the Environments to the QA Tester logons.

• Question 203:

  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 are the administrator of a Microsoft SOL Server Master Data Services (MDS) instance. The instance contains a model named Geography and a model named customer. The Geography model contains an entity named countryRegion.

  You need to ensure that the countryRegion entity members are available in the customer model.

  Solution: In the Geography model, publish a business rule with a Change Value action.

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

• Question 204:

  You are developing a SQL Server Integration Services (SSIS) package to load data into a data warehouse.

  

  You need to establish the correct order for loading each table to maximize parallel processing. Which order should you use?

  A. 1. DimCurrency, DimScenario, DimAccount in parallel 2. DimOrganization 3. FactFinance
  B. 1. DimCurrency, DimOrganization in parallel 2. DimScenario, DimAccount in parallel 3. FactFinance
  C. 1. DimCurrency, FactFinance in parallel 2. DimOrganization, DimScenario, DimAccount in parallel
  D. 1. FactFinance 2. DimOrganization, DimScenario, DimAccount in parallel 3. DimCurrency
  E. 1. DimCurrency 2. DimOrganization 3. DimScenario, DimAccount in parallel 4. FactFinance
  A. 1. DimCurrency, DimScenario, DimAccount in parallel 2. DimOrganization 3. FactFinance

  ---

  Explanation: References:

  http://msdn.microsoft.com/en-us/library/ms139892.aspx http://msdn.microsoft.com/en-us/library/ms141261.aspx

• Question 205:

  You need to build a knowledge base in Data Quality Services (DQS).

  You need to ensure that the data is validated by using a third-party data source before DQS processes the data.

  Which four actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

  Select and Place:

  

  

  ---

  Building a DQS knowledge base involves the following processes and components:

  Step 1: Perform Knowledge Discovery

  A computer-assisted process that builds knowledge into a knowledge base by processing a data sample

  Step 2: Perform Domain Management

  An interactive process that enables the data steward to verify and modify the knowledge that is in knowledge base domains, each of which is associated with a data field. This can include setting field-wide properties, creating rules, changing

  specific values, using reference data services, or setting up term-based or cross-field relationships.

  Step 3: Configure reference Data Services

  A process of domain management that enables you to validate your data against data maintained and guaranteed by a reference data provider.

  Step 4: Configure a Matching Policy

  A policy that defines how DQS processes records to identify potential duplicates and non-matches, built into the knowledge base in a computer-assisted and interactive process.

  References: https://docs.microsoft.com/en-us/sql/data-quality-services/dqs-knowledge-bases-and-domains

• Question 206:

  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
  A. Yes

  ---

  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-server-2017

• Question 207:

  You are designing a SQL Server Integration Services (SSIS) solution that will load multiple Online Transactional Processing (OLTP) data sources into a SQL Server data mart. You have the following requirements:

  A. Use a Data Profiling task. Use a Data Flow task to extract the XML output of the Data Profiling task into a SQL Server table. Query the table to view the exceptions.
  B. Use Data Flow tasks to process the clean data.
  C. Use a Data Profiling task. Read the exceptions in Data Profile Viewer.
  D. Design a stored procedure that examines data for common dirty data patterns. Use an Execute SQL task.

  C. Use a Data Profiling task. Read the exceptions in Data Profile Viewer.

• Question 208:

  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 DQS 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.

  Hot Area:

  

  

  ---

  The DQS Cleansing component takes input records, sends them to a DQS server, and gets them back corrected. The component can output not only the corrected data, but also additional columns that may be useful for you. For example the status columns. There is one status column for each mapped field, and another one that aggregated the status for the whole record. This record status column can be very useful in some scenarios, especially when records are further processed in different ways depending on their status.

  Is such cases, it is recommended to use a Conditional Split component below the DQS Cleansing component, and configure it to split the records to groups based on the record status (or based on other columns such as specific field status).

  References: https://blogs.msdn.microsoft.com/dqs/2011/07/18/using-the-ssis-dqscleansing-component/

• Question 209:

  DRAG DROP

  A Data Flow task in a SQL Server Integration Services (SSIS) package produces run-time errors.

  You need to edit the package to log specific error messages.

  Which three actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)

  Select and Place:

  

  

• Question 210:

  HOTSPOT

  You are editing a SQL Server Integration Services (SSIS) project named Project1 in SQL Server Data Tools.

  A package Connection Manager has been parameterized with project scope.

  You need to display the parameters that have been generated for the Connection Manager.

  What should you use? To answer, select the appropriate setting or settings in the answer area.

  Hot Area: