NEW QUESTION 1
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 are configuring a Microsoft SQL server named ow1 for a new data warehouse. The server contains eight drives and eight processor cores. Each drive uses a separate physical disk.
You need to configure storage for the tempdb database. The solution must minimize the amount of time it takes to process daily ETL jobs.
Solution: You configure eight files for the tenpdb database. You place the files on a drive that contains the operating system files.
Does this meet the goal?

• A. Yes
• B. No

Answer: B

NEW QUESTION 2
You have a data warehouse named DW1.
InDvfe you plan to create a table named Tablel that will be partitioned by hour. Tablel will contain the last three hours of data.
You plan to implement a sliding window process for inserting data into Tablel.
You need to recommend the minimum number of partitions that must be included in Tablel to support the planned implementation. The solution must minimize the number of transaction log records created during the insert process.
How many partitions should you recommend?

• A. 3
• B. 5
• C. 9
• D. 24

Answer: B

NEW QUESTION 3
Your company has a Microsoft SQL Server data warehouse instance. The human resources department assigns all employees a unique identifier. You plan to store this identifier in a new table named Employee.
You create a new dimension to store information about employees by running the following Transact-SQL statement:

You have not added data to the dimension yet. You need to modify the dimension to implement a new column named [EmployeeKey]. The new column must use unique values.
How should you complete the Transact-SQL statements? To answer, select the appropriate Transact-SQL segments in the answer area.

Answer:

Explanation:

NEW QUESTION 4
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 create a new knowledge base to improve the quality of the sport name data.
How should you configure the knowledge base? To answer, select the appropriate options in the dialog box in the answer area.

Answer:

Explanation: Spot 1: Create Knowledge base from: None
Select None if you do not want to base the new knowledge base on an existing knowledge base or data file.

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

• 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

Answer: E

Explanation: 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

NEW 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 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/control-flow

NEW QUESTION 7
You plan to deploy several Microsoft SQL Server Integration Services (SSIS) packages to a highly available SQL Server instance. The instance is configured to use an AlwaysOn availability group that has two replicas.
You need to identify which deployment method must be used to ensure that the packages are always accessible from all the nodes in the availability group.
Which deployment method should you use for the packages?

• A. Deploy to the msdb database on the secondary replica.
• B. Deploy to the msdb database on the primary replica.
• C. Deploy to a file on the hard drive of the primary replica.
• D. Deploy to a shared folder on a file server.

Answer: A

Explanation: Before you can configure SSIS to enable support of AlwaysOn on the new added secondary Replicas, you must connect to all new added secondary replicas.

Note: To use SSIS with AlwaysOn, you’ll need to add the SSIS Catalog (SSISDB) into an Availability Group. You’ll need to do the following steps:
Make sure you meet the prerequisites for using AlwaysOn
Connect to every node and create the SSISDB catalog. We need to create the catalog even on secondary nodes to create the other server-level objects (cleanup jobs, keys, accounts etc) that are used by SSIS.
Delete the SSISDB databases on secondary nodes.
Create an availability group, specifying SSISDB as the user database
Specify secondary replicas.
References: https://chrislumnah.com/2021/05/09/enabling-alwayson-for-ssisdb/

NEW QUESTION 8
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 a Microsoft SQL server that has Data Quality Services (DQS) installed.
You need to review the completeness and the uniqueness of the data stored in the matching policy. Solution: You create a matching rule.
Does this meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: Use a matching rule, and use completeness and uniqueness data to determine what weight to give a field in the matching process.
If there is a high level of uniqueness in a field, using the field in a matching policy can decrease the matching results, so you may want to set the weight for that field to a relatively small value. If you have a low level of uniqueness for a column, but low completeness, you may not want to include a domain for that column.
References:
https://docs.microsoft.com/en-us/sql/data-quality-services/create-a-matching-policy?view=sql-server-2021

NEW 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 implementing the data load process for a data warehouse.
The data warehouse uses daily partitions to store data added or modified during the last 60 days. Older data is stored in monthly partitions.
You need to ensure that the ETL process can modify the partition scheme during the data load process. Which component should you use to load the data to the data warehouse?

• A. the Slowly Changing Dimension transformation
• B. the Conditional Split transformation
• C. the Merge transformation
• D. the Data Conversion transformation
• E. an Execute SQL task

Answer: E

NEW QUESTION 10
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:

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.dia_city table to Dimension.city. Data loss is not permissible, and you must not leave traces of the old table in the data warehouse.
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.
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 configure the fact. Transaction 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.

Answer:

Explanation:

NEW QUESTION 11
You are testing a Microsoft SQL Server Integration Services (SSIS) package. The package includes the Control Flow task shown in the Control Flow exhibit (Click the Exhibit button) and the Data Flow task shown in the Data Flow exhibit. (Click the Exhibit button.)


You declare a variable named Seed as shown in the Variables exhibit. (Click the Exhibit button.) The variable is changed by the Script task during execution.

You need to be able to interrogate the value of the Seed variable after the Script task completes execution. For each of the following statements, select Yes if the statement is true. Otherwise, select No.

Answer:

Explanation: References:
https://docs.microsoft.com/en-us/sql/integration-services/variables-window

NEW QUESTION 12
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 three databases as described in the following table.

You plan to load at least one million rows of data each night from DB1 into the OnlineOrder table. You must load data into the correct partitions using a parallel process.
You create 24 Data Flow tasks. You must place the tasks into a component to allow parallel load. After all of the load processes compete, the process must proceed to the next task.
You need to load the data for the OnlineOrder table. What should you use?

• 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

Answer: H

Explanation: The Parallel Loop Task is an SSIS Control Flow task, which can execute multiple iterations of the standard Foreach Loop Container concurrently.
References:
http://www.cozyroc.com/ssis/parallel-loop-task

NEW QUESTION 13
You have a database named DB1 that contains millions of rows. You plan to perform a weekly audit of the changes to the rows.
You need to ensure that you can view which rows were modified and the hour that the modification occurred. What should you do?

• A. Enable Policy-Based Management
• B. Configure Stretch Database.
• C. Configure an SSIS database.
• D. Enable change data capture.

Answer: D

Explanation: SQL Server 2021 provides two features that track changes to data in a database: change data capture and change tracking.
Change data capture provides historical change information for a user table by capturing both the fact that DML changes were made and the actual data that was changed. Changes are captured by using an asynchronous process that reads the transaction log and has a low impact on the system.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/track-data-changes-sql-server

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

Answer:

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-tran
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-enable-db-transa

NEW QUESTION 15
You are developing a Microsoft SQL Server Master Data Services (MDS) solution.
The model contains an entity named Product. The Product entity has three user-defined attributes named category. Subcategory, and Price, respectively.
You need to ensure that combinations of values stored in the category and subcategory attributes are unique. What should you do?

• A. Create a derived hierarchy based on the category and subcategory attribute
• B. Use the category attribute as the top level for the hierarchy.
• C. Publish two business rules, one for each of the Category and Subcategory attributes.
• D. Set the value of the Attribute Type property for the Category and Subcategory attributes to Domain-based.
• E. Create a custom index that will be used by the Product entity.

Answer: D

NEW QUESTION 16
You plan to use the dtutil.exe utility with Microsoft SQL Server Integration Services (SSIS) to customize packages. You need to create a new package ID for package1 on Server1. Which dtutil.exe command should you run?

• A. dtutil.exe /FILE c:repositorypackagel.dtsx /DestServer Server! /COPY SQL;package1.dtsx
• B. dtutil.exe /I /FILE c:repositorypackagel.dtsx
• C. dtutil.exe /SQL package1 /COPY OTS;c:repositorypackage1.dtsx
• D. dtutil.exe /SQL package1 /DELETE

Answer: A