Microsoft 70-767 Online Practice Questions and Exam Preparation

Microsoft 70-767 Online Questions & Answers

• Question 101:

  You are creating a Data Quality Services (DQS) solution. You must provide statistics on the accuracy of the data.

  You need to use DQS profiling to obtain the required statistics.

  Which DQS activity should you use?

  A. Cleansing
  B. Knowledge Discovery
  C. SQL Profiler
  D. Matching Rule Definition
  A. Cleansing

• Question 102:

  You manage a SQL Server Master Data Services (MDS) environment.

  A new application requires access to the product data that is available in the MDS repository.

  You need to design a solution that gives the application access to the product data with the least amount of development effort.

  What should you do?

  A. Use sp_addlinkedserver to add a linked server to access the MDS database tables directly.
  B. Create an OLE DB connection string that sets the Provider property to MDS.
  C. Use transactional replication for data synchronization.
  D. Create a Subscription View in MDS.
  D. Create a Subscription View in MDS.

• Question 103:

  You are developing a data warehouse. You run the following Transact-SQL statement:

  

  Use the drop-down menus to select the answer choice that answers each question based on the information presented in the graphic. NOTE: Each correct selection is worth one point.

  Hot Area:

  

  

• Question 104:

  You are installing the Data Quality Server component of Data Quality Services.

  You need to provision the hardware and install the software for the server that runs the Data Quality Server.

  You must ensure that the minimum Data Quality Server prerequisites are met.

  What should you do?

  A. Install Microsoft Internet Explorer 6.0 SP1 or later.
  B. Install SQL Server 2016 Database Engine.
  C. Make sure the server has at least 4 GB of RAM.
  D. Install Microsoft SharePoint Server 2010 Standard Edition.
  B. Install SQL Server 2016 Database Engine.

  ---

  Explanation: Data Quality Server Minimum System Requirements

  *

  SQL Server 2016 Database Engine.

  *

  Memory (RAM): Minimum: 2 GB Recommended: 4 GB or more

  Note: SQL Server Data Quality Services (DQS) is a new feature in SQL Server 2016 that contains the following two components: Data Quality Server and Data Quality Client.

• Question 105:

  You have a data warehouse that contains a fact table named Table1 and a Product table named Dim1. Dim1 is configured as shown in the following table.

  

  You are adding a second OLTP system to the data warehouse as a new fact table named Table2. The Product table of the OLTP system is configured as shown in the following table

  

  You need to modify Dim1 to ensure that the table can be used for both fact tables.

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

  NOTE: Each correct selection is worth one point.

  A. Modify the data type of the Weight column in Dim1 to decimal (19, 2).
  B. Add the SalesUnit column to Dim1.
  C. Modify the data type of the Name column in Dim1 to varchar (85).
  D. Drop the ProductKey column from Dim1 and replace the column with the ProductIdentifier column.
  E. Drop the Color column from Dim1.
  F. Modify the data type of the ProductKey column in Dim1 to char (18).
  A. Modify the data type of the Weight column in Dim1 to decimal (19, 2).
  D. Drop the ProductKey column from Dim1 and replace the column with the ProductIdentifier column.

• Question 106:

  You manage a Data Quality Services (DQS) knowledge base.

  You need to ensure that DQS will create a synonym relationship between identical tags.

  What should you create?

  A. new domain
  B. linked domain
  C. term-based relations
  D. new knowledge base
  C. term-based relations

• Question 107:

  You are the administrator of a Microsoft SQL Server Master Data Services (MDS) model. The model was developed to provide consistent and validated snapshots of master data to the ETL processes by using subscription views. A new model version has been created.

  You need to ensure that the ETL processes retrieve the latest snapshot of master data.

  What should you do?

  A. Add a version flag to the last committed version, and create new subscription views that use this version flag.
  B. Update the subscription views to use the last committed version.
  C. Add a version flag to the new version, and update the subscription views to use this version flag.
  D. Add a version flag to the new version, and create new subscription views that use this version flag.
  D. Add a version flag to the new version, and create new subscription views that use this version flag.

• Question 108:

  You are maintaining a Data Quality Services (DQS) environment. The production server failed and a new server has been set up. The DQS databases are restored to a new server.

  All the appropriate permissions are granted.

  DQS users are experiencing issues connecting to the new Data Quality Server.

  You need to enable users to connect to the new server. Which Surface Area Configuration property should you enable?

  A. AdHocRemoteQueriesEnabled
  B. SoapEndpointsEnabled
  C. ClrIntegrationEnabled
  D. RemoteDacEnabled
  E. OleAutomationEnabled
  F. XpCmdShellEnabled
  C. ClrIntegrationEnabled

• Question 109:

  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 choices, 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.dia_city table to Dimension.city. Data loss is not

  permissible, and you must not leave traces of the old table in the data warehouse.

  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 configure the fact. Transaction table.

  Which three Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

  Select and Place:

  

  

• Question 110:

  DRAG DROP

  You are designing an extract, transform, load (ETL) process with SQL Server Integration Services (SSIS). Two packages, Package A and Package B, will be designed. Package A will execute Package B.

  Both packages must reference a file path corresponding to an input folder where files will be located for further processing.

  You need to design a solution so that the file path can be easily configured with the least administrative and development effort.

  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: