NEW QUESTION 1
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 multi-dimensional models.
The data warehouse stores data related to your company sales, financial transactions and financial budgets. All data for the data warehouse originates from the company's business financial system.
The data warehouse includes the following tables:

You must implement a partitioning scheme for 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.

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.
End of repeated scenario
You need to perform the first step to partition the fact .Transaction table.
How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area.

Answer:

Explanation: CREATE PROCEDURE
[DateRange] (INT) AS RANGE LEFT

NEW 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 sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have the following line-of-business solutions:
ERP system
Online WebStore
Partner extranet
One or more Microsoft SQL Server instances support each solution. Each solution has its own product catalog. You have an additional server that hosts SQL Server Integration Services (SSIS) and a data warehouse. You populate the data warehouse with data from each of the line-of-business solutions. The data warehouse does not store primary key values from the individual source tables.
The database for each solution has a table named Products that stored product information. The Products table in each database uses a separate and unique key for product records. Each table shares a column named ReferenceNr between the databases. This column is used to create queries that involve more than once solution.
You need to load data from the individual solutions into the data warehouse nightly. The following requirements must be met:
If a change is made to the ReferenceNr column in any of the sources, set the value of IsDisabled to True and create a new row in the Products table.
If a row is deleted in any of the sources, set the value of IsDisabled to True in the data warehouse. Solution: Perform the following actions:
Enable the Change Tracking feature for the Products table in the three source databases.
Query the CHANGETABLE function from the sources for the deleted rows.
Set the IsDIsabled column to True on the data warehouse Products table for the listed rows. Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: We must check for updated rows, not just deleted rows.
References: https://www.timmitchell.net/post/2021/01/18/getting-started-with-change-tracking-in-sql-server/

NEW QUESTION 3
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 have a database named DB1 that has change data capture enabled.
A Microsoft SQL Server Integration Services (SSIS) job runs once weekly. The job loads changes from DB1 to a data warehouse by querying the change data capture tables.
You remove the Integration Services job.
You need to stop tracking changes to the database. The solution must remove all the change data capture configurations from DB1.
Which stored procedure should you execute?

• A. catalog.deploy_project
• B. catalog.restore_project
• C. catalog.stop.operation
• D. sys.sp.cdc.addjob
• E. sys.sp.cdc.changejob
• F. sys.sp_cdc_disable_db
• G. sys.sp_cdc_enable_db
• H. sys.sp_cdc.stopJob

Answer: F

Explanation: sys.sp_cdc_disable_db disables change data capture for all tables in the database currently enabled. All system objects related to change data capture, such as change tables, jobs, stored procedures and functions, are dropped.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-disable-db-transa

NEW QUESTION 4
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?

• A. Yes
• B. No

Answer: B

NEW QUESTION 5
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 the following line-of-business solutions:
If a change is made to the ReferenceNr column in any of the sources, set the value of IsDisabled to True and create a new row in the Products table.
If a row is deleted in any of the sources, set the value of IsDisabled to True in the data warehouse.
One or more Microsoft SQL Server instances support each solution. Each solution has its own product catalog. You have an additional server that hosts SQL Server Integration Services (SSIS) and a data warehouse. You populate the data warehouse with data from each of the line-of-business solutions. The data warehouse does not store primary key values from the individual source tables.
The database for each solution has a table named Products that stored product information. The Products table in each database uses a separate and unique key for product records. Each table shares a column named ReferenceNr between the databases. This column is used to create queries that involve more than once solution.
You need to load data from the individual solutions into the data warehouse nightly. The following requirements must be met:
Enable the Change Tracking for the Product table in the source databases.
Query the cdc.fn_cdc_get_all_changes_capture_dbo_products function from the sources for updated rows.
Set the IsDisabled column to True for rows with the old ReferenceNr value.
Create a new row in the data warehouse Products table with the new ReferenceNr value.
Solution: Perform the following actions: Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: We must also handle the deleted rows, not just the updated rows.
References: https://solutioncenter.apexsql.com/enable-use-sql-server-change-data-capture/

NEW QUESTION 6
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 implementing a Microsoft SQL Server data warehouse with a multi-dimensional data model. You have a fact table that includes sales data for all products. The model includes a dimension named Geography that stores all geographies. You create a dimension that has a foreign key and provides the ability to analyze sales by the following sales channels: Internet or retail store.
You need to update the data model to allow business users to analyze Internet sales by geography without changing the overall structure of the data model.
What should you do?

• A. star schema
• B. snowflake schema
• C. conformed dimension
• D. slowly changing dimension (SCD)
• E. fact table
• F. semi-additive measure
• G. non-additive measure
• H. dimension table reference relationship

