Microsoft 70-767 Online Practice Questions and Exam Preparation

Microsoft 70-767 Online Questions & Answers

• Question 341:

  DRAG DROP

  You are building a SQL Server Integration Services (SSIS) package to load data from all files that are automatically copied to a directory each night through an external FTP process.

  You need to load data from all copied files to a destination table in SQL Server.

  Which three steps 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 342:

  You are installing the Data Quality Client on user desktops.

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

  Which components must be present to meet this goal? (Each correct answer presents part of the solution. Choose all that apply.)

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

  ---

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

• Question 343:

  You are the administrator of a server that hosts Data Quality Server for a large retail company.

  The server had a hardware failure during business hours.

  You need to restore the server that hosts Data Quality Server to another server. You have a recent backup of all the required databases.

  What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

  A. Restore the DQS_MAIN, DQS_PROJECTS, and DQS_STAGING_DATA databases to another server as soon as possible.
  B. Execute the DQS_MAIN.internal_core.RestoreDQDatabases stored procedure with the appropriate parameter.
  C. Restore only the DQS_MAIN and DQS_STAGING_DATA databases to another server as soon as possible.
  D. Execute the DQS_MAIN.internal_core.InitServer stored procedure with the appropriate parameter.
  A. Restore the DQS_MAIN, DQS_PROJECTS, and DQS_STAGING_DATA databases to another server as soon as possible.
  B. Execute the DQS_MAIN.internal_core.RestoreDQDatabases stored procedure with the appropriate parameter.

  ---

  Explanation: Steps to restore DQS Databases:

  Restore DQS_MAIN database.

  Restore the DQS_PROJECTS database.

  Restore the DQS_STAGING_DATA database.

  In Object Explorer, right-click the server, and then click New Query. In the Query Editor window, copy the following SQL statements, and replace with the password that you provided during the DQS installation for the

  database master key:

  USE [DQS_MAIN]

  GO

  EXECUTE [internal_core].[RestoreDQDatabases] '' GO

  Press F5 to execute the statements. Check the Results pane to verify that the statements have executed successfully.

  Note:

  * Backup and restore of SQL Server databases are common operations that database administrators perform for preventing loss of data in a case of disaster by recovering data from the backup databases. Data Quality Server is primarily implemented by two SQL Server databases: DQS_MAIN and DQS_PROJECTS. The backup and restore procedures of the Data Quality Services (DQS) databases are similar to any other SQL Server databases.

  Ref: http://msdn.microsoft.com/en-gb/library/hh213068(v=sql.110).aspx

• Question 344:

  You are performance tuning a SQL Server Integration Services (SSIS) package to load sales data from a source system into a data warehouse that is hosted on Windows Azure SQL Database. The package contains a data flow task that has

  10 source-to-destination execution trees.

  Only five of the source-to-destination execution trees are running in parallel.

  You need to ensure that all the execution trees run in parallel.

  What should you do?

  A. Add OnError and OnWarning event handlers.
  B. Create a new project and add the package to the project.
  C. Query the ExecutionLog table.
  D. Add an Execute SQL task to the event handlers.
  A. Add OnError and OnWarning event handlers.

• Question 345:

  You are designing a data warehouse with two fact tables. The first table contains sales per month and the second table contains orders per day.

  Referential integrity must be enforced declaratively.

  You need to design a solution that can join a single time dimension to both fact tables.

  What should you do?

  A. Join the two fact tables.
  B. Merge the fact tables.
  C. Change the level of granularity in both fact tables to be the same.
  D. Partition the fact tables by day.
  C. Change the level of granularity in both fact tables to be the same.

