Microsoft 70-767 Online Practice Questions and Exam Preparation

Microsoft 70-767 Online Questions & Answers

• Question 371:

  You are developing a Microsoft SQL Server Data Warehouse. You use SQL Server Integration Services (SSIS) packages to import files from a Microsoft Azure blob storage to the data warehouse.

  You plan to use multiple SQL Server instances and SSIS Scale Out to complete the workload faster.

  You must configure three SQL Server instances to run the SSIS package.

  Which two actions should you perform? Each correct answer presents part of the solution.

  NOTE: Each correct selection is worth one point.

  A. Install The SSIS Scale Out Worker feature on two servers. Install the Scale Out Master role feature on one server.
  B. Deploy the SSIS project to the SSIS catalog only on the SQL Server which has the Scale Out Master role installed.
  C. Install the SSIS Scale Out Worker feature on all three servers. Install the Scale Out Master role on one server.
  D. Deploy the SSIS project to the SSIS catalog on all three SQL Servers in the SSIS Scale Out environment.
  A. Install The SSIS Scale Out Worker feature on two servers. Install the Scale Out Master role feature on one server.
  D. Deploy the SSIS project to the SSIS catalog on all three SQL Servers in the SSIS Scale Out environment.

• Question 372:

  You need to recommend a storage solution for a data warehouse that minimizes load times. The solution must provide availability if a hard disk fails.

  Which RAID configuration should you recommend for each type of database file? To answer, drag the appropriate RAID configurations to the correct database file types. Each RAID configuration may be used once, more than once, or not at

  all. You may need to drag the split bar between panes or scroll to view content.

  NOTE: Each correct selection is worth one point.

  Select and Place:

  

  

• Question 373:

  You are the administrator of a database that hosts tables for a data warehouse.

  The table named Fact1 has data from the start of calendar year 2011 through the end of 2017. The table contains at least 20 million rows of data for each year. You create the table by running the following Transact-SQL statement:

  CREATE PARTITION FUNCTION PartitionFunc_Faet1(SMALLINT) AS RANGE LEFT FOR VALUES(2012,2013,2014,2015)

  You need to modify the partition function so that rows for each calendar year are in a separate partition. You must also move all data prior to 2014 to another table named Fact1.old.

  Hot Area:

  

  

• Question 374:

  You have a Microsoft SQL Server Data Warehouse instance that uses SQL Server Analysis Services (SSAS). The instance has a cube containing data from an on-premises SQL Server instance. A measure named Measure1 is configured to calculate the average of a column.

  You plan to change Measure1 to a full additive measure and create a new measure named Measure2 that evaluates data based on the first populated row.

  You need to configure the measures.

  What should you do? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  

  ---

  Box 1: The default setting is SUM (fully additive).

  Box 2: FirstNonEmpty: The member value is evaluated as the value of its first child along the time dimension that contains data.

  References: https://docs.microsoft.com/en-us/sql/analysis-services/multidimensionalmodels/define-semiadditive-behavior

• Question 375:

  You manage the user accounts in master data Services (MDS).

  You need to assign a user access to the MDS data and functions.

  Which two components must you assign? Each correct answer presents part of the solution.

  NOTE: Each correct selection is worth one point.

  A. file share permissions
  B. model object permissions
  C. functional area permissions
  D. SQL Database permissions
  B. model object permissions
  C. functional area permissions

• Question 376:

  Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series.

  Information and details provided in a question apply only to that question.

  You have a database named DB1 that has change data capture enabled.

  A Microsoft SQL Server Integration Services (SSIS) job runs once weekly. The job loads changes from DB1 to a data warehouse by querying the change data capture tables.

  Users report that an application that uses DB1 is suddenly unresponsive.

  You discover that the Integration Services job causes severe blocking issues in the application. You need to ensure that the users can run the application as quickly as possible. Your SQL Server login is a member of only the ssis.admin

  database role.

  Which stored procedure should you execute?

  A. catalog.deploy_project
  B. catalog.restore_project
  C. catalog.stop.operation
  D. sys.sp.cdc.addjob
  E. sys.sp.cdc.changejob
  F. sys.sp_cdc_disable_db
  G. sys.sp_cdc_enable_db
  H. sys.sp_cdc.stopJob
  E. sys.sp.cdc.changejob

  ---

  sys.sp_cdc_change_job modifies the configuration of a change data capture cleanup or capture job in the current database.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-change-job-transact-sql? view=sql-server-2017

• Question 377:

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

  ---

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

  You are using Microsoft SQL Server data Tools (SSID) to create a SQL Server Information Services (SSIS) package. The package contains a single Data Flow task as shown in the Data Flow exhibit. (Click the Data Flow tab.)

  Project properties are provided in the Project Properties exhibit. (Click the Project Properties tab.)

  

  The data flow imports a remote Microsoft Excel file if it exists. You have all of the appropriate permissions to access the file. The package fails to complete. The error message is shown in the Execution Results exhibit. (Click the Execution Results tab.) Execution Results

  

  You need to resolve the issue and ensure that you can run the package on a scheduled basis.

  For each of the following statements, select Yes if the statement is true. otherwise, select No.

  Hot Area:

  

  

• Question 379:

  You have a series of analytic data models and reports that provide insights into the participation rates for sports at different schools. Users enter information about sports and

  participants into a client application. The application stores this transactional data in a Microsoft SQL Server database. A SQL Server Integration Services (SSIS) package loads the data into the models.

  When users enter data, they do not consistently apply the correct names for the sports.

  The following table shows examples of the data entry issues.

  

  You need to improve the quality of the data.

  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.

  Select and Place:

  

  

  ---

  References: https://docs.microsoft.com/en-us/sql/data-quality-services/perform-knowledgediscovery

• Question 380:

  You manage the data warehouse for a large retail company. The company has many store locations. Each location runs their own independent sales systems. They report daily sales and receipt information to a central system every night.

  You plan to load data from all locations into a fact table named fact.Sales by using SQL Server Integration Services (SSIS) packages. You create the following Transact-SQL statement:

  You need to select the columns for the primary key and clustered column key. The keys must meet the following requirements:

  

  Prevent duplicate rows from being entered by a single system.

  Allow for point lookups of a single sale.

  Minimize storage requirements for nonclustered indexes.

  Store rows in the order that they are inserted into the table.

  How should you configure the keys? To answer, select the appropriate column type in the answer area.

  Hot Area: