70-463 Exam
microsoft 70 463? Tips for success
we provide Virtual Microsoft 70 463 dumps exam which are the best for clearing 70 463 exam test, and to get certified by Microsoft Implementing a Data Warehouse with Microsoft SQL Server 2012. The microsoft 70 463 Questions & Answers covers all the knowledge points of the real 70 463 training kit pdf exam. Crack your Microsoft 70 463 pdf Exam with latest dumps, guaranteed!
Q81. You develop a SQL Server Integration Services (SSIS) package that imports SQL Azure data into a data warehouse every night.
The SQL Azure 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. Decrease the maximum number of matches per lookup.
B. Change the similarity threshold to 0.55.
C. Change the Exhaustive property to True.
D. Increase the maximum number of matches per lookup.
Answer: B
Reference: http://msdn.microsoft.com/en-us/library/ms137786.aspx
Q82. You are developing a SQL Server Integration Services (SSIS) package to implement an incremental data load strategy. The package reads rows from a source system and compares them to rows in a destination system. New rows will be inserted and changed rows will be updated.
You have used a Lookup transformation and a Conditional Split transformation. The Lookup transformation joins the source and destination table on the business key, and includes all columns from the destination table in the data flow output. The Conditional Split transformation inspects the destination columns and directs data flow to either insert new records or update existing records.
You need to configure the Lookup transformation to ensure that all records flow to the Conditional Split transformation, regardless of whether the rows match an existing row in the destination table.
Which setting should you select? (To answer, select the appropriate option in the answer area.)
Answer:
Q83. You are using a SQL Server Integration Services (SSIS) project that is stored in the SSIS catalog. An Environment has been defined in the SSIS catalog.
You need to add the Environment to the project.
Which stored procedure should you use?
A. catalog.set_environment_reference_type
B. catalog.set_environment_property
C. catalog.create_environment_reference
D. catalog.create_environment
Answer: C
Q84. 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?
A. DateHired
B. DepartmentID
C. EmployeeID
D. EmployeeNum
E. FirstName
F. JobTitle
G. LastName
H. MiddleName
I. ReportsToID
Answer: C
Q85. You are developing a SQL Server Integration Services (SSIS) project with multiple packages to copy data to a Windows Azure SQL Database database.
An automated process must validate all related Environment references, parameter data types, package references, and referenced assemblies. The automated process must run on a regular schedule.
You need to establish the automated validation process by using the least amount of administrative effort.
What should you do?
A. Use an event handler for OnError for the package.
B. Use an event handler for OnError for each data flow task.
C. Use an event handler for OnTaskFailed for the package.
D. View the job history for the SQL Server Agent job.
E. View the All Messages subsection of the All Executions report for the package.
F. Store the System::SourceID variable in the custom log table.
G. Store the System::ServerExecutionID variable in the custom log table.
H. Store the System::ExecutionInstanceGUID variable in the custom log table.
I. Enable the SSIS log provider for SQL Server for OnError in the package control flow.
J. Enable the SSIS log provider for SQL Server for OnTaskFailed in the package control flow.
K. Deploy the project by using dtutil.exe with the /COPY DTS option.
L. Deploy the project by using dtutil.exe with the /COPY SQL option.
M. Deploy the .ispac file by using the Integration Services Deployment Wizard.
N. Create a SQL Server Agent job to execute the SSISDB.catalog.validate_project stored procedure.
O. Create a SQL Server Agent job to execute the SSISDB.catalog.validate_package stored procedure.
P. Create a SQL Server Agent job to execute the SSISDB.catalog.create_execution and SSISDB.catalog.start_execution stored procedures.
Q. Create a table to store error information. Create an error output on each data flow destination that writes OnError event text to the table.
R. Create a table to store error information. Create an error output on each data flow destination that writes OnTaskFailed event text to the table.
Answer: N
Q86. You are installing SQL Server Data Quality Services (DQS).
You need to give users belonging to a specific Active Directory group access to the Data Quality Server.
Which SQL Server application should you use?
A. Data Quality Client with administrative credentials
B. SQL Server Configuration Manager with local administrative credentials
C. SQL Server Data Tools with local administrative permissions
D. SQL Server Management Studio with administrative credentials
Answer: D
Q87. 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.)
Answer:
Q88. 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?
A. Develop a Master Data Services (MDS) solution.
B. Develop a Data Quality Services (DQS) solution.
C. Create a version control repository for the ETL solution.
D. Develop a custom data lineage solution.
Answer: D
Q89. You are designing a SQL Server Integration Services (SSIS) data flow to load sales transactions from a source system into a data warehouse hosted on Windows Azure SQL Database. One of the columns in the data source is named ProductCode.
Some of the data to be loaded will reference products that need special processing logic in the data flow.
You need to enable separate processing streams for a subset of rows based on the source product code.
Which data flow transformation should you use?
A. Source Assistant
B. Destination Assistant
C. Audit
D. Script Component
Answer: D
Explanation: Script Component Transformation:
The transformation that uses script to extract, transform, or load data.
Note:
* SQL Server Integration Services provides three different types of data flow components: sources, transformations, and destinations. Sources extract data from data stores such as tables and views in relational databases, files, and Analysis Services databases. Transformations modify, summarize, and clean data. Destinations load data into data stores or create in-memory datasets.
Incorrect: Not A, Not B: No Data Transforms called Source Assistant or Destination Assistant Not C: Not related to auditing
Q90. You are developing a SQL Server Integration Services (SSIS) package to load data into a Windows Azure SQL Database database. The package consists of several data flow tasks.
The package has the following auditing requirements:
. If a data flow task fails, a Transact-SQL (T-SQL) script must be executed.
. The T-SQL script must be executed only once per data flow task that fails, regardless of the nature of the error.
You need to ensure that auditing is configured to meet these requirements.
What should you do?
A. Use an event handler for OnError for the package.
B. Use an event handler for OnError for each data flow task.
C. Use an event handler for OnTaskFailed for the package.
D. View the job history for the SQL Server Agent job.
E. View the All Messages subsection of the All Executions report for the package.
F. Store the System::SourceID variable in the custom log table.
G. Store the System::ServerExecutionID variable in the custom log table.
H. Store the System::ExecutionInstanceGUID variable in the custom log table.
I. Enable the SSIS log provider for SQL Server for OnError in the package control flow.
J. Enable the SSIS log provider for SQL Server for OnTaskFailed in the package control flow.
K. Deploy the project by using dtutil.exe with the /COPY DTS option.
L. Deploy the project by using dtutil.exe with the /COPY SQL option.
M. Deploy the .ispac file by using the Integration Services Deployment Wizard.
N. Create a SQL Server Agent job to execute the SSISDB.catalog.va!idate_project stored procedure.
O. Create a SQL Server Agent job to execute the SSISDB.catalog.validate_package stored procedure.
P. Create a SQL Server Agent job to execute the
SSISDB.catalog.create_execution and SSISDB.catalog.start_execution stored procedures.
Q. Create a table to store error information. Create an error output on each data flow
destination that writes OnTaskFailed event text to the table.
Answer: C