Microsoft 70-463 Dumps Questions 2021

NEW QUESTION 1
You are designing a data warehouse with two fact tables. The first table contains sales per month and the second table contains orders per day.
Referential integrity must be enforced declaratively.
You need to design a solution that can join a single time dimension to both fact tables. What should you do?

• A. Create a time mapping table.
• B. Change the level of granularity in both fact tables to be the same.
• C. Merge the fact tables.
• D. Create a view on the sales tabl

Answer: B

NEW QUESTION 2
You are creating a SQL Server Master Data Services (MDS) model for a company.
The source data for the company is stored in a single table that contains the manager-to-subordinate relationships.
You need to create a hierarchy representing the organizational structure of the company. Which hierarchy type should you use?

• A. Natural
• B. Explicit
• C. Parent
• D. Recursive

Answer: D

Explanation: Reference: http://technet.microsoft.com/en-us/library/ff487006.aspx Reference: http://msdn.microsoft.com/en-us/library/ee633747.aspx Reference: http://technet.microsoft.com/en-us/library/ee633759.aspx

NEW QUESTION 3
DRAG DROP
You are building a SQL Server Integration Services (SSIS) package to load data from all files that are automatically copied to a directory each night through an external FTP process.
You need to load data from all copied files to a destination table in SQL Server.
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.)

Answer:

Explanation:
References:
http://msdn.microsoft.com/en-us/library/ms141724.aspx http://msdn.microsoft.com/en-us/library/ms137728.aspx

NEW QUESTION 4
You are installing SQL Server Data Quality Services (DQS).
You need to give specific users access to the Data Quality Server. Which SQL Server application should you use?

• A. SQL Server Configuration Manager
• B. SQL Server Data Tools
• C. SQL Server Management Studio
• D. Data Quality Client

Answer: C

Explanation: Ref: http://msdn.microsoft.com/en-us/library/hh213045.aspx

NEW QUESTION 5
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. Change the Exhaustive property to True.
• B. Change the similarity threshold to 0.55.
• C. Change the similarity threshold to 0.40.
• D. Increase the maximum number of matches per looku

Answer: B

Explanation: http://msdn.microsoft.com/en-us/library/ms137786.aspx

NEW QUESTION 6
You are writing a SQL Server Integration Services (SSIS) package that transfers data from a legacy system.
Data integrity in the legacy system is very poor. Invalid rows are discarded by the package but must be logged to a CSV file for auditing purposes.
You need to establish the best technique to log these invalid rows while minimizing the amount of development effort.
What should you do?

• A. Add a data tap on the output of a component in the package data flow.
• B. Deploy the package by using an msi file.
• C. Run the package by using the dtexecui.exe utility and the SQL Log provider.
• D. uses the dtutil /copy command.
• E. Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration.
• F. Create an OnError event handler.
• G. uses the Project Deployment Wizard.
• H. Use the gacutil command.
• I. Create a reusable custom logging component.
• J. Run the package by using the dtexec /rep /conn command.
• K. Run the package by using the dtexec /dumperror /conn command.

Answer: A

Explanation: Reference:
http://www.rafael-salas.com/2012/01/ssis-2012-quick-peek-to-data-taps.html http://msdn.microsoft.com/en-us/library/hh230989.aspx http://msdn.microsoft.com/en-us/library/jj655339.aspx

NEW QUESTION 7
You are designing a SQL Server Integration Services (SSIS) package that uploads a file to a table named Orders in a SQL Azure database.
The company's auditing policies have the following requirements:
• An entry must be written to a dedicated SQL Server log table named OrderLog.
• The entry must be written as soon as the file upload task completes. You need to meet the company's policy requirements.
Which event handler should you use?

• A. OnPostExecute
• B. OnWarning
• C. OnExecStatusChanged
• D. OnVariableValueChanged

Answer: A

Explanation: Reference: http://msdn.microsoft.com/en-us/library/ms140223.aspx

NEW QUESTION 8
You are installing the Data Quality Server component of Data Quality Services.
You need to provision the hardware and install the software for the server that runs the Data Quality Server.
You must ensure that the minimum Data Quality Server prerequisites are met. What should you do?

• A. Install Microsoft Internet Explorer 6.0 SP1 or later.
• B. Install SQL Server 2012 Database Engine.
• C. Make sure the server has at least 4 GB of RAM.
• D. Install Microsoft SharePoint Server 2010 Standard Edition.

Answer: B

Explanation: Data Quality Server Minimum System Requirements
* SQL Server 2012 Database Engine.
* Memory (RAM): Minimum: 2 GB Recommended: 4 GB or more
Note: SQL Server Data Quality Services (DQS) is a new feature in SQL Server 2012 that contains the following two components: Data Quality Server and Data Quality Client.

NEW QUESTION 9
You are developing a SQL Server Integration Services (SSIS) project that contains a project Connection Manager and multiple packages.
All packages in the project must connect to the same database. The server name for the database must be set by using a parameter named ParamConnection when any package in the project is executed.
You need to develop this project with the least amount of development effort.
What should you do? (Each answer presents a part of the solution. Choose all that apply.)

• A. Create a package parameter named ConnectionName in each package.
• B. Edit each package Connection Manage
• C. Set the ConnectionName property to@[$Project::ParamConnection].
• D. Edit the project Connection Manager in Solution Explore
• E. Set the ConnectionName property to @ [$Project::ParamConnection].
• F. Set the Sensitive property of the parameter to True.
• G. Create a project parameter named ConnectionName.
• H. Set the Required property of the parameter to True.

Answer: CEF

Explanation:
C: From question: "The server name for the database must be set by using a parameter named ParamConnection when any package in the project is executed."
E: SSIS 2012 has introduced the concept of Project level connection managers. An SSIS project is generally more than one package. To simplify lives, the SSIS team now allows for the sharing of common resources across projects, connection managers being one of those resources.
F: When a parameter is marked as required, a server value or execution value must be specified for that parameter. Otherwise, the corresponding package does not execute. Although the parameter has a default value at design time, it will never be used once the project is deployed.
Note:
* Integration Services (SSIS) parameters allow you to assign values to properties within packages at the time of package execution. You can create project parameters at the project level and package parameters at the package level. Project parameters are used to supply any external input the project receives to one or more packages in the project. Package parameters allow you to modify package execution without having to edit and redeploy the package.
Reference: Integration Services (SSIS) Parameters

NEW QUESTION 10
You are developing a SQL Server Integration Services (SSIS) package to load data into a SQL Server table on Server

• A. The package includes a data flow and is executed on Server
• B. The destination table has its own identity column.The destination data load has the following requirements: The identity values from the source table must be use
• C. Default constraints on the destination table must be ignore
• D. Batch size must be 100,000 rows.You need to add a destination and configure it to meet the requirement
• E. Which destination should you use?
• F. OLE DB Destination with Fast Load
• G. SQL Server Destination
• H. ADO NET Destination without Bulk Insert
• I. ADO NET Destination with Bulk Insert
• J. OLE DB Destination without Fast Load

Answer: A

Explanation: Reference: http://msdn.microsoft.com/en-us/library/ms141237.aspx Reference: http://msdn.microsoft.com/en-us/library/ms139821.aspx Reference: http://msdn.microsoft.com/en-us/library/ms141095.aspx

NEW QUESTION 11
You are designing a data warehouse hosted on SQL Azure. The data warehouse currently includes the dimUser and dimDistrict dimension tables and the factSales fact table. The dimUser table contains records for each user permitted to run reports against the warehouse; and the dimDistrict table contains information about sales districts.
The system is accessed by users from certain districts, as well as by area supervisors and users from the corporate headquarters.
You need to design a table structure to ensure that certain users can see sales data for only certain districts. Some users must be permitted to see sales data from multiple districts.
What should you do?

• A. Add a district column to the dimUser table.
• B. Partition the factSales table on the district column.
• C. Create a userDistrict table that contains primary key columns from the dimUser and dimDistrict tables.
• D. For each district, create a view of the factSales table that includes a WHERE clause for the distric

Answer: C

NEW QUESTION 12
You are implementing a SQL Server Integration Services (SSIS) package that imports Microsoft Excel workbook data into a Windows Azure SQL Database database. The package has been deployed to a production server that runs Windows Server 2008 R2 and SQL Server 2012.
The package fails when executed on the production server.
You need to ensure that the package can load the Excel workbook data without errors. You need to use the least amount of administrative effort to achieve this goal.
What should you do?

• A. Create a custom SSIS source component that encapsulates the 32-bit driver and compile it in 64- bit mode.
• B. Install a 64-bit ACE driver and execute the package by using the 64-bit run-time option.
• C. Execute the package by using the 32-bit run-time option.
• D. Replace the SSIS Excel source with a SSIS Flat File source.

Answer: C

Explanation: * See step 3 below.
To publish an Excel worksheet to Azure SQL Database, your package will contain a Data Flow Task, Excel Source task, and ADO NET Destination.
1) Create an SSIS project.
2) Drop a Data Flow Task onto the Control Flow design surface, and double click the Data Flow Task.
3) Drop an Excel Source onto the Data Flow design surface.
Note When using the Excel Source task on a 64-bit machine, set Run64BitRuntime to False.
*I ncorrect:
Not D: The Flat File source reads data from a text file. The text file can be in delimited, fixed width, or mixed format.

