最新的Microsoft Implementing a SQL Data Warehouse - 70-767免費考試真題
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 developing a Microsoft SQL Server Integration Services (SSIS) package.
You need to ensure that the packa
ge records the current Log Sequence Number (LSN) in the source database before the package begins reading source tables.
Which SSIS Toolbox item should you use?
You are developing a Microsoft SQL Server Integration Services (SSIS) package.
You need to ensure that the packa
ge records the current Log Sequence Number (LSN) in the source database before the package begins reading source tables.
Which SSIS Toolbox item should you use?
正確答案: C
說明：（僅 Fast2test 成員可見）
A company plans to load data from a CSV file that is stored in a Microsoft Azure Blob storage container.
You need to load the data.
How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
You need to load the data.
How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
正確答案:
Explanation
Create DATABASE
CREATE EXTERNAL
BULK invoice
From invoice.csv
You deploy a Microsoft Server database that contains a staging table named EmailAddress_Import. Each night, a bulk process will import customer information from an external database, cleanse the data, and then insert it into the EmailAddress table. Both tables contain a column named EmailAddressValue that stores the email address.
You need to implement the logic to meet the following requirements:
* Email addresses that are present in the EmailAddress_Import table but not in the EmailAddress table must be inserted into the EmailAddress table.
* Email addresses that are not in the EmailAddress_Import but are present in the EmailAddress table must be deleted from the EmailAddress table.
How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
You need to implement the logic to meet the following requirements:
* Email addresses that are present in the EmailAddress_Import table but not in the EmailAddress table must be inserted into the EmailAddress table.
* Email addresses that are not in the EmailAddress_Import but are present in the EmailAddress table must be deleted from the EmailAddress table.
How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
正確答案:
Explanation
Box 1: EmailAddress
The EmailAddress table is the target.
Box 2: EmailAddress_import
The EmailAddress_import table is the source.
Box 3: NOT MATCHED BY TARGET
Box 4: NOT MATCHED BY SOURCE
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql
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 answer 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.Order table is optimized for weekly reporting, but the company wants to change it daily. The Fact.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 optimize data loading for the Dimension.Customer table.
Which three Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.
NOTE: You will not need all of the Transact-SQL segments.
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.Order table is optimized for weekly reporting, but the company wants to change it daily. The Fact.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 optimize data loading for the Dimension.Customer table.
Which three Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.
NOTE: You will not need all of the Transact-SQL segments.
正確答案:
Explanation
Step 1: USE DB1
From Scenario: 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.
Step 2: EXEC sys.sp_cdc_enable_db
Before you can enable a table for change data capture, the database must be enabled. To enable the database, use the sys.sp_cdc_enable_db stored procedure.
sys.sp_cdc_enable_db has no parameters.
Step 3: EXEC sys.sp_cdc_enable_table
@source schema = N 'schema' etc.
Sys.sp_cdc_enable_table enables change data capture for the specified source table in the current database.
Partial syntax:
sys.sp_cdc_enable_table
[ @source_schema = ] 'source_schema',
[ @source_name = ] 'source_name' , [,[ @capture_instance = ] 'capture_instance' ]
[,[ @supports_net_changes = ] supports_net_changes ]
Etc.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-enable-table-trans
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-enable-db-transac
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 in the review screen.
You configure a new matching policy Master Data Services (MDS) as shown in the following exhibit.
You review the Matching Results of the policy and find that the number of new values matches the new values.
You verify that the data contains multiple records that have similar address values, and you expect some of the records to match.
You need to increase the likelihood that the records will match when they have similar address values.
Solution: You decrease the minimum matching score of the matching policy.
Does this meet the goal?
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 in the review screen.
You configure a new matching policy Master Data Services (MDS) as shown in the following exhibit.
You review the Matching Results of the policy and find that the number of new values matches the new values.
You verify that the data contains multiple records that have similar address values, and you expect some of the records to match.
You need to increase the likelihood that the records will match when they have similar address values.
Solution: You decrease the minimum matching score of the matching policy.
Does this meet the goal?
正確答案: A
說明：（僅 Fast2test 成員可見）
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 in 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?
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 in 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
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 in the review screen.
You plan to deploy a Microsoft SQL server that will host a data warehouse named DB1.
The server will contain four SATA drives configured as a RAID 10 array.
You need to minimize write contention on the transaction log when data is being loaded to the database.
Solution: You add more data files to DB1.
Does this meet the goal?
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 in the review screen.
You plan to deploy a Microsoft SQL server that will host a data warehouse named DB1.
The server will contain four SATA drives configured as a RAID 10 array.
You need to minimize write contention on the transaction log when data is being loaded to the database.
Solution: You add more data files to DB1.
Does this meet the goal?
正確答案: A
說明：（僅 Fast2test 成員可見）
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 answer choices, but the text of the scenario is exactly the same in each question in the series.
Start of repeated scenario
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.Order table is optimized for weekly reporting, but the company wants to change it to daily. The FactOrder 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.
* 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 FactOrder 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.
End of repeated scenario
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.
Start of repeated scenario
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.Order table is optimized for weekly reporting, but the company wants to change it to daily. The FactOrder 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.
* 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 FactOrder 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.
End of repeated scenario
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.
正確答案:
Explanation
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 need 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-change-track
https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-table-usage-scenarios?view=sql-server
You have a database named OnlineSales that contains a table named Customers. You plan to copy incremental changes from the Customers table to a data warehouse every hour.
You need to enable change tracking for the Customers table.
How should you complete the Transact-SQL statements? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
You need to enable change tracking for the Customers table.
How should you complete the Transact-SQL statements? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
正確答案:
Explanation
Box 1: DATABASE [OnlineSales]
Before you can use change tracking, you must enable change tracking at the database level. The following example shows how to enable change tracking by using ALTER DATABASE.
ALTER DATABASE AdventureWorks2012
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)
Box 2: CHANGE_TRACKING = ON
ALTER SET CHANGE_RETENTION
Box 3: ALTER TABLE [dbo].[Customers]
Change tracking must be enabled for each table that you want tracked. When change tracking is enabled, change tracking information is maintained for all rows in the table that are affected by a DML operation.
The following example shows how to enable change tracking for a table by using ALTER TABLE.
ALTER TABLE Person.Contact
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)
Box 4: ENABLE CHANGE_TRACKING
References:
https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/enable-and-disable-change-tracking-sql-
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 answer choices, but the text of the scenario is exactly the same in each question In the series.
Start of repeated scenario
Contoso. Ltd. has a Microsoft SQL Server environment that includes SQL Server Integration Services (SSIS), a data warehouse, and SQL Server Analysis Services (SSAS) Tabular and multidimensional models.
The data warehouse stores data related to your company sales, financial transactions and financial budgets. All data for the data warenouse originates from the company's business financial system.
The data warehouse includes the following tables:
The company plans to use Microsoft Azure to store older records from the data warehouse. You must modify the database to enable the Stretch Database capability.
Users report that they are becoming confused about which city table to use for various queries. You plan to create a new schema named Dimension and change the name of the dbo.du_city table to Diamension.city. Data loss is not permissible, and you must not leave traces of the old table in the data warehouse.
Pal to create a measure that calculates the profit margin based on the existing measures.
You must implement a partitioning scheme few the fact. Transaction table to move older data to less expensive storage. Each partition will store data for a single calendar year, as shown in the exhibit (Click the Exhibit button.) You must align the partitions.
You must improve performance for queries against the fact.Transaction table. You must implement appropriate indexes and enable the Stretch Database capability.
End of repeated scenario
You need to resolve the problems reported about the dia__city table.
How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
Start of repeated scenario
Contoso. Ltd. has a Microsoft SQL Server environment that includes SQL Server Integration Services (SSIS), a data warehouse, and SQL Server Analysis Services (SSAS) Tabular and multidimensional models.
The data warehouse stores data related to your company sales, financial transactions and financial budgets. All data for the data warenouse originates from the company's business financial system.
The data warehouse includes the following tables:
The company plans to use Microsoft Azure to store older records from the data warehouse. You must modify the database to enable the Stretch Database capability.
Users report that they are becoming confused about which city table to use for various queries. You plan to create a new schema named Dimension and change the name of the dbo.du_city table to Diamension.city. Data loss is not permissible, and you must not leave traces of the old table in the data warehouse.
Pal to create a measure that calculates the profit margin based on the existing measures.
You must implement a partitioning scheme few the fact. Transaction table to move older data to less expensive storage. Each partition will store data for a single calendar year, as shown in the exhibit (Click the Exhibit button.) You must align the partitions.
You must improve performance for queries against the fact.Transaction table. You must implement appropriate indexes and enable the Stretch Database capability.
End of repeated scenario
You need to resolve the problems reported about the dia__city table.
How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
正確答案:
