70-767 Exam
All About 70-767 simulations Dec 2021
Exambible 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 Most recent Microsoft 70-767 dumps study guide. PASSED 70-767 First attempt! Here What I Did.
Q21. You are editing a SQL Server Integration Services (SSIS) package that contains three Execute SQL tasks and no other tasks. The three Execute SQL tasks modify products in staging tables in preparation for a data warehouse load.
The package and all three Execute SQL product tasks have their TransactionOption property set to Supported.
You need to ensure that if any of the three Execute SQL product tasks fail, all three tasks will roll back their changes.
What should you do?
A. Change the TransactionOption property of the package to Required.
B. Change the TransactionOption property of all three Execute SQL product tasks to Required.
C. Move the three Execute SQL product tasks into a Foreach Loop container.
D. Move the three Execute SQL product tasks into a Sequence container.
Answer: A
Explanation:
References:
http://msdn.microsoft.com/en-us/library/ms137690.aspx http://msdn.microsoft.com/en-us/library/ms141144.aspx
Q22. You are designing a data warehouse with two fact tables. The first table contains sales per month and the second table contains orders per day.
Referential integrity must be enforced declaratively.
You need to design a solution that can join a single time dimension to both fact tables.
What should you do?
A. Join the two fact tables.
B. Merge the fact tables.
C. Create a time dimension that can join to both fact tables at their respective granularity.
D. Create a surrogate key for the time dimension.
Answer: C
Explanation: References: http://msdn.microsoft.com/en-us/library/ms174537.aspx
http://technet.microsoft.com/en-us/library/ms174832.aspx http://msdn.microsoft.com/en-us/library/ms174884.aspx http://decipherinfosys.wordpress.com/2007/02/01/surrogate-keys-vs-natural-keys-for- primary-key/
http://www.agiledata.org/essays/keys.html http://www.databasejournal.com/features/mssql/article.php/3922066/SQL-Server-Natural-
Key-Verses-Surrogate-Key.htm http://www.jamesserra.com/archive/2021/01/surrogate-keys/
Q23. You are creating a SQL Server Integration Services (SSIS) package to retrieve product data from two different sources. One source is hosted in a SQL Azure database. Each source contains products for different distributors.
Products for each distributor source must be combined for insertion into a single product table destination.
You need to select the appropriate data flow transformation to meet this requirement. Which transformation types should you use? (Each correct answer presents a complete
solution. Choose all that apply.)
A. Multicast
B. Merge Join
C. Term Extraction
D. union All
E. Merge
Answer: D,E
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ms141703.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms141775.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms141020.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms141809.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms137701.aspx
Q24. 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:
Q25. You are reviewing the design of an existing fact table named factSales, which is loaded incrementally from a SQL Azure database by a SQL Server Integration Services (SSIS) package each hour. The fact table has approximately 4 billion rows and is dimensioned by product, sales date, and sales time of day.
The database administrator is concerned about the rapid growth of the database and users experience poor reporting performance against this database. Reporting requirements have recently changed and the only remaining report that uses this fact table reports sales by product name, sale month, and sale year. No other reports will be created against this table.
You need to reduce the report processing time and minimize the growth of the database. What should you do?
A. Create an indexed view over the fact table to sum orderTotal by month.
B. Create a view over the fact table to sum orderTotal by month.
C. Change the granularity of the fact table to month.
D. Partition the fact table by productKey.
Answer: C
Q26. You are reviewing the design of an existing fact table named factSales, which is loaded from a SQL Azure database by a SQL Server Integration Services (SSIS) package each day. The fact table has approximately 1 billion rows and is dimensioned by product, sales date, and sales time of day.
The database administrator is concerned about the growth of the database. Users report poor reporting performance against this database. Reporting requirements have recently changed and the only remaining report that uses this fact table reports sales by product name, sale month, and sale year. No other reports will be created against this table.
You need to reduce the report processing time and minimize the growth of the database. What should you do?
A. Partition the table by product type.
B. Create a view over the fact table to aggregate sales by month.
C. Change the granularity of the fact table to month.
D. Create an indexed view over the fact table to aggregate sales by month.
Answer: C
Q27. DRAG DROP
You are designing a SQL Server Integration Services (SSIS) package to execute 12 Transact-SQL (T-SQL) statements on a SQL Azure database. The T-SQL statements may be executed in any order. The T-SQL statements have unpredictable execution times.
You have the following requirements:
•The package must maximize parallel processing of the T-SQL statements.
•After all the T-SQL statements have completed, a Send Mail task must notify administrators.
You need to design the SSIS 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.)
Answer:
Q28. 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:
Reference: http://msdn.microsoft.com/en-us/library/gg492088.aspx Reference: http://msdn.microsoft.com/en-us/library/gg492153.aspx
Q29. 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
Q30. You are deploying a new SQL Server Integration Services (SSIS) project to the test environment.
A package in the project uses a custom task component.
You need to ensure that the custom object is deployed on the test environment correctly. What should you do?
A. Run the package by using the dtexec /rep /conn command.
B. Create a reusable custom logging component.
C. Create an OnError event handler.
D. Use the gacutil command.
E. Use the dtutil /copy command.
F. Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration.
G. Run the package by using the dtexec /dumperror /conn command.
H. Use the Project Deployment Wizard.
I. Deploy the package by using an msi file.
J. Add a data tap on the output of a component in the package data flow.
K. Run the package by using the dtexecui.exe utility and the SQL Log provider.
Answer: D
Explanation:
Reference:
http://msdn.microsoft.com/en-us/library/ms403356.aspx