Microsoft 70-767 Online Practice Questions and Exam Preparation

Microsoft 70-767 Online Questions & Answers

• Question 121:

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

  You need to configure the City entity to meet User1 and User2 access requirements.

  What should you do?

  A. Set the Approval Required property to True for the City entity.
  B. Set the transaction Log Type Property to member for the City entity.
  C. Publish a business rule for the City entity which starts a workflow.
  D. Set the value of the Enable Change tracking property to true for the Code attribute of the City entity.
  C. Publish a business rule for the City entity which starts a workflow.

• Question 122:

  DRAG DROP

  A SQL Server Integration Services (SSIS) project has been deployed to the SSIS catalog. The project includes a project Connection Manager to connect to the data warehouse. The SSIS catalog includes two Environments:

  Test

  Production

  Each Environment defines a single Environment Variable named ConnectionString of type string. The value of each variable consists of the connection string to the test or production data warehouses.

  You need to execute deployed packages by using either of the defined Environments.

  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 123:

  You are building a server to host a data warehouse.

  The planned disk activity for-khe data warehouse is five percent write activity and 95 percent read activity.

  You need to recommend a storage solution for the data files of the data warehouse. The solution must meet the following requirements:

  *Ensure that the data warehouse is available if two disks fail.

  *Minimize hardware costs.

  Which RAID configuration should you recommend?

  A. RAID1
  B. RAID 5
  C. RAID 6
  D. RAID 10
  C. RAID 6

  ---

  According to the Storage Networking Industry Association (SNIA), the definition of RAID 6 is: "Any form of RAID that can continue to execute read and write requests to all of a RAID array's virtual disks in the presence of any two concurrent disk failures."

• Question 124:

  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 a Microsoft Azure SQL Data Warehouse instance. You run the following Transact-SQL statement:

  

  The query fails to return results.

  You need to determine why the query fails.

  Solution: You run the following Transact-SQL statements:

  

  Does the solution meet the goal?

  A. Yes
  B. No
  A. Yes

  ---

  To use submit_time we must use sys.dm_pdw_exec_requests table, which holds information about all requests currently or recently active in SQL Data Warehouse. It lists one row per request/query. Label is an Optional label string associated with some SELECT query statements. https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-pdw-exec-requests-transact-sql?view=aps-pdw-2016-au7

• Question 125:

  You are developing a Microsoft SQL Server Data Warehouse. You use SQL Server Integration Services (SSIS) packages to import files from a Microsoft Azure blob storage to the data warehouse.

  You plan to use multiple SQL Server instances and SSIS Scale Out to complete the workload faster.

  Which two actions should you perform? Each correct answer presents part of the solution.

  NOTE: Each correct solution is worth one point.

  A. Deploy the SSIS project to the SSIS catalog on all three SQL Servers in the SSIS Scale Out environment.
  B. Install the SSIS Scale Out Worker feature on two servers. Install the Scale Out Master role feature on one server.
  C. Deploy the SSIS project to the SSIS catalog on the SQL Server which has the Scale Out Master role installed.
  D. Install the SSIS Scale Out Worker feature on all three servers. Install the Scale Out Master role on one server.
  B. Install the SSIS Scale Out Worker feature on two servers. Install the Scale Out Master role feature on one server.

  ---

  References: https://docs.microsoft.com/en-us/sql/integration-services/scale-out/walkthrough-set-up-integration-services-scale-out?view=sql-server-ver15

• Question 126:

  You have a Microsoft SQL Server Integration Services (SSIS) package that includes a custom Script task. You set a breakpoint at line 97 as shown in the exhibit. (Click the Exhibit tab.) C# Methods

  

  You run the code and the debugger pauses at line 97. You need to debug the ConvertToString method. What should you do?

  A. Use the Step Over command.
  B. Use the Toggle Breakpoint command.
  C. Use the Step Out command.
  D. Create a new Function breakpoint.
  D. Create a new Function breakpoint.

  ---

  References: https://docs.microsoft.com/en-us/visualstudio/debugger/navigating-through-code-with-the-debugger

• Question 127:

  You have the Microsoft SQL Server Integration Services (SSIS) package shown in the Control flow exhibit. (Click the Exhibit button.)

  

  The package iterates over 100 files in a local folder. For each iteration, the package increments a variable named loop as shown in the Expression task exhibit. (Click the Exhibit button) and then imports a file. The initial value of the variable loop is 0.

  

  You suspect that there may be an issue with the variable value during the loop. You define a breakpoint on the Expression task as shown in the BreakPoint exhibit. (Click the Exhibit button.)

  

  You need to check the value of the loop variable value.

  For each of the following statements, select Yes if the statement is true. Otherwise, select No.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  

  ---

  Break condition: When the task or container receives the OnPreExecute event.

  Called when a task is about to execute. This event is raised by a task or a container immediately before it runs.

  The loop variable does not reset.

  With the debugger, you can break, or suspend, execution of your program to examine your code, evaluate and edit variables in your program, etc.

• Question 128:

  You are designing a fact table in a SQL Server database.

  The fact table must meet the following requirements:

  A. Design a fact table with 5 dimensional key columns and 10 measure columns. Place the columnstore index on the dimensional key columns.
  B. Design a fact table with 5 dimensional key columns and 10 measure columns. Place the columnstore index on the measure columns.
  C. Design a fact table with 10 dimensional key columns and 5 measure columns. Place the columnstore index on the dimensional key columns and the measure columns.
  D. Design a fact table with 10 dimensional key columns and 5 measure columns. Place the columnstore index on only the measure columns.

  C. Design a fact table with 10 dimensional key columns and 5 measure columns. Place the columnstore index on the dimensional key columns and the measure columns.

• Question 129:

  You are developing a SQL Server Integration Services (SSIS) package to implement an incremental data load strategy. The package reads data from a source system that uses the SQL Server change data capture (CDC) feature.

  You have added a CDC Source component to the data flow to read changed data from the source system.

  You need to add a data flow transformation to redirect rows for separate processing of insert, update, and delete operations.

  Which data flow transformation should you use?

  A. DQS Cleansing
  B. Merge Join
  C. Pivot
  D. Conditional Split
  D. Conditional Split

• Question 130:

  DRAG DROP

  A SQL Server Integration Services (SSIS) package named DataFeed interacts with an external vendor data feed. The package is executed several times a day, either as part of other packages' control flow or by itself. The external data feed is

  unreliable because network failures and slow response times are frequent. The package is currently deployed on the file system.

  To analyze the reliability of the external data feed, you must collect execution data.

  Every time the DataFeed package is executed, the following information must be logged:

  Start Time

  End Time

  Execution Result

  Execution Duration

  You need to design a logging solution that meets the requirements by using the least amount of administrative and development effort.

  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: