Microsoft Implementing a Data Warehouse with Microsoft SQL Server 2012/2014 (70-463) Free Practice Test
Question 1
You are developing a SQL Server Integration Services (SSIS) package that imports unsorted data into a data warehouse hosted on Microsoft Azure SQL Database.
You have the following requirements:
*A destination table must contain all of the data in two source tables.
*Duplicate records must be inserted into the destination table.
You need to develop a data flow that imports the data while meeting the requirements.
How should you develop the data flow? (To answer, drag the appropriate transformation from the list of transformations to the correct location in the answer area.)
You have the following requirements:
*A destination table must contain all of the data in two source tables.
*Duplicate records must be inserted into the destination table.
You need to develop a data flow that imports the data while meeting the requirements.
How should you develop the data flow? (To answer, drag the appropriate transformation from the list of transformations to the correct location in the answer area.)
Correct Answer:
Explanation
References:
http://msdn.microsoft.com/en-us/library/ms141703.aspx
http://msdn.microsoft.com/en-us/library/ms141775.aspx
http://msdn.microsoft.com/en-us/library/ms1410
http://msdn.microsoft.com/en-us/library/ms140182
Question 2
You deploy a server that has SQL Server installed.
You deploy a SQL Server Integration Services (SSIS) package to the server.
You need to automate the execution of the package. The solution must ensure that you receive a notification if the package to execute.
In which order should you perform all the actions?
You deploy a SQL Server Integration Services (SSIS) package to the server.
You need to automate the execution of the package. The solution must ensure that you receive a notification if the package to execute.
In which order should you perform all the actions?
Correct Answer:
Explanation
Question 3
You are editing a SQL Server Integration Services (SSIS) package that contains three Execute SQL tasks and no other tasks. The package and all three Execute SQL tasks have their TransactionOption property set to Supported.
You need to ensure that if any of the Execute SQL tasks fail, all three tasks will roll back their changes.
What should you do?
You need to ensure that if any of the Execute SQL tasks fail, all three tasks will roll back their changes.
What should you do?
Correct Answer: D
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 4
You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.)
Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table. You need to assign the appropriate constraints and table properties to ensure data integrity and visibility. On which column in the Employee table should you use an identity specification to include a seed of 1,000 and an increment of 1?
Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table. You need to assign the appropriate constraints and table properties to ensure data integrity and visibility. On which column in the Employee table should you use an identity specification to include a seed of 1,000 and an increment of 1?
Correct Answer: B
Question 5
You have a database named Sales and a data warehouse named DataW.
From Sales, you plan to bulk insert to a table in DataW that is more than 2TB. The process will insert a minimum of 11 million rows simultaneously.
You need to identify which data storage strategy must be used to minimize load times.
Which data storage strategy should you identify?
From Sales, you plan to bulk insert to a table in DataW that is more than 2TB. The process will insert a minimum of 11 million rows simultaneously.
You need to identify which data storage strategy must be used to minimize load times.
Which data storage strategy should you identify?
Correct Answer: D
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 6
You are designing a package control flow. The package moves sales order data from a SQL Azure transactional database to an on-premise reporting database. The package will run several times a day, while new sales orders are being added to the transactional database. The current design of the package control flow is shown in the answer area. (Click the Exhibit button.)
The Insert New Orders Data Flow task must meet the following requirements:
*Usage of the tempdb database should not be impacted.
*Concurrency should be maximized, while only reading committed transactions.
*If the task fails, only that task needs to be rolled back.
You need to configure the Insert New Orders Data Flow task to meet the requirements.
How should you configure the transaction properties? (To answer, select the appropriate setting or settings in the answer area.)
The Insert New Orders Data Flow task must meet the following requirements:
*Usage of the tempdb database should not be impacted.
*Concurrency should be maximized, while only reading committed transactions.
*If the task fails, only that task needs to be rolled back.
You need to configure the Insert New Orders Data Flow task to meet the requirements.
How should you configure the transaction properties? (To answer, select the appropriate setting or settings in the answer area.)
Correct Answer:
IsolationLevel = ReadCommited.
TransactionOption = Required
References:
http://msdn.microsoft.com/en-us/library/ms137690.aspx
http://msdn.microsoft.com/en-us/library/ms137749.aspx
http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.dtscontainer.isolationlevel.aspx
http://msdn.microsoft.com/en-us/library/ms173763.aspx
Question 7
You are using SQL Server Data Tools to develop a SQL Server Integration Services (SSIS) project.
The first package that you create in this project contains a package connection that accesses a Microsoft Excel file. Additional packages in the project must also access this file.
You need to define and reuse the Excel file connection in all project packages.
What should you do?
The first package that you create in this project contains a package connection that accesses a Microsoft Excel file. Additional packages in the project must also access this file.
You need to define and reuse the Excel file connection in all project packages.
What should you do?
Correct Answer: A
Question 8
You administer a Microsoft SQL Server 2012 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.)
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.)
Correct Answer:
Tick the "Unicode" checkbox.
Set the Format option to "Fixed Width"
Untick the "Column names in the first data row" checkbox.
Explanation
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"
Set the Format option to "Fixed Width"
Untick the "Column names in the first data row" checkbox.
Explanation
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 9
You are developing a SQL Server Integration Services (SSIS) package.
The package is stored as the myPackage.dtsx file in the root directory of the C drive of the SSIS server.
You need to run the package from the command prompt.
Which command should you use?
The package is stored as the myPackage.dtsx file in the root directory of the C drive of the SSIS server.
You need to run the package from the command prompt.
Which command should you use?
Correct Answer: D
Question 10
You are developing a SQL Server Integration Services (SSIS) project to read and write data from a Windows Azure SQL Database database to a server that runs SQL Server 2012.
The connection will be used by data flow tasks in multiple SSIS packages. The address of the target Windows Azure SQL Database database will be provided by a project parameter.
You need to create a solution to meet the requirements by using the least amount of administrative effort.
What should you do?
The connection will be used by data flow tasks in multiple SSIS packages. The address of the target Windows Azure SQL Database database will be provided by a project parameter.
You need to create a solution to meet the requirements by using the least amount of administrative effort.
What should you do?
Correct Answer: C
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 11
You are creating a sales data warehouse. When a product exists in the product dimension, you update the product name. When a product does not exist, you insert a new record.
In the current implementation, the DimProduct table must be scanned twice, once for the insert and again for the update. As a result, inserts and updates to the DimProduct table take longer than expected.
You need to create a solution that uses a single command to perform an update and an insert.
How should you use a MERGE T-SQL statement to accomplish this goal? (To answer, drag the appropriate answer choice from the list of options to the correct location or locations in the answer area.
You may need to drag the split bar between panes or scroll to view content.)
In the current implementation, the DimProduct table must be scanned twice, once for the insert and again for the update. As a result, inserts and updates to the DimProduct table take longer than expected.
You need to create a solution that uses a single command to perform an update and an insert.
How should you use a MERGE T-SQL statement to accomplish this goal? (To answer, drag the appropriate answer choice from the list of options to the correct location or locations in the answer area.
You may need to drag the split bar between panes or scroll to view content.)
Correct Answer:
Explanation
References:
http://msdn.microsoft.com/en-us/library/bb510625.aspx
http://msdn.microsoft.com/en-us/library/cc280522
Question 12
You are developing a SQL Server Integration Service (SSIS) package. The package loads a customer dimension table by using a data flow task.
Changes to the customer attributes must be tracked over time.
You need to produce a checksum value to identify the rows that have changed since the last Extract, Transform and Load (ETL) process execution. You need to use the least amount of development effort to achieve this goal.
Which transformation should you use? (To answer, select the appropriate transformation in the answer area.)
Changes to the customer attributes must be tracked over time.
You need to produce a checksum value to identify the rows that have changed since the last Extract, Transform and Load (ETL) process execution. You need to use the least amount of development effort to achieve this goal.
Which transformation should you use? (To answer, select the appropriate transformation in the answer area.)
Correct Answer:
Explanation
Question 13
You are designing an extract, transform, and load (ETL) solution that loads data into dimension tables. The ETL process involves many transformation steps.
You need to ensure that the design can provide:
* Auditing information for compliance and business user acceptance
* Tracking and unique identification of records for troubleshooting and error correction What should you do?
You need to ensure that the design can provide:
* Auditing information for compliance and business user acceptance
* Tracking and unique identification of records for troubleshooting and error correction What should you do?
Correct Answer: B
Question 14
You are completing the installation of the Data Quality Server component of SQL Server Data Quality Services (DQS). You need to complete the post-installation configuration.
What should you do?
What should you do?
Correct Answer: C
Question 15
You are adding a new capability to several dozen SQL Server Integration Services (SSIS) packages.
The new capability is not available as an SSIS task. Each package must be extended with the same new capability.
You need to add the new capability to all the packages without copying the code between packages.
What should you do?
The new capability is not available as an SSIS task. Each package must be extended with the same new capability.
You need to add the new capability to all the packages without copying the code between packages.
What should you do?
Correct Answer: A
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).