• Question 346:

  You are designing a data warehouse for a software distribution business that stores sales by software title. It stores sales targets by software category. Software titles are classified into subcategories and categories. Each software title is

  included in only a single software 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:

  factSales, used to record daily sales by software title factTarget, used to record the monthly sales targets by software category

  Reports must be developed against the warehouse that reports sales by software title, category and subcategory, and sales targets.

  You need to design the software title dimension. The solution should use as few tables as possible while supporting all the requirements.

  What should you do?

  A. Create three software tables, dimSoftware, dimSoftwareCategory, and dimSoftwareSubcategory and a fourth bridge table that joins software titles to their appropriate category and subcategory table records with foreign key constraints. Direct the cube developer to use key granularity attributes.
  B. Create three software tables, dimSoftware, dimSoftwareCategory, and dimSoftwareSubcategory. Connect factSales to all three tables and connect factTarget to dimSoftwareCategory with foreign key constraints. Direct the cube developer to use key granularity attributes.
  C. Create one table, dimSoftware, which contains Software Detail, Category, and Subcategory columns. Connect factSales to dimSoftware with a foreign key constraint. Direct the cube developer to use a non-key granularity attribute for factTarget.
  D. Create two tables, dimSoftware and dimSoftwareCategory. Connect factSales to dimSoftware and factTarget to dimSoftwareCategory with foreign key constraints. Direct the cube developer to use key granularity attributes.
  C. Create one table, dimSoftware, which contains Software Detail, Category, and Subcategory columns. Connect factSales to dimSoftware with a foreign key constraint. Direct the cube developer to use a non-key granularity attribute for factTarget.

• Question 347:

  DRAG DROP

  You develop a SQL Server Integration Services (SSIS) project by using the Project Deployment model.

  The project contains many packages. It is deployed on a server named Development1. The project will be deployed to several servers that run SQL Server 2016.

  The project accepts one required parameter. The data type of the parameter is a string.

  A SQL Agent job is created that will call the master.dtsx package in the project. A job step is created for the SSIS package.

  The job must pass the value of an SSIS Environment Variable to the project parameter.

  The value of the Environment Variable must be configured differently on each server that runs SQL Server. The value of the Environment Variable must provide the server name to the project parameter.

  You need to configure SSIS on the Development1 server to pass the Environment Variable to the package.

  Which four actions should you perform in sequence by using SQL Server Management Studio? (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 348:

  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 Change Type values should you select? (To answer, drag the appropriate value from the list of values to the correct location or locations in the answer area.)

  Select and Place:

  

  

• Question 349:

  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 are loading data from an OLTP database to a data warehouse.

  The OLTP database includes a table (or sales data and a table for refund data.

  The data warehouse contains a single table for all the sales and refund data.

  Which component should you use to load the data to the data warehouse?

  A. the Slowly Changing Dimension transformation
  B. the Conditional Split transformation
  C. the Merge transformation
  D. the Data Conversion transformation
  E. an Execute SQL task
  F. the Aggregate transformation
  G. the Lookup transformation
  C. the Merge transformation

  ---

  The Merge transformation combines two sorted datasets into a single dataset. The rows from each dataset are inserted into the output based on values in their key columns.

  By including the Merge transformation in a data flow, you can merge data from two data sources, such as tables and files.

  References: https://docs.microsoft.com/en-us/sql/integration-services/data-flow/transformations/merge-transformation? view=sql-server-2017

• Question 350:

  You are developing a Microsoft SQL Server Integration Services (SSIS) package to incrementally load new and changed records from a data source.

  The SSIS package must load new records into Table1 and updated records into Table1_Updates. After loading records, the package must call a Transact-SQL statement to process updated rows according to existing business logic.

  You need to complete the design of the SSIS package.

  Which tasks should you use? To answer, drag the appropriate SSIS objects to the correct targets. Each SSIS object 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:

  

  

  ---

  Step 1: CDC Control Task Get Processing Range

  Step 2: Mark Processed Range

  Step 3: Data Flow

  The Data Flow task encapsulates the data flow engine that moves data between sources and destinations, and lets the user transform, clean, and modify data as it is moved. Addition of a Data Flow task to a package control flow makes it

  possible for the package to extract, transform, and load data.

  Step 4: CDC Source

  The CDC source reads a range of change data from SQL Server 2017 change tables and delivers the changes downstream to other SSIS component.

  Step 5: CDC Splitter

  The CDC splitter splits a single flow of change rows from a CDC source data flow into different data flows for Insert, Update and Delete operations.

  https://docs.microsoft.com/en-us/sql/integration-services/control-flow/cdc-control-task

  https://docs.microsoft.com/en-us/sql/integration-services/control-flow/data-flow-task

  https://docs.microsoft.com/en-us/sql/integration-services/data-flow/cdc-splitter?view=sql-server-2017