Microsoft 70-463 Online Practice Questions and Exam Preparation

Microsoft 70-463 Online Questions & Answers

• Question 71:

  You are developing a SQL Server Integration Services (SSIS) package. An Execute SQL task in the package checks product stock levels and sets a package variable named InStock to TRUE or FALSE depending on the stock level found.

  After the successful execution of the Execute SQL task, one of two data flow tasks must run, depending on the value of the InStock variable.

  You need to set the precedence constraints.

  Which value for the evaluation operation should you use? (To answer, select the appropriate option for the evaluation operation in the answer area.)

  

  Hot Area:

  

  

• Question 72:

  You install a SQL Server 2012 database engine instance on a production server. A month later, you install SQL Server 2012 Integration Services (SSIS).

  You must develop an SSIS project and deploy it to the server by using the Project Deployment model.

  Operations Log records that are outside the configured retention period must be cleaned automatically.

  You need to create the SSIS catalog on the production server and ensure that the operations log cleaning requirement is met.

  Which two actions should you perform? Each correct answer presents PART OF THE SOLUTION.

  A. Change the recovery mode of the msdb database to FULL.
  B. Start the SQL Server Agent service.
  C. Enable FILESTREAM with Full Access.
  D. Enable the resource Governor.
  E. Enable CLR Integration.
  F. Change the Server-wide Default Logging Level in SSISDB to Verbose.
  G. Start the SQL Server Browser service.
  B. Start the SQL Server Agent service.
  E. Enable CLR Integration.

  ---

  B: SSIS projects, packages, parameters, environments, and operational history are all stored in the SSISDB database so if you have hundreds of SSIS packages or packages that run every few minutes you could see how the database storing all the historical information would grow exponentially.

  Also included when you enable this feature is a SQL Server Agent Job called SSIS Server Maintenance job:

  

  E: To create the SSISDB catalog in SQL Server Management Studio

  1.

  Open SQL Server Management Studio.

  2.

  Connect to the SQL Server Database Engine.

  3.

  In Object Explorer, expand the server node, right-click the Integration Services Catalogs node, and then click Create Catalog.

  4.

  Click Enable CLR Integration. The catalog uses CLR stored procedures. Etc.

  References: http://msdn.microsoft.com/en-us/library/gg471509(v=sql.110).aspx

  https://www.mssqltips.com/sqlservertip/3307/managing-the-size-of-the-sql-server-ssis-catalog-database/

• Question 73:

  You are using the Knowledge Discovery feature of the Data Quality Services (DQS) client application to modify an existing knowledge base. In the mapping configuration, two of the three columns are mapped to existing domains in the

  knowledge base. The third column, named Team Type, does not yet have a domain.

  You need to complete the mapping of the Team Type column.

  What should you do?

  A. Add a column mapping for the Team Type column.
  B. Map a composite domain to the source column.
  C. Create a composite domain that includes the Team Type column.
  D. Add a domain for the Team Type column.
  D. Add a domain for the Team Type column.

  ---

  References: http://msdn.microsoft.com/en-us/library/ff877917.aspx http://msdn.microsoft.com/en-us/library/hh213015.aspx http://msdn.microsoft.com/en-us/library/gg524799.aspx

• Question 74:

  Which of the following T-SQL functions is not very useful for capturing lineage information?

  A. APP_NAME()
  B. USER_NAME()
  C. DEVICE_STATUS()
  D. SUSER_SNAME()
  C. DEVICE_STATUS()

  ---

  References: https://books.google.co.za/books?id=XGezBQAAQBAJandpg=PT253andlpg=PT253anddq=Redirect+a+failed+data+flow+task+to+a+File+System+taskandsource=blandots=-cyhdndqT-andsig=e7IQXhMqy-cHkh8UfPgzlhav_Mandhl=enandsa=Xandved=0ahUKEwjivfX9t5PZAhUpCcAKHQ5eCCgQ6AEIPDAC#v=onepageandq=switch%20content%20from%20a%20nonpartitioned%20table%20to%20a%20partition%20of%20a%20partitioned%20tableandf=false

• Question 75:

  You are building a fact table in a data warehouse.

  The table must have a columnstore index. The table cannot be partitioned.

  You need to design the fact table and load it with data.

  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.)

  Select and Place:

  

  

• Question 76:

  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 running a training exercise for Microsoft SQL Server 2012 junior developers. You are discussing the use of Subscription Views in SQL Server Master Data Services (MDS).

  Which of the following describes the purpose of Subscription Views in SQL Server Master Data Services (MDS)?

  A. To ensure the quality and accuracy of your master data.
  B. To change the location of members in an explicit hierarchy.
  C. To view a list of leaf and consolidated members.
  D. It allows subscribing systems to view your data in the Master Data Services database.
  E. It indicates the version that users or subscribing systems should use.
  D. It allows subscribing systems to view your data in the Master Data Services database.

• Question 77:

  You are developing a SQL Server Integration Services (SSIS) package to load data into a data warehouse.

  

  You need to establish the correct order for loading each table to maximize parallel processing.

  Which order should you use?

  A. 1. DimCurrency, DimScenario, DimAccount in parallel 2. DimOrganization 3. FactFinance
  B. 1. DimCurrency, DimOrganization in parallel 2. DimScenario, DimAccount in parallel 3. FactFinance
  C. 1. DimCurrency, FactFinance in parallel 2. DimOrganization, DimScenario, DimAccount in parallel
  D. 1. FactFinance 2. DimOrganization, DimScenario, DimAccount in parallel 3. DimCurrency
  E. 1. DimCurrency 2. DimOrganization 3. DimScenario, DimAccount in parallel 4. FactFinance
  A. 1. DimCurrency, DimScenario, DimAccount in parallel 2. DimOrganization 3. FactFinance

  ---

  References: http://msdn.microsoft.com/en-us/library/ms139892.aspx http://msdn.microsoft.com/en-us/library/ms141261.aspx

• Question 78:

  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 Script component
  B. an SCD component
  C. an Aggregate component
  D. a Merge component
  D. a Merge component

  ---

  Note: Type of Slowly Changing DimensionsSlowly 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 wizardReplacement 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.

• Question 79:

  You work as a senior database administrator 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 Microsoft SQL Server 2012 administrators. You are currently discussing Multicast transformation.

  Which of the following is TRUE with regards to Multicast transformation? (Choose two.)

  A. It prevents a package from creating logical copies of data.
  B. It allows a package to create logical copies of data.
  C. It distributes its input to one or more outputs.
  D. It distributes its input to a single output.
  B. It allows a package to create logical copies of data.
  C. It distributes its input to one or more outputs.

• Question 80:

  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. Enable logging to the Application Event Log in the package control flow for the OnPostExecute event. After 10 executions, view the Application Event Log.
  B. Enable logging to an XML file in the package control flow for the OnInformation event. After 10 executions, view the Application Event Log.
  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. After 10 executions, in SQL Server Management Studio, view the Execution Performance subsection of the All Executions report for the package.
  D. After 10 executions, in SQL Server Management Studio, view the Execution Performance subsection of the All Executions report for the package.

  ---

  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.

  References: https://docs.microsoft.com/en-us/sql/integration-services/performance/monitor-running-packages-and-other-operations