Microsoft 70-767 Online Practice Questions and Exam Preparation

Microsoft 70-767 Online Questions & Answers

• Question 61:

  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 perform the initial data loading process of the DQS knowledge base, based on the CountryRegion Entity in the MDS model.

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

  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.

  Each night you receive a comma separated values (CSV) file that contains different types of rows. Each row type has a different structure. Each row in the CSV file is unique. The first column in every row is named Type. This column identifies

  the data type.

  For each data type, you need to load data from the CSV file to a target table. A separate table must contain the number of rows loaded for each data type.

  Solution: You create a SQL Server Integration Services (SSIS) package as shown in the exhibit. (Click the Exhibit tab.)

  

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

  ---

  The conditional split must be before the count.

• Question 63:

  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.
  C. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility.

  ---

  http://msdn.microsoft.com/en-us/library/ms141212.aspx http://www.mssqltips.com/sqlservertip/2450/ssis-package-deployment-model-in-sql-server- 2016-part-1-of-2/ http://www.mssqltips.com/sqlservertip/2450/ssis-package-deployment-model-in-sql-server- 2016-part-2-of-2/

• Question 64:

  HOTSPOT

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

  The package is allowed to connect to only one database. An Environment variable contains the name of the database.

  The OLE DB project connection manager has been parameterized.

  You need to configure the connection manager property to accept the value of the Environment variable.

  Which property should you use? (To answer, configure the appropriate option or options in the dialog box in the answer area.)

  Hot Area:

  

  

• Question 65:

  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. Depending on the value in a source column, the package redirects rows to one of five different data flow paths.

  You need to add a data flow transformation to support the package redirection.

  Which data flow transformation should you use?

  A. Conditional Split
  B. Pivot
  C. Multicast
  D. Lookup
  A. Conditional Split

• Question 66:

  Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer chokes, but the text of the scenario is exactly the same in each question in the series.

  Start of repeated scenario Contoso. Ltd. has a Microsoft SQL Server environment that includes SQL Server Integration Services (SSIS), a data warehouse, and SQL Server Analysis Services (SSAS) Tabular and multi-dimensional models.

  The data warehouse stores data related to your company sales, financial transactions and financial budgets. All data for the data warehouse originates from the company s business financial system.

  The data warehouse includes the following tables:

  

  The company plans to use Microsoft Azure to store older records from the data warehouse. You must modify the database to enable the Stretch Database capability.

  Users report that they are becoming confused about which city table to use for various queries. You plan to create a new schema named Dimension and change the name of the dbo.diamension_city table to Diamension.city. Data loss is not permissible, and you must not leave traces of the old table in the data warehouse.

  The fact. Transaction table has measures named RawCost and Totaisale that calculate the wholesale cost of materials. You plan to create a measure that calculates the profit margin based on the two existing measures.

  You must implement a partitioning scheme for the fact.Transaction table to move older data to less expensive storage. Each partition will store data for a single calendar year, as shown in the exhibit (Click the Exhibit button.) You must align the partitions.

  

  You must improve performance for queries against the fact.Transaction table. You must implement appropriate indexes and enable the Stretch Database capability.

  End of repeated scenario

  You need to create the ProfitMargin measure for the fact. Transaction table.

  How should you complete the MDX statement? To answer, select the appropriate MDX segments in the answer area.

  Hot Area:

  

  

• Question 67:

  You are installing SQL Server Data Quality Services (DQS).

  You need to give users belonging to a specific Active Directory group access to the Data Quality Server.

  Which SQL Server application should you use?

  A. Data Quality Client with administrative credentials
  B. SQL Server Configuration Manager with local administrative credentials
  C. SQL Server Data Tools with local administrative permissions
  D. SQL Server Management Studio with administrative credentials
  D. SQL Server Management Studio with administrative credentials

