Microsoft 70-463 Online Practice Questions and Exam Preparation

Microsoft 70-463 Online Questions & Answers

• Question 1:

  You have a SQL Server data warehouse that contains all of the customer data for your company.

  You plan to deploy an archiving solution.

  You need to move a table named OLDCUSTOMERS to a new storage device. OLDCUSTOMERS contains a clustered index.

  Which four 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.

  NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.

  Select and Place:

  

  

• Question 2:

  You are installing the Data Quality Client on user desktops.

  You need to ensure that the prerequisite software components are installed.

  Which two components must be present to meet this goal? Each correct answer presents part of the solution.

  A. Internet Explorer 6.0 SP1 or later
  B. Microsoft Silverlight 4
  C. SQL Server Management Studio
  D. SQL Server Data Tools
  E. .NET Framework 4.0
  F. Microsoft Silverlight 5
  G. NET Framework 3.5 SP1
  A. Internet Explorer 6.0 SP1 or later
  E. .NET Framework 4.0

  ---

  Before installing DQS, make sure that your computer meets the minimum system requirements. Data Quality Client requirement:

  

  .NET Framework 4.0 (installed during the Data Quality Client installation, if not already installed)

  

  Internet Explorer 6.0 SP1 or later

  References: https://msdn.microsoft.com/en-us/library/gg492277.aspx#PreInstallationTasks

• Question 3:

  You are implementing a new SQL Server Integration Services (SSIS) package that loads data from various flat files and a Microsoft Azure SQL Database database.

  Daily transactions must be loaded into a staging database. All the SSIS tasks will use the CurrentDate variable date.

  You need to set the CurrentDate variable to the current execution date when the package starts. You need to achieve this goal by using the least amount of development effort.

  What should you use to set the variable?

  A. an Expression task
  B. an Execute SQL task
  C. a Script component
  D. a Script task
  A. an Expression task

  ---

  Property expression is an expression that is assigned to a property to enable dynamic update of the property at run time. For example, a property expression can update the To line that a Send Mail task uses by inserting an e-mail address

  that is stored in a variable.

  An expression can be added to a package, task, Foreach Loop, For Loop, Sequence, Foreach enumerator, event handler, a package or project level connection manager, or log provider. Any property of these objects that is read/write can

  implement a property expression. Integration Services also supports the use of property expressions in some custom properties of data flow components. Variables and precedence constraints do not support property expressions, but they

  include special properties in which you can use expressions.

  Property expressions can be updated in different ways:

  User-defined variables can be included in package configurations and then updated when the package is deployed. At run time, the property expression is evaluated using the updated variable value. System variables that are included in

  expressions are updated at run time, which changes the results of the property evaluation.

  Date and time functions are evaluated at run time and provide the updated values to property expressions.

  Variables in expressions can be updated by the scripts that the Script task and the Script component run.

  References:

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

  http://blog.sqltechie.com/2012/11/expression-task-in-ssis-2012.html

• Question 4:

  You are developing a SQL Server Integration Services (SSIS) package.

  The package uses custom functionality that accesses a SQL Server database. The custom functionality must be implemented by using Language Integrated Query (LINQ).

  You need to ensure that the LINQ code can be debugged at design time.

  What should you select from the SSIS Toolbox? (To answer, drag the appropriate item to the correct location in the answer area.)

  Select and Place:

  

  

• Question 5:

  Your company is evaluating the data cleansing capabilities of SQL Server Data Quality Services (DQS).

  You have the following requirements:

  You must use a stand-alone server to host DQS and the services necessary to run DQS. You must minimize the number of steps required to install DQS components.

  You must grant permissions to a login named DataStewards to enable DataStewards to edit and run DQS projects.

  You need to implement DQS.

  Which for actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area arrange them in the correct order.

  Select and Place:

  

  

• Question 6:

  You work as a database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment.

  You are in the process of developing a SQL Server Master Data Services (MDS) model. ABC.com's source data is hosted by a single table. The table includes the manager-to-subordinate relationships.

  You now have to design a hierarchy that suits ABC.com's organizational structure.

  Which of the following actions should you take?

  A. You should consider making use of a Recursive hierarchy type.
  B. You should consider making use of a Non-Mandatory Explicit hierarchy.
  C. You should consider making use of a Mandatory Explicit Hierarchy.
  D. You should consider making use of a Derived Hierarchy with Explicit Caps.
  A. You should consider making use of a Recursive hierarchy type.

• Question 7:

  You are a database developer of a Microsoft SQL Server 2012 database. You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID. A sample of this data is as shown in the following table. You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to ensure that the data in the table is in the order of SourceID and then CustomerID.

  Which Transact- SQL statement should you use?

  A. CREATE TABLE Customer(SourceID int NOT NULL IDENTITY,CustomerID int NOT NULL IDENTITY,CustomerName varchar(255) NOT NULL);
  B. CREATE TABLE Customer(SourceID int NOT NULL,CustomerID int NOT NULL PRIMARY KEY CLUSTERED,CustomerName varchar(255) NOT NULL);
  C. CREATE TABLE Customer(SourceID int NOT NULL PRIMARY KEY CLUSTERED,CustomerID int NOT NULL UNIQUE,CustomerName varchar(255) NOT NULL);
  D. CREATE TABLE Customer(SourceID int NOT NULL,CustomerID int NOT NULL,CustomerName varchar(255) NOT NULL,CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED(SourceID, CustomerID));
  D. CREATE TABLE Customer(SourceID int NOT NULL,CustomerID int NOT NULL,CustomerName varchar(255) NOT NULL,CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED(SourceID, CustomerID));

• Question 8:

  You work as a senior database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment.

  You are hosting a training exercise for ABC.com's junior database developers. You are currently discussing an EventHandlerClass property that is used to retrieve or configure a value that indicates whether the container participates in

  transactions.

  Which of the following is the EventHandlerClass property that you are discussing?

  A. The TransactionOption property.
  B. The Executables property.
  C. The LoggingOptions property.
  D. The Variables property.
  A. The TransactionOption property.

• Question 9:

  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.
  A. Add a data tap on the output of a component in the package data flow.

  ---

  References: 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

• Question 10:

  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. 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.
  C. Change the similarity threshold to 0.85.