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 loading data from an OLTP database to a data warehouse. The database contains a table named Sales.
Sales contains details of records that have a type of refund and records that have a type of sales. The data warehouse design contains a table for sales data and a table for refund data.
Which component should you use to load the data to the warehouse?
Correct Answer:
B
The Conditional Split transformation can route data rows to different outputs depending on the content of the data. The implementation of the Conditional Split transformation is similar to a CASE decision structure in a programming language. The transformation evaluates expressions, and based on the results, directs the data row to the specified output. This transformation also provides a default output, so that if a row matches no expression it is directed to the default output.
References:
https://docs.microsoft.com/en-us/sql/integration-services/data-flow/transformations/conditionalsplit-
Transformation
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?
Correct Answer:
B
You manage Master Data Services (MDS).
You need to create a new entity with the following requirements:
• Maximize the performance of the MDS system.
• Ensure that the Entity change logs are stored.
You need to configure the Transaction Log Type setting. Which type should you use?
Correct Answer:
D
You have a data warehouse.
You need to move a table named Fact.ErrorLog to a new filegroup named LowCost.
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.
Solution:
Step 1: Add a filegroup named LowCost to the database. First create a new filegroup.
Step 2:
The next stage is to go to the ‘Files’ page in the same Properties window and add a file to the filegroup (a filegroup always contains one or more files)
Step 3:
To move a table to a different filegroup involves moving the table’s clustered index to the new filegroup. While this may seem strange at first this is not that surprising when you remember that the leaf level of the clustered index actually contains the table data. Moving the clustered index can be done in a single statement using the DROP_EXISTING clause as follows (using one of the AdventureWorks2008R2 tables as an example) :
CREATE UNIQUE CLUSTERED INDEX PK_Department_DepartmentID ON HumanResources.Department(DepartmentID)
WITH (DROP_EXISTING=ON,ONLINE=ON) ON SECONDARY
This recreates the same index but on the SECONDARY filegroup.
References:
http://www.sqlmatters.com/Articles/Moving a Table to a Different Filegroup.aspx
Does this meet the goal?
Correct Answer:
A
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. The package design consists of two differently structured sources in a single data flow. The Sales source retrieves sales transactions from a SQL Server database, and the Product source retrieves product details from an XML file.
You need to combine the two data flow sources into a single output dataset. Which SSIS Toolbox item should you use?
Correct Answer:
G
The Merge Join transformation provides an output that is generated by joining two sorted datasets using a FULL, LEFT, or INNER join. For example, you can use a LEFT join to join a table that includes product information with a table that lists the country/region in which a product was manufactured. The result is a table that lists all products and their country/region of origin.
References:
https://docs.microsoft.com/en-us/sql/integration-services/data-flow/transformations/merge-join-transformation
