Lead2pass 70-767 Exam Questions Free Download:

https://www.lead2pass.com/70-767.html

QUESTION 1
You are reviewing the design of a customer dimension table in an existing data warehouse hosted on SQL Azure.
The current dimension design does not allow the retention of historical changes to customer attributes such as Postcode.
You need to redesign the dimension to enable the full historical reporting of changes to multiple customer attributes including Postcode.
What should you do?

A.    Add StartDate and EndDate columns to the customer dimension.
B.     Add an IsCurrent column to the customer dimension.
C.    Enable Snapshot Isolation on the data warehouse.
D.    Add CurrentValue and PreviousValue columns to the customer dimension.

Answer: A

QUESTION 2
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:

– [ID]
– [QuoteDate]
– [Open]
– [Close]
– [High]
– [Low]
– [Volume]

Each of the following queries must be able to use a columnstore index:

SELECT AVG ([Close]) AS [AverageClose] FROM Quotes WHERE [QuoteDate] BETWEEN ‘20100101’ AND ‘20101231’.
SELECT AVG([High] – [Low]) AS [AverageRange] FROM Quotes WHERE [QuoteDate] BETWEEN ‘20100101’ AND
‘20101231’.
SELECT SUM([Volume]) AS [SumVolume] FROM Quotes WHERE [QuoteDate] BETWEEN ‘20100101’ AND ‘20101231’.

You need to ensure that the indexing strategy meets the requirements.
The strategy must also minimize the number and size of the indexes.
What should you do?

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]

Answer: C
Explanation:
http://msdn.microsoft.com/en-us/library/gg492088.aspx
http://msdn.microsoft.com/en-us/library/gg492153.aspx

QUESTION 3
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 junk dimension for the geography dimension.
B.    Implement change data capture.
C.    Create a conformed dimension for the geography dimension.
D.    Create three geography dimensions.

Answer: C

QUESTION 4
To facilitate the troubleshooting of SQL Server Integration Services (SSIS) packages, a logging methodology is put in place.
The methodology has the following requirements:

– The deployment process must be simplified.
– All the logs must be centralized in SQL Server.
– Log data must be available via reports or T-SQL.
– Log archival must be automated.

You need to configure a logging methodology that meets the requirements while minimizing the amount of deployment and development effort.
What should you do?

A.    Open a command prompt and run the gacutil command.
B.    Open a command prompt and execute the package by using the SQL Log provider and
running the dtexecui.exe utility.
C.    Add an OnError event handler to the SSIS project.
D.    Use an msi file to deploy the package on the server.
E.    Configure the output of a component in the package data flow to use a data tap.
F.    Run the dtutil command to deploy the package to the SSIS catalog and store the
configuration in SQL Server.
G.    Open a command prompt and run the dtexec/rep/conn command.
H.    Open a command prompt and run the dtutil/copy command.
I.    Open a command prompt and run the dtexec/dumperror/conn command.
J.    Configure the SSIS solution to use the Project Deployment Model.
K.    Create a reusable custom logging component and use it in the SSIS project.

Answer: J
Explanation:
http://msdn.microsoft.com/en-us/library/ms140246.aspx
http://msdn.microsoft.com/en-us/library/hh231187.aspx

QUESTION 5
You are developing a SQL Server Integration Services (SSIS) project that copies a large amount of rows from a SQL Azure database.
The project uses the Package Deployment Model.
This project is deployed to SQL Server on a test server.
You need to ensure that the project is deployed to the SSIS catalog on the production server.
What should you do?

A.    Open a command prompt and run the dtexec/dumperror/conn command.
B.    Create a reusable custom logging component and use it in the SSIS project.
C.    Open a command prompt and run the gacutil command.
D.    Add an OnError event handler to the SSIS project.
E.    Open a command prompt and execute the package by using the SQL Log provider and
running the dtexecui.exe utility.
F.    Open a command prompt and run the dtexec/rep/conn command.
G.    Open a command prompt and run the dtutil/copy command.
H.    Use an msi file to deploy the package on the server.
I.    Configure the SSIS solution to use the Project Deployment Model.
J.    Configure the output of a component in the package data flow to use a data tap.
K.    Run the dtutil command to deploy the package to the SSIS catalog and store the
configuration in SQL Server.

Answer: I
Explanation:
http://msdn.microsoft.com/en-us/library/hh231102.aspx
http://msdn.microsoft.com/en-us/library/hh213290.aspx
http://msdn.microsoft.com/en-us/library/hh213373.aspx

QUESTION 6
You are developing a SQL Server Integration Services (SSIS) package.
To process complex scientific data originating from a SQL Azure database, a custom task component is added to the project.
You need to ensure that the custom component is deployed on a test environment correctly.
What should you do?

A.    Add an OnError event handler to the SSIS project.
B.    Open a command prompt and run the gacutil command.
C.    Configure the SSIS solution to use the Project Deployment Model.
D.    Open a command prompt and run the dtexec/dumperror/conn command.
E.    Configure the output of a component in the package data flow to use a data tap.
F.    Open a command prompt and execute the package by using the SQL Log provider and
running the dtexecui.exe utility.
G.    Open a command prompt and run the dtexec/rep/conn command.
H.    Run the dtutil command to deploy the package to the SSIS catalog and store the
configuration in SQL Server.
I.    Use an msi file to deploy the package on the server.
J.    Open a command prompt and run the dtutil/copy command.
K.    Create a reusable custom logging component and use it in the SSIS project.

Answer: B
Explanation:
http://msdn.microsoft.com/en-us/library/ms403356.aspx

QUESTION 7
Drag and Drop Question
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:

– Development
– QA

Each Environment defines a single Environment Variable named ConnectionString of type string. The value of each variable consists of the connection string to the development or QA data warehouses.
You need to be able 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.)

Answer:

QUESTION 8
You develop and deploy a SQL Server Integration Services (SSIS) package.
The package is stored in the file system.
You need to execute the package without importing it to the SSIS server.
What should you use to execute the package? (Each correct answer presents a complete solution. Choose all that apply.)

A.    catalog.start_package
B.    dtexec
C.    SQL Server Management Studio
D.    SQL Server Agent

Answer: BD
Explanation:
http://technet.microsoft.com/en-us/library/ms141708(v=sql.110).aspx

70-767 dumps full version (PDF&VCE): https://www.lead2pass.com/70-767.html

Large amount of free 70-767 exam questions on Google Drive: https://drive.google.com/open?id=0B3Syig5i8gpDdTF0R0taLWgxSmc

You may also need:

70-764 exam dumps: https://drive.google.com/open?id=0B3Syig5i8gpDUjBoM0pVQnlUTlU