Answer: D

NEW QUESTION 7
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-transact-sql

NEW QUESTION 8
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 implementing a Microsoft SQL Server data warehouse with a multi-dimensional data model. When testing a pilot version of the data warehouse, business users observe that the number of products in
stock is inaccurate. The number of products in stock always increases and represents the total number of
products that have ever been in stock.
You need to correct the existing model and ensure that it reflects the number of in-stock products. You must not change the overall structure of the data model.
What should you do?

• A. star schema
• B. snowflake schema
• C. conformed dimension
• D. slowly changing dimension (SCD)
• E. fact table
• F. semi-additive measure
• G. non-additive measure
• H. dimension table reference relationship

Answer: H

NEW QUESTION 9
You are designing a data transformation process using Microsoft SQL Server Integration Services (SSIS). You need to ensure that every row is compared with every other row during transformation.
What should you configure? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.

Answer:

Explanation: When you configure the Fuzzy Grouping transformation, you can specify the comparison algorithm that the transformation uses to compare rows in the transformation input. If you set the Exhaustive property to true, the transformation compares every row in the input to every other row in the input. This comparison algorithm may produce more accurate results, but it is likely to make the transformation perform more slowly unless the number of rows in the input is small.
References:
https://docs.microsoft.com/en-us/sql/integration-services/data-flow/transformations/fuzzy-grouping-transformati

NEW QUESTION 10
You have a series of analytic data models and reports that provide insights into the participation rates for sports at different schools. Users enter information about sports and participants into a client application. The application stores this transactional data in a Microsoft SQL Server database. A SQL Server Integration Services (SSIS) package loads the data into the models.
When users enter data, they do not consistently apply the correct names for the sports. The following table shows examples of the data entry issues.

You need to improve the quality of the data.
Which four 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:

Explanation: References: https://docs.microsoft.com/en-us/sql/data-quality-services/perform-knowledge-discovery

NEW QUESTION 11
You are developing a Microsoft SQL Server Integration Services (SSIS) package that loads a data warehouse. You need to inspect the data that is being processed by the package. What should you do first?

• A. Set a break point on the Control Flow path.
• B. Enable SQL Trace.
• C. Enable logging on the Data Flow path.
• D. Enable a data viewer on the Data Flow path.

Answer: A

NEW QUESTION 12
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.
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 consume. 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

Answer: A

Explanation: You should use a columnstore 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-serv

NEW 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 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 increase the relative weights for Address Line 1 of the matching policy.
Does this meet the goal?

• A. Yes
• B. NO

Answer: B

Explanation: Decrease the Min. matching score.
A data matching project consists of a computer-assisted process and an interactive process. The matching project applies the matching rules in the matching policy to the data source to be assessed. This process assesses the likelihood that any two rows are matches in a matching score. Only those records with a probability of a match greater than a value set by the data steward in the matching policy will be considered a match.
References: https://docs.microsoft.com/en-us/sql/data-quality-services/data-matching

NEW QUESTION 14
You have a data warehouse that contains a fact table named Table1 and a Product table named Dim1. Dim1 is configured as shown in the following table.

You are adding a second OLTP system to the data warehouse as a new fact table named Table2. The Product table of the OLTP system is configured as shown in the following table

You need to modify Dim1 to ensure that the table can be used for both fact tables.
Which two actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

• A. Modify the data type of the Weight column in Dim1 to decimal (19, 2).
• B. Add the SalesUnit column to Dim1.
• C. Modify the data type of the Name column in Dim1 to varchar (85).
• D. Drop the ProductKey column from Dim1 and replace the column with the ProductIdentifier column.
• E. Drop the Color column from Dim1.
• F. Modify the data type of the ProductKey column in Dim1 to char (18).

Answer: AD

NEW QUESTION 15
You have a data warehouse named DW1 that contains 20 years of data. DW1 contains a very large fact table. New data is loaded to the fact table monthly.
Many reports query DW1 for the past year of data. Users frequently report that the reports are slow.
You need to modify the fact table to minimize the amount of time it takes to run the reports. The solution must ensure that other reports can continue to be generated from DW1.
What should you do?

• A. Move the historical data to SAS disks and move the data from the past year to SSD disk
• B. Run the ALTERTABLE statement.
• C. Move all the data to SSD disk
• D. Load and archive the data by using partition switching.
• E. Move all the data to SAS disk
• F. Load and archive the data by using partition switching.
• G. Move the historical data to SAS disks and move the data for the past year to SSD disk
• H. Create a distributed partitioned view.

Answer: A

Explanation: We use ALTER TABLE to partition the table.

NEW QUESTION 16
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.

Answer:

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-