Microsoft 70-767 Online Practice Questions and Exam Preparation

Microsoft 70-767 Online Questions & Answers

• Question 11:

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

  You add an Execute SQL task to the control flow. The task must execute a simple INSERT statement.

  The task has the following requirements:

  A. INSERT INTO dbo.Table (variablevalue) VALUES (@StringVar)
  B. INSERT INTO dbo.Table (variablevalue) VALUES ($Project::StringVar)
  C. INSERT INTO dbo.Table (variablevalue) VALUES (?)
  D. INSERT INTO dbo.Table (variablevalue) VALUES ($Package::StringVar)

  C. INSERT INTO dbo.Table (variablevalue) VALUES (?)

• Question 12:

  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 new version, and create new subscription views that use this version flag.
  B. Create new subscription views for the new version.
  C. Update the subscription views to use the new version.
  D. Update the subscription views to use the last committed version.
  A. Add a version flag to the new version, and create new subscription views that use this version flag.

  ---

  When a version is ready for users or for a subscribing system, you can set a flag to identify the version. You can move this flag from version to version as needed. Flags help users and subscribing systems identify which version of a model to use.

  References: https://docs.microsoft.com/en-us/sql/master-data-services/versions-master-data-services

• Question 13:

  You are implementing the indexing strategy for a fact table in a data warehouse. The fact table is named Quotes. The table has no indexes and consists of seven columns:

  A. Create one columnstore index that contains [ID], [Close], [High], [Low], [Volume], and [QuoteDate].
  B. Create three coiumnstore indexes: One containing [QuoteDate] and [Close] One containing [QuoteDate], [High], and [Low] One containing [QuoteDate] and [Volume]
  C. Create one columnstore index that contains [QuoteDate], [Close], [High], [Low], and [Volume].
  D. Create two columnstore indexes: One containing [ID], [QuoteDate], [Volume], and [Close] One containing [ID], [QuoteDate], [High], and [Low]

  C. Create one columnstore index that contains [QuoteDate], [Close], [High], [Low], and [Volume].

  ---

  Reference: http://msdn.microsoft.com/en-us/library/gg492088.aspx Reference: http://msdn.microsoft.com/en-us/library/gg492153.aspx

• Question 14:

  You are a database developer of a Microsoft SQL Server 2016 database. You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID. A sample of this data is as shown in the following table. You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to ensure that the data in the table is in the order of SourceID and then CustomerID.

  Which Transact- SQL statement should you use?

  A. CREATE TABLE Customer (SourceID int NOT NULL IDENTITY, CustomerID int NOT NULL IDENTITY, CustomerName varchar(255) NOT NULL);
  B. CREATE TABLE Customer (SourceID int NOT NULL, CustomerID int NOT NULL PRIMARY KEY CLUSTERED, CustomerName varchar(255) NOT NULL);
  C. CREATE TABLE Customer (SourceID int NOT NULL PRIMARY KEY CLUSTERED, CustomerID int NOT NULL UNIQUE, CustomerName varchar(255) NOT NULL);
  D. CREATE TABLE Customer (SourceID int NOT NULL, CustomerID int NOT NULL, CustomerName varchar(255) NOT NULL, CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED (SourceID, CustomerID));
  D. CREATE TABLE Customer (SourceID int NOT NULL, CustomerID int NOT NULL, CustomerName varchar(255) NOT NULL, CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED (SourceID, CustomerID));

• Question 15:

  You plan to deploy several Microsoft SQL Server Integration Services (SSIS) packages to a highly available SQL Server instance. The instance is configured to use an AlwaysOn availability group that has two replicas.

  You need to identify which deployment method must be used to ensure that the packages are always accessible from all the nodes in the availability group.

  Which deployment method should you use for the packages?

  A. Deploy to the msdb database on the secondary replica.
  B. Deploy to the msdb database on the primary replica.
  C. Deploy to a file on the hard drive of the primary replica.
  D. Deploy to a shared folder on a file server.
  A. Deploy to the msdb database on the secondary replica.

  ---

  Before you can configure SSIS to enable support of AlwaysOn on the new added secondary Replicas, you must connect to all new added secondary replicas.

  

  Note: To use SSIS with AlwaysOn, you’ll need to add the SSIS Catalog (SSISDB) into an Availability Group. You’ll need to do the following steps:

  

  Make sure you meet the prerequisites for using AlwaysOn

  

  Connect to every node and create the SSISDB catalog. We need to create the catalog even on secondary nodes to create the other server-level objects (cleanup jobs, keys, accounts etc) that are used by SSIS.

  

  Delete the SSISDB databases on secondary nodes.

  

  Create an availability group, specifying SSISDB as the user database

  

  Specify secondary replicas.

  References: https://chrislumnah.com/2017/05/09/enabling-alwayson-for-ssisdb/

• Question 16:

  DRAG DROP

  You are validating whether a SQL Server Integration Services (SSIS) package named Master.dtsx in the SSIS catalog is executing correctly.

  You need to display the number of rows in each buffer passed between each data flow component of the package.

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

  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. The package design consists of the sources shown in the following diagram:

  

  Each source contains data that is not sorted.

  You need to combine data from all of the sources into a single dataset.

  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
  C. Union All

• Question 18:

  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 sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

  You are developing a Microsoft SQL Server Integration Services (SSIS) projects. The project consists of several packages that load data warehouse tables.

  You need to extend the control flow design for each package to use the following control flow while minimizing development efforts and maintenance:

  

  Solution: You add the control flow to a script task. You add an instance of the script task to the storage account in Microsoft Azure. Does the solution meet the goal?

  A. Yes
  B. No
  B. No

  ---

  A package consists of a control flow and, optionally, one or more data flows. You create the control flow in a package by using the Control Flow tab in SSIS Designer. References: https://docs.microsoft.com/en-us/sql/integration-services/ control-flow/control- flow

• Question 19:

  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. Matching
  C. Knowledge Discovery
  D. Matching Policy
  A. Cleansing

  ---

  Ref: http://msdn.microsoft.com/en-gb/library/hh213055(v=sql.110).aspx

• Question 20:

  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 sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

  You have the following line-of-business solutions:

  ERP system

  Online WebStore

  Partner extranet

  One or more Microsoft SQL Server instances support each solution. Each solution has its own product catalog. You have an additional server that hosts SQL Server Integration Services (SSIS) and a data warehouse. You populate the data

  warehouse with data from each of the line-of-business solutions. The data warehouse does not store primary key values from the individual source tables.

  The database for each solution has a table named Products that stored product information. The Products table in each database uses a separate and unique key for product records. Each table shares a column named ReferenceNr between

  the databases. This column is used to create queries that involve more than once solution.

  You need to load data from the individual solutions into the data warehouse nightly. The following requirements must be met:

  If a change is made to the ReferenceNr column in any of the sources, set the value of IsDisabled to True and create a new row in the Products table. If a row is deleted in any of the sources, set the value of IsDisabled to True in the data

  warehouse.

  Solution: Perform the following actions:

  Enable the Change Tracking feature for the Products table in the three source databases.

  Query the CHANGETABLE function from the sources for the deleted rows. Set the IsDIsabled column to True on the data warehouse Products table for the listed rows. Does the solution meet the goal?

  A. Yes
  B. No
  B. No

  ---

  We must check for updated rows, not just deleted rows.

  References: https://www.timmitchell.net/post/2016/01/18/getting-started-with-change- tracking-in-sql-server/