Get Real 70-767 Exam Questions | 70-767 Question Answers Realexamdumps.com

Question  No : 1

A SQL Server Integration Services (SSIS) package on a computer is failing periodically in production. The package was created less than one year ago and was deployed to the SSIS catalog. Sometimes the package is started on a SQL Agent schedule; sometimes the package is started manually by an SSIS developer by using the Object Explorer in SQL Server Management Studio. You need to identify the authenticated user responsible for starting the package each time it failed in the past. Where can you find this information?

A. the SQL Server Log
B. the SSISDB.[catalog].[executions] view
C. the SSISDB.[catalog].[event_messages] view
D. the SQL Agent Job History
E. the SQL Agent Error Log

Answer: B

Question No : 2

You are reviewing the design of a customer dimension table in an existing data warehousehosted 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

Preparing Tips For Microsoft 70-767 Final Exam | 70-767 Dumps PDF Realexamdumps.com

Question No : 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 No : 4

You are designing an extract, transform, load (ETL) process for loading data from a SQL Server database into a large fact table in a data warehouse each day with the prior day's sales data. The ETL process for the fact table must meet the following requirements:
. Load new data in the shortest possible time.
. Remove data that is more than 36 months old.
. Ensure that data loads correctly.
. Minimize record locking.
. Minimize impact on the transaction log.
You need to design an ETL process that meets the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

A. Partition the destination fact table by date. Insert new data directly into the fact table and delete old data directly from the fact table.
B. Partition the destination fact table by date. Use partition switching and staging tables both to remove old data and to load new data.
C. Partition the destination fact table by customer. Use partition switching both to remove old data and to load new data into each partition.
D. Partition the destination fact table by date. Use partition switching and a staging table to remove old data. Insert new data directly into the fact table.

Answer: B

Pass 70-767 Certifications With 100% Passing | Microsoft 70-767 Updated Dumps

Question No : 5

The fact table must meet the following requirements:

. Include a column store index.

. Allow users to choose up to 10 dimension tables and up to five facts at one time.

. Maximize performance of queries that aggregate measures by using any of the 10 dimensions.

. Support billions of rows.

. Use the most efficient design strategy.

You need to design the fact table to meet the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

A. Design a fact table with 5 dimensional key columns and 10 measure columns.

Place the column store index on the dimensional key columns.

B. Design a fact table with 5 dimensional key columns and 10 measure columns.

Place the column store index on the measure columns.

C. Design a fact table with 10 dimensional key columns and 5 measure columns. Place the column store index on the dimensional key columns and the measure columns.

D. Design a fact table with 10 dimensional key columns and 5 measure columns. Place the column store index on only the measure columns.

Answer: C

Question No : 6

You are designing a SQL Server Integration Services (SSIS) solution that will load multiple Online Transactional Processing (OLTP) data sources into a SQL Server data mart. You have the following requirements:

. Ensure that the process supports the creation of an exception report that details possible duplicate key values, null ratios within columns, and column-length distributions of values.

. Ensure that users can generate the exception report in an XML format.

. Use the minimum development effort.

You need to design the SSIS solution to meet the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

A. Use a Data Profiling task. Use a Data Flow task to extract the XML output of the Data Profiling task into a SQL Server table. Query the table to view the exceptions.

B. Use Data Flow tasks to process the clean data.

C. Use a Data Profiling task. Read the exceptions in Data Profile Viewer.

D. Design a stored procedure that examines data for common dirty data patterns. Use an Execute SQL task.

Answer: C

Get Monthly Microsoft 70-767 Exam Updates - 70-767Questions With Valid Answers - Braindumps

Question No : 7

You are designing a partitioning strategy for a large fact table in a data warehouse. Tens of millions of new records are loaded into the data warehouse weekly, outside of business hours. Most queries are generated by reports and by cube processing. Data is frequently queried at the day level and occasionally at the month level. You need to partition the table to maximize the performance of queries. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

A. Partition the fact table by month, and compress each partition.

B. Partition the fact table by week.

C. Partition the fact table by year.

D. Partition the fact table by day, and compress each partition.

Answer: D

Question No : 8

You manage an inventory system that has a table named Products. The Products table has several hundred columns. You generate a report that relates two columns named ProductReference and ProductName from the Products table. The result is sorted by a column named QuantityInStock from largest to smallest. You need to create an index that the report can use. How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area.

Answer:

Get Latest Microsoft 70-767 Exam Braindumps With Money Back Assurance By Realexamdumps.com

Question No : 9

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 a data warehouse that stores information about products, sales, and orders for a manufacturing company. The instance contains a database that has two tables named SalesOrderHeader and SalesOrderDetail. SalesOrderHeader has 500,000 rows and SalesOrderDetail has 3,000,000 rows. Users report performance degradation when they run the following stored procedure:

You need to optimize performance.

Solution: You run the following Transact-SQL statement:

Does the solution meet the goal?

A. Yes

B. No

Answer: B

Explanation:

Microsoft recommend against specifying 0 PERCENT or 0 ROWS in a CREATE STATISTICS..WITH SAMPLE statement. When 0 PERCENT or ROWS is specified, the statistics object is created but does not contain statistics data.

References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-statistics-transactsql

Question No : 10

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 an ASP.NET assembly. You add a script task that references this assembly to each data warehouse load package. Does the solution meet the goal?

A. Yes

B. No

Answer: B

Explanation:

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/controlflow

Pass Your Microsoft 70-767 Actual Test In Just One Day With Realexamdumps.com Braindumps