Microsoft 70-767 Online Practice Questions and Exam Preparation

Microsoft 70-767 Online Questions & Answers

• Question 351:

  CORRECT TEXT

  You are designing a package control flow. The package moves sales order data from a SQL Azure transactional database to an on-premise reporting database. The package will run several times a day, while new sales orders are being

  added to the transactional database.

  The current design of the package control flow is shown in the answer area. (Click the Exhibit button.)

  

  The Insert New Orders Data Flow task must meet the following requirements:

  

  Usage of the tempdb database should not be impacted.

  

  Concurrency should be maximized, while only reading committed transactions.

  

  If the task fails, only that task needs to be rolled back.

  You need to configure the Insert New Orders Data Flow task to meet the requirements.

  

  How should you configure the transaction properties? To answer, select the appropriate setting or settings in the answer area.

  Correct Answer. IsolationLevel = ReadCommited. TransactionOption = Required
  IsolationLevel = ReadCommited. TransactionOption = Required

• Question 352:

  You have a database named DB1 that contains millions of rows.

  You plan to perform a weekly audit of the changes to the rows.

  You need to ensure that you can view which rows were modified and the hour that the modification occurred.

  What should you do?

  A. Enable Policy-Based Management
  B. Configure Stretch Database.
  C. Configure an SSIS database.
  D. Enable change data capture.
  D. Enable change data capture.

  ---

  SQL Server 2017 provides two features that track changes to data in a database: change data capture and change tracking.

  Change data capture provides historical change information for a user table by capturing both the fact that DML changes were made and the actual data that was changed. Changes are captured by using an asynchronous process that reads the transaction log and has a low impact on the system.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/track-data-changes-sql-server

• Question 353:

  HOTSPOT

  You are developing a SQL Server Integration Service (SSIS) package. The package loads a customer dimension table by using a data flow task.

  Changes to the customer attributes must be tracked over time.

  You need to produce a checksum value to identify the rows that have changed since the last Extract, Transform and Load (ETL) process execution. You need to use the least amount of development effort to achieve this goal.

  Which transformation should you use? (To answer, select the appropriate transformation in the answer area.)

  Hot Area:

  

  

• Question 354:

  HOTSPOT

  A SQL Server Integration Services (SSIS) package is designed to download data from a financial database hosted in SQL Azure.

  The connection string to the financial database is defined as a project parameter named FinConStr. The parameter value must be stored securely and must be set explicitly every time the package is executed.

  You need to configure the parameter to meet the requirements.

  What should you do? (To answer, configure the appropriate option or options in the dialog box in the answer area.)

  Hot Area:

  

  

• Question 355:

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

  Daily transactions must be loaded into a staging database. All the SSIS tasks will use the CurrentDate variable as the transaction 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. A Script component
  C. A Script task
  D. An Execute SQL task
  A. An Expression task

  ---

  Explanation: Ref: http://msdn.microsoft.com/en-us/library/ms141214.aspx http://blog.sqltechie.com/2016/11/expression-task-in-ssis-2016.html

• Question 356:

  DRAG DROP

  You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse. You are developing the part of the SSIS package that populates the ProjectDates dimension table. The business key of the ProjectDates table is the ProjectName column. The business user has given you the dimensional attribute behavior for each of the four columns in the ProjectDates table:

  ExpectedStartDate - New values should be tracked over time.

  ActualStartDate - New values should not be accepted.

  ExpectedEndDate - New values should replace existing values.

  ActualEndDate - New values should be tracked over time.

  You use the SSIS Slowly Changing Dimension Transformation.

  You must configure the Change Type value for each source column.

  Which settings should you select? (To answer, select the appropriate setting or settings in the answer area. Each Change Type may be used once, more than once, or not at all.)

  Select and Place:

  

  

• Question 357:

  You are designing a data warehouse for a fresh food distribution business that stores sales by individual product. It stores sales targets by product category. Products are classified into subcategories and categories.

  Each product is included in only a single product subcategory, and each subcategory is included in only a single category.

  The data warehouse will be a data source for an Analysis Services cube.

  The data warehouse contains two fact tables:

  A. Create two product tables, dimProduct and dimProductCategory. Connect factSales to dimProduct and factProductTarget to dimProductCategory with foreign key constraints. Direct the cube developer to use key granularity attributes.
  B. Create one product table, dimProduct, which contains product detail, category, and subcategory columns. Connect factSales to dimProduct with a foreign key constraint. Direct the cube developer to use a non-key granularity attribute for factProductTarget.
  C. Create three product tables, dimProduct, dimProductCategory, and dimProductSubcategory, and a fourth bridge table that joins products to their appropriate category and subcategory table records with foreign key constraints. Direct the cube developer to use key granularity attributes.
  D. Create three product tables, dimProduct, dimProductCategory, and dimProductSubcategory. Connect factSales to all three product tables and connect factProductTarget to dimProductCategory with foreign key constraints. Direct the cube developer to use key granularity attributes.

  B. Create one product table, dimProduct, which contains product detail, category, and subcategory columns. Connect factSales to dimProduct with a foreign key constraint. Direct the cube developer to use a non-key granularity attribute for factProductTarget.

• Question 358:

  You are developing a SQL Server Integration Services (SSIS) project with multiple packages to copy data to a Windows Azure SQL Database database.

  An automated process must validate all related Environment references, parameter data types, package references, and referenced assemblies. The automated process must run on a regular schedule.

  You need to establish the automated validation process by using the least amount of administrative effort.

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

• Question 359:

  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 plan to deploy a Microsoft SQL server that will host a data warehouse named DB1.

  The server will contain four SATA drives configured as a RAID 10 array.

  You need to minimize write contention on the transaction log when data is being loaded to the database.

  Solution: You configure the server to automatically delete the transaction logs nightly.

  Does this meet the goal?

  A. Yes
  B. No
  B. No

  ---

  You should place the log file on a separate drive.

  References:

  https://www.red-gate.com/simple-talk/sql/database-administration/optimizing-transaction-log-throughput/

  https://docs.microsoft.com/en-us/sql/relational-databases/policy-based-management/place-data-and-log-files-on-separate-drives?view=sql-server-2017

• Question 360:

  DRAG DROP

  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: