Implementing a Data Warehouse using SQL
Question No: 131 – (Topic 2)
You are developing a data flow transformation to merge two data sources. One source contains product data and the other source contains data about the country in which the product was manufactured. Both data sources contain a two-character CountryCode column and both use SQL Server. Both data sources contain an ORDER BY clause to sort the data by the CountryCode column in ascending order.
You use a Merge Join transformation to join the data.
You need to ensure that the Merge Join transformation works correctly without additional transformations. What should you do? (Each answer presents a part of the solution.
Choose all that apply.)
set the appropriate SortKeyPosition properties on the data sources.
set the ValidateExternalMetaData property on the Merge Join transformation to True.
set the IsSorted property on both data sources.
Set the MaxBuffersPerlnput property on the Merge Join transformation to 2.
Answer: A,C Explanation: References:
http://msdn.microsoft.com/en-us/library/ms141775.aspx http://msdn.microsoft.com/en-us/library/ms137653.aspx http://siddhumehta.blogspot.com/2009/05/validateexternalmetadata-property.html http://msdn.microsoft.com/en-us/library/ms135950.aspx
Question No: 132 DRAG DROP – (Topic 2)
You are developing a SQL Server Integration Services (SSIS) package that is ready for deployment to a production server. The package contains sensitive information secured by using the EncryptSensitiveWithUserKey package protection level.
You are preparing the package for deployment by the production operations team.
You need to ensure that the production operations team can open and execute the package without re-entering the sensitive information.
Which three steps 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.)
Question No: 133 – (Topic 2)
You are creating a SQL Server Integration Services (SSIS) package to retrieve product data from two different sources. One source is hosted in a SQL Azure database. Each source contains products for different distributors.
Products for each distributor source must be combined for insertion into a single product table destination.
You need to select the appropriate data flow transformation to meet this requirement.
Which transformation types should you use? (Each answer represents a complete solution. Choose all that apply.)
Slowly Changing Dimension
Question No: 134 – (Topic 2)
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?
Add CurrentValue and PreviousValue columns to the student dimension.
Enable Snapshot Isolation on the data warehouse.
Add an IsCurrent column to the student dimension.
Add StartDate and EndDate columns to the student dimension.
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 No: 135 – (Topic 2)
You are developing a SQL Server Integration Services (SSIS) package to implement an incremental data load strategy. The package reads data from a source system that uses the SQL Server change data capture (CDC) feature.
You have added a CDC Source component to the data flow to read changed data from the source system.
You need to add a data flow transformation to redirect rows for separate processing of insert, update, and delete operations.
Which data flow transformation should you use?
Explanation: The CDC splitter splits a single flow of change rows from a CDC source data flow into different data flows for Insert, Update and Delete operations
Question No: 136 CORRECT TEXT – (Topic 2)
You administer a Microsoft SQL Server 2016 database. The database contains a table that has the following definition:
You want to export data from the table to a flat file by using the SQL Server Import and Export Wizard.
You need to ensure that the following requirements are met:
->The first row of the file contains the first row of data.
->Each record is of the same length.
->The date follows the U.S. date format.
->The file supports international characters.
What should you do? (To answer, simply select the option or options in the answer area that you would configure.)
Answer: Tick the “Unicode” checkbox. Set the Format option to “Fixed Width”
Untick the “Column names in the first data row” checkbox.
Each record is of the same length = Fixed Width. The file supports international characters = Unicode.
The date follows the U.S. date format = Locale (already configured).
The first row of the file contains the first row of data = Uncheck “Column names in the first data row”
Question No: 137 – (Topic 2)
You are designing a partitioning strategy for a large fact table in a data warehouse. Tens of millions of new records are loaded into the data warehouse weekly, outside of business hours.
Most queries are generated by reports and by cube processing. Data is frequently queried at the day level and occasionally at the month level.
You need to partition the table to maximize the performance of queries. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
Partition the fact table by month, and compress each partition.
Partition the fact table by week.
Partition the fact table by year.
Partition the fact table by day, and compress each partition.
Question No: 138 HOTSPOT – (Topic 2)
You are editing a SQL Server Integration Services (SSIS) project named Project1 in SQL Server Data Tools.
A package Connection Manager has been parameterized with project scope.
You need to display the parameters that have been generated for the Connection Manager.
What should you use? To answer, select the appropriate setting or settings in the answer area.
Question No: 139 DRAG DROP – (Topic 2)
You administer a Microsoft SQL Server database. You want to import data from a text file to the database.
You need to ensure that the following requirements are met:
->Data import is performed by using a stored procedure.
->Data is loaded as a unit and is minimally logged.
Which data import command and recovery model should you choose? (To answer, drag the appropriate data import command or recovery model to the appropriate location or locations in the answer area. Each data import command or recovery model 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.)
http://msdn.microsoft.com/en-us/library/ms162802.aspx http://msdn.microsoft.com/en-us/library/ms188365.aspx http://msdn.microsoft.com/en-us/library/ms175937.aspx http://msdn.microsoft.com/en-us/library/aa337544.aspx
Question No: 140 – (Topic 2)
You maintain a SQL Server Integration Services (SSIS) package. The package was developed by using SQL Server 2008 Business Intelligence Development Studio (BIDS).
The package includes custom scripts that must be upgraded. You need to upgrade the package to SQL Server 2016.
Which tool should you use?
SQL Server Configuration Manager
SQL Server Agent
SSIS Upgrade Wizard in SQL Server Management Studio
SQL Server DTExecUI utility (dtexecui.exe)
Answer: C Explanation:
Use the SSIS Package Upgrade Wizard to migrate packages that were developed in SQL Server 2005 Integration Services (SSIS) and SQL Server 2008 Integration Services (SSIS) to the package format that the current release of Integration Services uses.
Reference: SSIS Upgrade Wizard (SSIS Package Upgrade Wizard)
Topic 3, Volume C
|Lowest Price Guarantee||Yes||No||No|
|Free VCE Simulator||Yes||No||No|