Microsoft 70-767 Online Practice Questions and Exam Preparation

Microsoft 70-767 Online Questions & Answers

• Question 171:

  You are completing the installation of the Data Quality Server component of SQL Server Data Quality Services (DQS).

  You need to complete the post-installation configuration.

  What should you do?

  A. Install the Analysis Services OLE DB Provider.
  B. Make the data available for DQS operations.
  C. Run the Data Quality Server Installer.
  D. Install ADOMD.NET.
  C. Run the Data Quality Server Installer.

  ---

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

• Question 172:

  Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while

  others might not have a correct solution.

  After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

  You have an on-premises Microsoft SQL Server instance and a Microsoft Azure SQL Data Warehouse instance. You move data from the on-premises database to the data warehouse once each day by using a SQL Server Integration

  Services (SSIS) package.

  You observe that the package no longer completes within the allotted time.

  You need to determine which tasks are taking a long time to complete.

  Solution: You enable package logging within SSIS.

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

• Question 173:

  You are designing a SQL Server Integration Services (SSIS) package that uses the Fuzzy Lookup transformation.

  The reference data to be used in the transformation does not change.

  You need to reuse the Fuzzy Lookup match index to increase performance and reduce maintenance.

  What should you do?

  A. Select the GenerateAndPersistNewIndex option in the Fuzzy Lookup Transformation Editor.
  B. Select the GenerateNewIndex option in the Fuzzy Lookup Transformation Editor.
  C. Select the DropExistingMatchlndex option in the Fuzzy Lookup Transformation Editor.
  D. Execute the sp_FuzzyLookupTableMaintenanceUninstall stored procedure.
  E. Execute the sp_FuzzyLookupTableMaintenanceInvoke stored procedure.
  A. Select the GenerateAndPersistNewIndex option in the Fuzzy Lookup Transformation Editor.

  ---

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

• Question 174:

  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.
  A. After 10 executions, in SQL Server Management Studio, view the Execution Performance subsection of the All Executions report for the package.

  ---

  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.

• Question 175:

  You are implementing a Microsoft SQL Server data warehouse with a multi-dimensional data model. You are optimizing the data model across multiple cubes.

  You examine the existing data model and observe that each cube contains multiple date dimension tables.

  You need to optimize the data model in and across cubes without changing the overall table structure.

  What should you use?

  A. star schema
  B. snowflake schema
  C. conformed dimension
  D. slowly changing dimension (SCD)
  E. fact table
  F. semi-additive measure
  G. non-additive measure
  H. dimension table reference relationship
  C. conformed dimension

• Question 176:

  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
  D. Recursive

  ---

  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

• Question 177:

  You are designing a SQL Server Integration Services (SSIS) solution. The solution will contain an SSIS project that includes several SSIS packages. Each SSIS package will define the same connection managers and variables. You have the following requirements:

  A. Use a project deployment model. Modify connection manager properties to use project parameters.
  B. Use a package deployment model. Save each SSIS package to a file share that can be accessed from all environments.
  C. Use a package deployment model. Configure connections in an XML configuration file referenced by an environment variable that corresponds to the SQL Server environment of each SSIS package.
  D. Use a project deployment model. Modify connection manager properties to use package parameters.

  A. Use a project deployment model. Modify connection manager properties to use project parameters.

• Question 178:

  You are designing a data warehouse that uses SQL Server 2016. You are preparing to update the contents of a fact table that contains a non-clustered columnstore index.

  You need to run an update statement against the table.

  What should you do first?

  A. Pause the columnstore index.
  B. Change the recovery model of the database to Bulk-logged.
  C. Change the non-clustered columnstore index to be a clustered columnstore index.
  D. Drop the columnstore index.
  D. Drop the columnstore index.

• Question 179:

  HOTSPOT

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

  Hot Area:

  

  

• Question 180:

  You administer a SQL Server Integration Services (SSIS) solution in the SSIS catalog. A SQL Server Agent job is used to execute a package daily with the basic logging level.

  Recently, the package execution failed because of a primary key violation when the package inserted data into the destination table.

  You need to identify all previous times that the package execution failed because of a primary key violation.

  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.
  E. View the All Messages subsection of the All Executions report for the package.