70-463 Exam
Quick Guide: 70 463 training kit pdf
Exam Code: 70 463 dumps (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Implementing a Data Warehouse with Microsoft SQL Server 2012
Certification Provider: Microsoft
Free Today! Guaranteed Training- Pass 70 463 pdf Exam.
Q121. You are designing a data warehouse that uses SQL Server 2012.
The data warehouse contains a table named factSales that stores product sales. The table has a clustered index on the primary key, four foreign keys to dimension tables, and an aggregate column for sales totals. All key columns use the int data type and the aggregate column uses the money data type.
You need to increase the speed of data retrieval from the factSales table.
Which index type should you add to the table?
A. Clustered
B. Semantic search
C. Nonclustered
D. XML
Answer: C
Q122. 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 flat file. Additional packages in the project must also access this file.
You need to define and reuse the flat file connection in all project packages.
What should you do?
A. Convert the package Connection Manager in the first package to a project Connection Manager.
B. Copy the package Connection Manager and paste it into the second package.
C. Convert the project to the Package Deployment model.
D. Set the ProtectionLevel property of the package Connection Manager to DontSaveSensitive to reuse the flat file connection.
Answer: A
Q123. You are developing a SQL Server Integration Services (SSIS) package.
The package sources data from an HTML web page that lists product stock levels.
You need to implement a data flow task that reads the product stock levels from the HTML web page.
Which data flow source should you use?
A. Use an msi file to deploy the package on the server.
B. Open a command prompt and run the dtutil /copy command.
C. Add an OnError event handler to the SSIS project.
D. Open a command prompt and run the gacutil command.
Answer: B
Q124. You are developing a SQL Server Integration Services (SSIS) package that loads data into a data warehouse hosted on Windows Azure SQL Database.
You must combine two data sources together by using the ProductID column to provide complete details for each record. The data retrieved from each data source is sorted in ascending order by the ProductID column.
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.)
Answer:
Q125. You are developing a SQL Server Integration Services (SSIS) package.
You need to design a package to change a variable value during package execution by using the least amount of development effort.
What should you use?
A. Express on task
B. Data Cleansing transformation
C. Fuzzy Lookup transformation
D. Term Lookup transformation
E. Data Profiling task
Answer: A
Q126. You develop a SQL Server Integration Services (SSIS) package that imports Windows Azure SQL Database data into a data warehouse every night.
The Windows Azure SQL Database data contains many misspellings and variations of abbreviations. To import the data, a developer used the Fuzzy Lookup transformation to choose the closest-matching string from a reference table of allowed values. The number of rows in the reference table is very large.
If no acceptable match is found, the Fuzzy Lookup transformation passes a null value.
The current setting for the Fuzzy Lookup similarity threshold is 0.50.
Many values are incorrectly matched.
You need to ensure that more accurate matches are made by the Fuzzy Lookup transformation without degrading performance.
What should you do?
A. Change the similarity threshold to 0.40.
B. Decrease the maximum number of matches per lookup.
C. Change the similarity threshold to 0.85.
D. Increase the maximum number of matches per lookup.
Answer: C
Explanation: * Similarity threshold Set the similarity threshold at the component level by using the slider. The closer the value is to 1, the closer the resemblance of the lookup value to the source value must be to qualify as a match. Increasing the threshold can improve the speed of matching since fewer candidate records need to be considered.
Incorrect:
* Maximum number of matches to output per lookup
Specify the maximum number of matches the transformation can return for each input row.
The default is 1.
Q127. You are designing a SQL Server Integration Services (SSIS) package. The package moves order-related data to a staging table named Order. Every night the staging data is truncated and then all the recent orders from the online store database are inserted into the staging table.
Your package must meet the following requirements:
. If the truncate operation fails, the package execution must stop and report an error. . If the Data Flow task that moves the data to the staging table fails, the entire refresh operation must be rolled back. . For auditing purposes, a log entry must be entered in a SQL log table after each execution of the Data Flow task.
The TransactionOption property for the package is set to Required.
You need to design the package to meet the requirements.
How should you design the control flow for the package? (To answer, drag the appropriate setting from the list of settings to the correct location or locations in the answer area.)
Answer:
Q128. 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?
A. Audit
B. Merge Join
C. Merge
D. CDC Splitter
Answer: C
Explanation: MERGE with Incremental Loads The change data capture functionality that is new in SQL Server 2008 makes it easier to perform incremental loads reliably to a data warehouse. As an alternative to using parameterized OLE DB Command transformations to perform the inserts and the updates, you can use the MERGE statement to combine both operations.
Q129. You are designing a SQL Server Integration Services (SSIS) package that uses the Fuzzy Lookup transformation.
The reference data to be used in the transformation does not change.
You need to reuse the Fuzzy Lookup match index to increase performance and reduce maintenance.
What should you do?
A. Select the GenerateAndPersistNewIndex option in the Fuzzy Lookup Transformation Editor.
B. Select the GenerateNewIndex option in the Fuzzy Lookup Transformation Editor.
C. Select the DropExistingMatchlndex option in the Fuzzy Lookup Transformation Editor.
D. Execute the sp_FuzzyLookupTableMaintenanceUninstall stored procedure.
E. Execute the sp_FuzzyLookupTableMaintenanceInvoke stored procedure.
Answer: A
Reference: http://msdn.microsoft.com/en-us/library/ms137786.aspx
Q130. 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.
Answer: