Top Microsoft 70-767 software Choices

Testking 70-767 Questions are updated and all 70-767 answers are verified by experts. Once you have completely prepared with our 70-767 exam prep kits you will be ready for the real 70-767 exam without a problem. We have Improved Microsoft 70-767 dumps study guide. PASSED 70-767 First attempt! Here What I Did.

♥♥ 2021 NEW RECOMMEND ♥♥

Free VCE & PDF File for Microsoft 70-767 Real Exam (Full Version!)

★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions

Free Instant Download NEW 70-767 Exam Dumps (PDF & VCE):
Available on: http://www.surepassexam.com/70-767-exam-dumps.html

Q11. You are reviewing the design of a student dimension table in an existing data warehouse hosted on SQL Azure.

The current dimension design does not allow the retention of historical changes to student attributes such as ParentOccupation.

You need to redesign the dimension to enable the full historical reporting of changes to multiple student attributes including ParentOccupation.

What should you do?

A. Add CurrentValue and PreviousValue columns to the student dimension.

B. Enable Snapshot Isolation on the data warehouse.

C. Add an IsCurrent column to the student dimension.

D. Add StartDate and EndDate columns to the student dimension.

Answer: D

Explanation: Adding a start and end date will give you this ability as when a record is inserted and given a start and end date, you’ll have the ability to determine when they were active therefore giving you a retention of historical changes

Q12. DRAG DROP

You are building a fact table in a data warehouse.

The table must have a columnstore index. The table cannot be partitioned. You need to design the fact table and load it with data.

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

Answer:

Q13. You are designing an enterprise star schema that will consolidate data from three independent data marts. One of the data marts is hosted on SQL Azure.

Most of the dimensions have the same structure and content. However, the geography dimension is slightly different in each data mart.

You need to design a consolidated dimensional structure that will be easy to maintain while ensuring that all dimensional data from the three original solutions is represented.

What should you do?

A. Create a conformed dimension for the geography dimension.

B. Implement change tracking.

C. Create a degenerate dimension for the geography dimension.

D. Create a Type 2 slowly changing dimension for the geography dimension.

Answer: A

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

The package uses a Foreach container to process text files found in a folder. The package must be deployed to a single server by using the Project Deployment model.

Multiple SQL Server Agent jobs call the package. Each job is executed on a different schedule. Each job passes a different folder path to the package.

You need to configure the package to accept the folder path from each job. Which package configuration should you use?

A. .dtsConfig file

B. Registry Entry

C. Environment Variable

D. Parent Package Variable

E. XML Configuration File

Answer: C

Explanation: 

Reference:

http://msdn.microsoft.com/en-us/library/ms345179.aspx http://msdn.microsoft.com/en-us/library/ms141708.aspx http://msdn.microsoft.com/en-us/library/hh213214.aspx http://msdn.microsoft.com/en-us/library/hh213296.aspx http://msdn.microsoft.com/en-us/library/hh213293.aspx

Q15. DRAG DROP

You are developing a SQL Server Integration Services (SSIS) project by using the Project Deployment model.

The project contains many packages. It is deployed on a server named SQLTest1. The project will be deployed to several servers that run SQL Server 2021.

The project accepts one required parameter. The data type of the parameter is a string.

A SQL Agent job is created that will call the Loading.dtsx package in the project. A job step is created for the SSIS package.

The job must pass the value of an SSIS Environment Variable to the project parameter. The value of the Environment Variable must be configured differently on each server that runs SQL Server. The value of the Environment Variable must provide the server name to the project parameter.

You need to configure SSIS on the SQLTest1 server to pass the Environment Variable to the package.

Which four actions should you perform in sequence by using SQL Server Management Studio?

(To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)

Answer:

Q16. You are a database developer of a Microsoft SQL Server 2021 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));

Answer: D

Q17. CORRECT TEXT

You are designing a package control flow. The package moves sales order data from a SQL Azure transactional database to an on-premise reporting database. The package will run several times a day, while new sales orders are being added to the transactional database. The current design of the package control flow is shown in the answer area. (Click the Exhibit button.)

The Insert New Orders Data Flow task must meet the following requirements:

•Usage of the tempdb database should not be impacted.

•Concurrency should be maximized, while only reading committed transactions.

•If the task fails, only that task needs to be rolled back.

You need to configure the Insert New Orders Data Flow task to meet the requirements. How should you configure the transaction properties? (To answer, select the appropriate

setting or settings in the answer area.)

Answer: 

IsolationLevel = ReadCommited. TransactionOption = Required References:

http://msdn.microsoft.com/en-us/library/ms137690.aspx http://msdn.microsoft.com/en-us/library/ms137749.aspx http://msdn.microsoft.com/en- us/library/microsoft.sqlserver.dts.runtime.dtscontainer.isolationlevel.aspx http://msdn.microsoft.com/en-us/library/ms173763.aspx

Q18. 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. Create a Subscription View in MDS.

B. Access the product entity tables in the MDS database directly.

C. Use SQL Server Integration Services (SSIS) to extract the data and put it in a staging database.

D. Use change data capture on the product entity tables.

Answer: A

Q19. DRAG DROP

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

You have the following requirements:

•A destination table must contain all of the data in two source tables.

•Duplicate records must be inserted into the destination table.

You need to develop a data flow that imports the data while meeting the requirements. How should you develop the data flow? (To answer, drag the appropriate transformation

from the list of transformations to the correct location in the answer area.)

Answer:

Explanation:

References:

http://msdn.microsoft.com/en-us/library/ms141703.aspx http://msdn.microsoft.com/en-us/library/ms141775.aspx http://msdn.microsoft.com/en-us/library/ms141020.aspx http://msdn.microsoft.com/en-us/library/ms140182.aspx

Q20. You are designing a SQL Server Integration Services (SSIS) data flow to load sales transactions from a source system into a data warehouse hosted on SQL Azure. One of the columns in the data source is named ProductCode.

Some of the data to be loaded will reference products that need special processing logic in the data flow.

You need to enable separate processing streams for a subset of rows based on the source product code.

Which data flow transformation should you use?

A. Audit

B. Source Assistant

C. Script Task

D. Conditional Split

Answer: D

Explanation:

http://msdn.microsoft.com/en-us/library/ms137640.aspx http://msdn.microsoft.com/en-us/library/ms141150.aspx http://msdn.microsoft.com/en-us/library/ff929138.aspx http://msdn.microsoft.com/en-us/library/ff929116.aspx