Microsoft 70-767 exam ready here! Get the latest 70-767 exam exercise questions and exam dumps pdf for free! 100% pass the exam to select
the full Microsoft 70-767 dumps : https://www.lead4pass.com/70-767.html the link to get VCE or PDF. All exam questions are updated!
Lead4pass offers the latest Microsoft 70-767 Google Drive
[Latest updates] Free Microsoft 70-767 dumps pdf download from Google Drive: https://drive.google.com/file/d/1l2VNxivG6nY-DODEQgADSh7PBTPvNc4F/
Examsall Exam Table of Contents:
- Microsoft 70-767 Practice testing questions from Youtube
- latest updated Microsoft 70-767 exam questions and answers
- Lead4Pass Microsoft discount code
- About lead4pass
Microsoft 70-767 Practice testing questions from Youtube
latest updated Microsoft 70-767 exam questions and answers
QUESTION 1
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 section, you will NOT be able to return to it. As a result, these questions will not
appear on the review screen.
Each night you receive a comma-separated values (CSV) file that contains different types of rows. Each row type has a
different structure. Each row in the CSV file is unique. The first column in every row is named Type. This column
identifies
the data type.
For each data type, you need to load data from the CSV file to a target table. A separate table must contain the number
of rows loaded for each data type.
Solution: You create a SQL Server Integration Services (SSIS) package as shown in the exhibit. (Click the Exhibit tab.)
Does the solution meet the goal?
A. Yes
B. No
Correct Answer: A
The conditional split is correctly placed before the count.
QUESTION 2
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 section, you will NOT be able to return to it. As a result, these questions will not
appear on the review screen.
Your company uses Microsoft SQL Server to deploy a data warehouse to an environment that has a SQL Server
Analysis Services (SSAS) instance. The data warehouse includes the Fact. Order table as shown in the following table
definition. The table has no indexes.
You must minimize the amount of space that indexes for the Fact. Order table consumes. You run the following queries
frequently. Both queries must be able to use a columnstore index:
You need to ensure that the queries complete as quickly as possible.
Solution: You create one columnstore index that includes the [Order Date Key], [Tax Amount], and [Total Excluding Tax]
columns.
Does the solution meet the goal?
A. Yes
B. No
Correct Answer: A
You should use a column store index.
Columnstore indexes are the standard for storing and querying large data warehousing fact tables. This index uses
column-based data storage and query processing to achieve gains up to 10 times the query performance in your data
warehouse over traditional row-oriented storage.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-2017
QUESTION 3
You are reviewing the design of an existing fact table named fast sales, 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 order total by month.
B. Create a view over the fact table to sum the order total by month.
C. Change the granularity of the fact table to month.
D. Partition the fact table by a product key.
Correct Answer: C
QUESTION 4
You are developing a SQL Server Integration Services (SSIS) package.
OnError and OnWarning events must be logged for viewing in the built-in SSIS reports by using SQL Server
Management Studio.
You need to execute the package and minimize the number of event types that are logged.
Which setting should you use? (To answer, change the appropriate setting in the answer area.)
Hot Area:
Correct Answer:
QUESTION 5
You have a Microsoft SQL Server data warehouse instance that supports several client applications.
The data warehouse includes the following tables: Dimension.SalesTerritory, Dimension.Customer, Dimension.Date,
Fact.Ticket, and Fact.Order. The Dimension.SalesTerritory and Dimension. Customer tables are frequently updated. The
Fact. The order table is optimized for weekly reporting, but the company wants to change it to daily. The Fact. The order table is
loaded by using an ETL process. Indexes have been added to the table over time, but the presence of these indexes
slows data loading.
All data in the data warehouse is stored on a shared SAN. All tables are in a database named DB1. You have a second
database named DB2 that contains copies of production data for a development environment. The data warehouse has
grown and the cost of storage has increased. Data older than one year is accessed infrequently and is considered
historical.
The following requirements must be met:
Implement table partitioning to improve the manageability of the data warehouse and to avoid the need to repopulate all
transactional data each night. Use a partitioning strategy that is as granular as possible.
Partition the Fact. Order table and retain a total of seven years of data.
Partition the Fact. Ticket table and retain seven years of data. At the end of each month, the partition structure must
apply a sliding window strategy to ensure that a new partition is available for the upcoming month and that the oldest
month of data is archived and removed.
Optimize data loading for the Dimension.SalesTerritory, Dimension.Customer, and Dimension.Date tables.
Incrementally load all tables in the database and ensure that all incremental changes are processed.
Maximize the performance during the data loading process for the Fact. Order partition.
Ensure that historical data remains online and available for querying.
Reduce ongoing storage costs while maintaining query performance for current data.
You are not permitted to make changes to the client applications.
You need to optimize data loading for the Dimension.SalesTerritory, Dimension.Customer, and Dimension.Date tables.
Which technology should you use for each table?
To answer, select the appropriate technologies in the answer area.
Hot Area:
Correct Answer:
Box 1: Temporal table
Box 2: Temporal table
Compared to CDC, Temporal tables are more efficient in storing historical data as it ignores insert actions.
Box 3: Change Data Capture (CDC)
By using change data capture, you can track changes that have occurred over time to your table. This kind of
functionality is useful for applications, like a data warehouse load process that needs to identify changes, so they can
correctly apply
updates to track historical changes over time.
CDC is good for maintaining slowly changing dimensions.
Scenario: Optimize data loading for the Dimension.SalesTerritory, Dimension.Customer, and Dimension.Date tables.
The Dimension.SalesTerritory and Dimension. Customer tables are frequently updated.
References:
https://www.mssqltips.com/sqlservertip/5212/sql-server-temporal-tables-vs-change-data-capture-vs-changetracking–part-2/
https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-table-usage-scenarios?view=sql-server-2017
QUESTION 6
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 section, you will NOT be able to return to it. As a result, these questions will not
appear on the review screen.
Your company uses Microsoft SQL Server to deploy a data warehouse to an environment that has a SQL Server
Analysis Services (SSAS) instance. The data warehouse includes the Fact. Order table as shown in the following table
definition. The table has no indexes.
You need to ensure that the queries complete as quickly as possible.
Solution: You create a measure for the Fact.Order table. Does the solution meet the goal?
A. Yes
B. No
Correct Answer: B
You should use a column store index.
Columnstore indexes are the standard for storing and querying large data warehousing fact tables. This index uses
column-based data storage and query processing to achieve gains up to 10 times the query performance in your data
warehouse over traditional row-oriented storage.
References: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sqlserver-2017
QUESTION 7
You are designing a data warehouse hosted on Windows Azure SQL Database. The data warehouse currently includes
the dimUser and dimRegion dimension tables and the fact sales fact table. The dimUser table contains records for each
user permitted to run reports against the warehouse, and the dim region table contains information about sales regions.
The system is accessed by users from certain regions, as well as by area supervisors and users from the corporate
headquarters.
You need to design a table structure to ensure that certain users can see sales data for only certain regions. Some
users must be permitted to see sales data from multiple regions.
What should you do?
A. For each region, create a view of the fact sales table that includes a WHERE clause for the region.
B. Create a user region table that contains primary key columns from the dimUser and dimRegion tables.
C. Add a region column to the dimUser table.
D. Partition the fact sales table on the region column.
Correct Answer: B
QUESTION 8
You are designing a SQL Server Integration Services (SSIS) 2016 package that imports data from a Windows Azure
SQL Database database into a SQL Server database. The SSIS package has the following requirements:
Every night, a very large amount of data is imported into the staging database.
Package processing time must be minimized.
The package must run on its own dedicated server when it is deployed to production.
Transaction log activity when data is imported must be minimized.
You need to design the package to meet the requirements.
Which destination component should you use?
A.
DataReader
B.
OLE DB
C.
ADO.NET
D.
SQL Server
Correct Answer: B
QUESTION 9
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 a database administrator for an e-commerce company that runs an online store. The company has the
databases described in the following table.
Each day, data from the table OnlineOrder in DB2 must be exported by partition. The tables must not be locked during
the process.
You need to write a Microsoft SQL Server Integration Services (SSIS) package that performs the data export. What
should you use it?
A. Lookup transformation
B. Merge transformation
C. Merge Join transformation
D. MERGE statement
E. Union All transformation
F. Balanced Data Distributor transformation
G. Sequential container
H. Foreach Loop container
Correct Answer: E
The Union All transformation combines multiple inputs into one output. For example, the outputs from five different Flat
File sources can be inputs to the Union All transformation and combined into one output. References:
https://docs.microsoft.com/en-us/sql/integration-services/data-flow/transformations/union-all-transformation
QUESTION 10
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.
Correct 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
QUESTION 11
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is
repeated in each question. Each question presents a different goal and answers choices, but the text of the scenario is
exactly the same in each question in this series.
You have a Microsoft SQL Server data warehouse instance that supports several client applications.
The data warehouse includes the following tables: Dimension.SalesTerritory, Dimension.Customer, Dimension.Date,
Fact.Ticket, and Fact.Order. The Dimension.SalesTerritory and Dimension. Customer tables are frequently updated.
The
Fact. The order table is optimized for weekly reporting, but the company wants to change it daily. The Fact. The order table is
loaded by using an ETL process. Indexes have been added to the table over time, but the presence of these indexes
slows
data loading.
All data in the data warehouse is stored on a shared SAN. All tables are in a database named DB1. You have a second
database named DB2 that contains copies of production data for a development environment. The data warehouse has
grown and the cost of storage has increased. Data older than one year is accessed infrequently and is considered
historical.
You have the following requirements:
– Implement table partitioning to improve the manageability of the data warehouse and to avoid the need to repopulate
all transactional data each night. Use a partitioning strategy that is as granular as possible.
-Partition the Fact. Order table and retain a total of seven years of data.
– Partition the Fact. Ticket table and retain seven years of data. At the end of each month, the partition structure must
apply a sliding window strategy to ensure that a new partition is available for the upcoming month and that the oldest month of data is archived and removed.
-Optimize data loading for the Dimension.SalesTerritory, Dimension.Customer, and Dimension.Date tables.
-Incrementally load all tables in the database and ensure that all incremental changes are processed.
-Maximize the performance during the data loading process for the Fact. Order partition.
-Ensure that historical data remains online and available for querying.
-Reduce ongoing storage costs while maintaining query performance for current data.
You are not permitted to make changes to the client applications.
You need to configure the Fact. Order table.
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:
Correct Answer:
Explanation:
From scenario: Partition the Fact.Order table and retain a total of seven years of data. Maximize the performance during
the data loading process for the Fact. Order partition.
Step 1: Create a partition function.
Using the CREATE PARTITION FUNCTION is the first step in creating a partitioned table or index.
Step 2: Create a partition scheme based on the partition function.
To migrate SQL Server partition definitions to SQL Data Warehouse simply:
1.
Eliminate the SQL Server partition scheme.
2.
Add the partition function definition to your CREATE TABLE.
Step 3: Execute an ALTER TABLE command to specify the partition function.
References: https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-partition
QUESTION 12
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.
Correct Answer: A
QUESTION 13
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 section, you will NOT be able to return to it As a result, these questions will not
appear on the review screen.
You have an on-premises Microsoft SQL Server instance and a Microsoft Azure SQL Data Warehouse instance. You
move data from the on-premises database to the data warehouse once each day by using a SQL Server Integration
Services (SSIS) package.
You observe that the package no longer completes within the allotted time.
You need to determine which tasks are taking a long time to complete.
Solution: You enable package logging within SSIS.
Does the solution meet the goal?
A. Yes
B. No
Correct Answer: B
Lead4Pass Microsoft discount code
About lead4pass
Lead4Pass has 7 years of exam experience! A number of professional Microsoft exam experts! Update exam questions throughout the year! The most complete exam questions and answers! The safest buying experience! The greatest free sharing of exam practice questions and answers!
Our goal is to help more people pass the Microsoft exam! Exams are a part of life, but they are important!
In the study, you need to sum up the study! Trust Lead4Pass to help you pass the exam 100%!
Summarize:
Examsall free to share Microsoft 70-767 exam exercise questions, 70-767 pdf, 70-767 exam video! Lead4pass updates exam questions and answers throughout the year!
Make sure you pass the exam successfully. Select lead4Pass 70-767 to pass Microsoft 70-767 exam “Implementing a Data Warehouse using SQL”.
ps.
Latest update Lead4pass 70-767 exam dumps: https://www.lead4pass.com/70-767.html (402 Q&As)
[Latest updates] Free Microsoft 70-767 Dumps pdf download from Google Drive: https://drive.google.com/file/d/1l2VNxivG6nY-DODEQgADSh7PBTPvNc4F/