70-463 Exam
How to win with 70 463 dumps
Real of exam 70 463 brain dumps materials and bootcamp for Microsoft certification for IT candidates, Real Success Guaranteed with Updated 70 463 training kit pdf pdf dumps vce Materials. 100% PASS Implementing a Data Warehouse with Microsoft SQL Server 2012 exam Today!
Q41. 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?
A. Add CurrentValue and PreviousValue columns to the student dimension.
B. Enable Snapshot Isolation on the data warehouse.
C. Add an IsCurrent column to the student dimension.
D. Add StartDate and EndDate columns to the student dimension.
Answer: D
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
Q42. You are deploying a project to the SQL Server Integration Services (SSIS) catalog.
You need to ensure that the project is deployed to the production environment with the least amount of administrative effort.
What should you do?
A. Create an On Error event handler.
B. Add a data tap on the output of a component in the package data flow.
C. Use the gacutil command.
D. Create a reusable custom logging component.
E. Run the package by using the dtexecui.exe utility and the SQL Log provider.
F. Run the package by using the dtexec /rep /conn command.
G. Use the Integration Services Deployment Wizard.
H. Run the package by using the dtexec /dumperror /conn command.
I. Use the dtutil /copy command.
J. Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration.
K. Deploy the package by using an msi file.
Answer: G
Reference:
http://msdn.microsoft.com/en-us/library/hh479588.aspx
http://msdn.microsoft.com/en-us/library/hh213290.aspx
http://msdn.microsoft.com/en-us/library/hh213373.aspx
Q43. You develop a SQL Server Integration Services (SSIS) package in a project by using the Project Deployment Model. It is regularly executed within a multi-step SQL Server Agent job.
You make changes to the package that should improve performance.
You need to establish if there is a trend in the durations of the next 10 successful executions of the package. You need to use the least amount of administrative effort to achieve this goal.
What should you do?
A. After 10 executions, in SQL Server Management Studio, view the Execution Performance subsection of the All Executions report for the package.
B. Configure the package to send you an email upon completion that includes information about the duration of the package. After 10 executions, view the emails.
C. Enable logging to the Application Event Log in the package control flow for the OnInformation event. After 10 executions, view the Application Event Log.
D. Enable logging to the Application Event Log in the package control flow for the OnPostExecute event. After 10 executions, view the Application Event Log.
Answer: A
Explanation: The All Executions Report displays a summary of all Integration Services executions that have been performed on the server. There can be multiple executions of the sample package. Unlike the Integration Services Dashboard report, you can configure the All Executions report to show executions that have started during a range of dates. The dates can span multiple days, months, or years.
The report displays the following sections of information.
* Filter
Shows the current filter applied to the report, such as the Start time range.
* Execution Information
Shows the start time, end time, and duration for each package execution.You can view a
list of the parameter values that were used with a package execution, such as values that
were passed to a child package using the Execute Package task.
Q44. You are implementing a SQL Server Integration Services (SSIS) package that loads data hosted in a SQL Azure database into a data warehouse.
The source system contains redundant or inconsistent data. When the package finds invalid data, the row containing the invalid data must be omitted but it must also be written to a text file for further analysis.
You need to establish the best technique to log these invalid rows while keeping the amount of development effort to a minimum.
What should you do?
A. Add an OnError event handler to the SSIS project.
B. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility.
C. Use an msi file to deploy the package on the server.
D. Open a command prompt and run the gacutil command.
E. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server.
F. Open a command prompt and run the dtutil /copy command.
G. Create a reusable custom logging component and use it in the SSIS project.
H. Configure the SSIS solution to use the Project Deployment Model.
I. Configure the output of a component in the package data flow to use a data tap.
J. Open a command prompt and run the dtexec /rep /conn command.
K. Open a command prompt and run the dtexec /dumperror /conn command.
Answer: I
Explanation:
References: http://technet.microsoft.com/en-us/library/hh230989.aspx
http://www.rafael-salas.com/2012/01/ssis-2012-quick-peek-to-data-taps.html http://msdn.microsoft.com/en-us/library/ms162820.aspx http://msdn.microsoft.com/en-us/library/hh231187.aspx http://technet.microsoft.com/en-us/library/ms140223.aspx http://msdn.microsoft.com/en-us/library/jj655339.aspx
Q45. You are developing a SQL Server Integration Services (SSIS) package.
To process complex scientific data originating from a SQL Azure database, a custom task component is added to the project. You need to ensure that the custom component is deployed on a test environment
correctly. What should you do?
A. Add an OnError event handler to the SSIS project.
B. Open a command prompt and run the gacutil command.
C. Configure the SSIS solution to use the Project Deployment Model.
D. Open a command prompt and run the dtexec /dumperror /conn command.
E. Configure the output of a component in the package data flow to use a data tap.
F. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility.
G. Open a command prompt and run the dtexec /rep /conn command.
H. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server.
I. Use an msi file to deploy the package on the server.
J. Open a command prompt and run the dtutil /copy command.
K. Create a reusable custom logging component and use it in the SSIS project.
Answer: B
Reference:
http://msdn.microsoft.com/en-us/library/ms403356.aspx
Q46. You are creating a SQL Server Integration Services (SSIS) package that implements a Type 3 Slowly Changing Dimension (SCD).
You need to add a task or component to the package that allows you to implement the SCD logic.
What should you use?
A. a Data Conversion component
B. an Execute SQL task that executes a MERGE statement on the database
C. a Merge component
D. an Expression task
Answer: C
Explanation: Note: Type of Slowly Changing Dimensions Slowly Changing Dimensions are categorized into three types named: Type 1, Type 2, and Type3. The Type 1 SCD does not maintain the history of changing attributes, it overwrites values of the attributes. Type 2 maintains historical values for changing attributes. Type 3 that we do not use much maintains separate columns for changed attributes. SSIS SCD wizard supports both Type 1 and Type 2. Using MERGE instead of SCD wizard Replacement of SCD wizard with MERGE is not a straightforward technique. If the SCD has both Type 1 and Type 2 types attributes, they need to be handled separately.
Q47. pic 2)
You are designing a SQL Server Integration Services (SSIS) package configuration strategy.
The package configuration must meet the following requirements:
. Include multiple properties in a configuration.
. Support several packages with different configuration settings. You need to select the appropriate configuration. Which configuration type should you use?
To answer, select the appropriate option from the drop-down list in the dialog box.
Answer:
Q48. You are designing a SQL Server Integration Services (SSIS) solution that will load multiple Online Transactional Processing (OLTP) data sources into a SQL Server data mart.
You have the following requirements:
. Ensure that the process supports the creation of an exception report that details possible duplicate key values, null ratios within columns, and column-length distributions of values.
. Ensure that users can generate the exception report in an XML format. . Use the minimum development effort.
You need to design the SSIS solution to meet the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Use a Data Profiling task. Use a Data Flow task to extract the XML output of the Data Profiling task into a SQL Server table. Query the table to view the exceptions.
B. Use Data Flow tasks to process the clean data.
C. Use a Data Profiling task. Read the exceptions in Data Profile Viewer.
D. Design a stored procedure that examines data for common dirty data patterns. Use an Execute SQL task.
Answer: C
Q49. 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
Q50. 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 correct answer presents part of the solution. Choose all that apply.)
A. Change the ORDER BY clause on the product source to order by ProductName.
B. Change the Merge Join transformation to a Merge transformation.
C. Set the appropriate SortKeyPosition properties on the data sources.
D. Set the IsSorted property on both data sources.
Answer: C,D