NEW QUESTION 13
You are designing a SQL Server Integration Services (SS1S) package that uploads a file to a table named Orders in a SQL Azure database.
The company's auditing policies have the following requirements:
• An entry must be written to a dedicated SQL Server log table named OrderLog.
• The entry must be written as soon as the file upload task completes.
You need to meet the company's policy requirements. Which event handler should you use?

• A. OnProgress
• B. Onlnformation
• C. OnPostExecute
• D. OnComplete

Answer: C

Explanation: Reference: http://msdn.microsoft.com/en-us/library/ms140223.aspx

NEW QUESTION 14
DRAG DROP
A new dedicated server is used to execute resource-intensive SQL Server Integration Services (SSIS) 2012 packages. The environment that you are deploying the packages to has the following constraints:
The operating system is Windows Server 2008 R2. The SSIS packages are stored in the SSIS catalog.
Some of these SSIS packages use 32-bit custom components.
You need to install only the components that are required to deploy and run the packages on the new server.
Which three actions 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.)

Answer:

Explanation: Note: Step 1:
Step 2:
If you require 32-bit versions of the Integration Services runtime and tools to run certain packages in 32-bit mode, you must also install SQL Server Data Tools (SSDT).
Note:
For a complete installation of Integration Services, together with the tools and documentation for developing and managing packages, select both Integration Services and the following Shared Features:
* SQL Server Data Tools to install the tools for designing packages.
* Management Tools - Complete to install SQL Server Management Studio for managing packages.
* Client Tools SDK to install managed assemblies for Integration Services programming.
Step 3: How to Modify the Service Configuration File to Connect to a Named Instance of SQL Server To connect to a named instance of SQL Server, you must modify the service configuration file, MsDtsSrvr.ini.xml.

NEW 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?

• A. Use the Expression task.
• B. Use the Script task.
• C. Develop a custom task.
• D. Use the Script component,
• E. Develop a custom component.

Answer: C

Explanation: References:
http://msdn.microsoft.com/en-us/library/ms135965.aspx http://msdn.microsoft.com/en-us/library/ms345161.aspx

NEW QUESTION 16
DRAG DROP
You are validating whether a SQL Server Integration Services (SSIS) package named Master.dtsx in the SSIS catalog is executing correctly.
You need to display the number of rows in each buffer passed between each data flow component of the package.
Which three actions 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.)

Answer:

Explanation: Ref: http://msdn.microsoft.com/en-us/library/hh230986.aspx

NEW QUESTION 17
You have a secured database that contains all of the customer data for your company. You plan to use a project deployment model.
You need to create a SQL Server Integration Services (SSIS) package that connects to the database by using a dedicated username and password. The solution must ensure that the password used for the connection string is encrypted.
Which two actions should you perform? Each correct answer presents part of the solution.

• A. Modify the set_execution_parameter_value stored procedure.
• B. Set the package protection level to EncryptSensitiveWithPassword.
• C. Select the Sensitive check box for the catalog environment.
• D. Set the sensitive property of the package parameter to True.
• E. Set the package protection level to EncryptSensitiveWithUserKe

Answer: BD

NEW QUESTION 18
HOTSPOT
You are developing a SQL Server Integration Services (SSIS) package.
OnError and OnWarning events must be logged for viewing in the built-in SSIS reports by using SQL Server Management Studio.
You need to execute the package and minimize the number of event types that are logged. Which setting should you use? (To answer, change the appropriate setting in the answer area.)

Answer:

Explanation: Ref: http://msdn.microsoft.com/en-gb/library/hh231191.aspx

NEW QUESTION 19
You need to extract data from delimited text files. What connection manager type would you choose?

• A. A Flat File connection manager
• B. An OLE DB connection manager
• C. An ADO.NET connection manager
• D. A File connection manager

Answer: A

Explanation: References:
http://smallbusiness.chron.com/import-csv-ssis-46849.html

NEW QUESTION 20
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, view the job history for the SQL Server Agent job.
• B. After 10 executions, in SQL Server Management Studio, view the Execution Performance subsection of the All Executions report for the project.
• C. Enable logging to the Application Event Log in the package control flow for the OnInformation even
• D. After 10 executions, view the Application Event Log.
• E. Enable logging to an XML file in the package control flow for the OnPostExecute even
• F. After 10 executions, view the XML file.

Answer: B

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.