• Question 68:

  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 a database administrator for an e-commerce company that runs an online store. The company has the databases described in the following table.

  

  Product prices are updated and are stored in a table named Products on DB1. The Products table is deleted and refreshed each night from MDS by using a Microsoft SQL Server Integration Services (SSIS) package. None of the data sources are sorted.

  You need to update the SSIS package to add current prices to the Products table.

  What should you use?

  A. Lookup transformation
  B. Merge transformation
  C. Merge Join transformation
  D. MERGE statement
  E. Union All transformation
  F. Balanced Data Distributor transformation
  G. Sequential container
  H. Foreach Loop container
  D. MERGE statement

  ---

  In the current release of SQL Server Integration Services, the SQL statement in an Execute SQL task can contain a MERGE statement. This MERGE statement enables you to accomplish multiple INSERT, UPDATE, and DELETE operations in a single statement.

  References: https://docs.microsoft.com/en-us/sql/integration-services/control-flow/merge- in-integration-services-packages

• Question 69:

  You are developing a SQL Server Integration Services (SSIS) project that contains a project Connection Manager and multiple packages.

  All packages in the project must connect to the same database. The server name for the database must be set by using a parameter named ServerParam when any package in the project is executed.

  You need to develop this project with the least amount of development effort.

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

  A. Set the Sensitive property of the parameter to True.
  B. Edit each package Connection Manager. Set the ServerName property to @[$Project::ServerParam].
  C. Edit the project Connection Manager in Solution Explorer. Set the ServerName property to @[$Project::ServerParam].
  D. Create a project parameter named ServerName.
  E. Create a package parameter named ServerName in each package.
  F. Set the Required property of the parameter to True.
  C. Edit the project Connection Manager in Solution Explorer. Set the ServerName property to @[$Project::ServerParam].
  D. Create a project parameter named ServerName.
  F. Set the Required property of the parameter to True.

  ---

  Explanation: C: From question: " The server name for the database must be set by using a parameter named ServerParam when any package in the project is executed."

  D: SSIS 2016 has introduced the concept of Project level connection managers. An SSIS project is generally more than one package. To simplify lives, the SSIS team now allows for the sharing of common resources across projects, connection managers being one of those resources.

  F: When a parameter is marked as required, a server value or execution value must be specified for that parameter. Otherwise, the corresponding package does not execute. Although the parameter has a default value at design time, it will never be used once the project is deployed.

  Note:

  * Integration Services (SSIS) parameters allow you to assign values to properties within packages at the time of package execution. You can create project parameters at the project level and package parameters at the package level. Project parameters are used to supply any external input the project receives to one or more packages in the project. Package parameters allow you to modify package execution without having to edit and redeploy the package.

  Reference: Integration Services (SSIS) Parameters

• Question 70:

  You are preparing to install SQL Server 2016 Master Data Services (MDS).

  You need to ensure that the database requirements are met.

  What should you install?

  A. Microsoft SharePoint Server 2010 Standard Edition SP1
  B. Microsoft SharePoint Server 2010 Enterprise Edition SP1
  C. SQL Server 2016 Data Center (64-bit) x64 on the database server
  D. SQL Server 2016 Enterprise (64-bit) x64 on the database server
  D. SQL Server 2016 Enterprise (64-bit) x64 on the database server

  ---

  *

  Master Data Services is a new feature introduced in SQL Server 2008 R2 and further enhanced in SQL Server 2016.

  *

  SQL Server 2016 Enterprise features include Master Data Services:

  Note:

  *

  Microsoft SQL Server Master Data Services is a Master Data Management (MDM) product from Microsoft, which will ship as a part of the Microsoft SQL Server database.Originally code-named Bulldog, Master Data Services is the rebranding of the Stratature MDM product titled +EDM, which Microsoft acquired in June 2007. Master Data Services is architecturally similar to +EDM, with increased integration with other Microsoft applications as well as some new features. Master Data Services first shipped with Microsoft SQL Server 2008 R2.