Microsoft 70-767 Online Practice Questions and Exam Preparation

Microsoft 70-767 Online Questions & Answers

• Question 1:

  Your team is creating SQL Server Integration Services (SSIS) packages that have several dependencies. The packages use parameters for configuration purposes.

  Your company's IT policies include the following change control requirements:

  After a package is ready for deployment, your team must hand over the process to junior IT personnel.

  The process must guarantee that when a package has been validated, that same package (and all its dependencies) is deployed to production. The process must be repeatable and reliable and must be executed with the least administrative

  and training effort by junior IT personnel.

  You need to use the most appropriate deployment unit to satisfy the company policies, while minimizing issues such as incorrect version or configuration.

  Which type of deployment unit should you use?

  A. dtsx
  B. SSIS deployment manifest
  C. msi
  D. ispac
  D. ispac

• Question 2:

  You are implementing a SQL Server Integration Services (SSIS) package that loads data hosted in a SQL Azure database into a data warehouse. The source system contains redundant or inconsistent data. When the package finds invalid data, the row containing the invalid data must be omitted but it must also be written to a text file for further analysis.

  You need to establish the best technique to log these invalid rows while keeping the amount of development effort to a minimum.

  What should you do?

  A. Add an OnError event handler to the SSIS project.
  B. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility.
  C. Use an msi file to deploy the package on the server.
  D. Open a command prompt and run the gacutil command.
  E. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server.
  F. Open a command prompt and run the dtutil /copy command.
  G. Create a reusable custom logging component and use it in the SSIS project.
  H. Configure the SSIS solution to use the Project Deployment Model.
  I. Configure the output of a component in the package data flow to use a data tap.
  J. Open a command prompt and run the dtexec /rep /conn command.
  I. Configure the output of a component in the package data flow to use a data tap.

  ---

  http://technet.microsoft.com/en-us/library/hh230989.aspx http://www.rafael-salas.com/2016/01/ssis-2016-quick-peek-to-data-taps.html http://msdn.microsoft.com/en-us/library/ms162820.aspx http://msdn.microsoft.com/en-us/library/ hh231187.aspx http://technet.microsoft.com/en-us/library/ms140223.aspx http://msdn.microsoft.com/en-us/library/jj655339.aspx

• Question 3:

  DRAG DROP

  You need to create and execute an Execute SQL task to mark the batch as failed if either the Create Batch or Load Products task fails.

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

  Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is

  exactly the same in each question in this series.

  You have a Microsoft SQL Server data warehouse instance that supports several client applications.

  The data warehouse includes the following tables: Dimension.SalesTerritory, Dimension.Customer, Dimension.Date, Fact.Ticket, and Fact.Order. The Dimension.SalesTerritory and Dimension.Customer tables are frequently updated. The

  Fact.Order table is optimized for weekly reporting, but the company wants to change it daily. The Fact.Order table is loaded by using an ETL process. Indexes have been added to the table over time, but the presence of these indexes slows

  data loading.

  All data in the data warehouse is stored on a shared SAN. All tables are in a database named DB1. You have a second database named DB2 that contains copies of production data for a development environment. The data warehouse has

  grown and the cost of storage has increased. Data older than one year is accessed infrequently and is considered historical.

  You have the following requirements:

  Implement table partitioning to improve the manageability of the data warehouse and to avoid the need to repopulate all transactional data each night. Use a partitioning strategy that is as granular as possible.

  Partition the Fact.Order table and retain a total of seven years of data.

  Partition the Fact.Ticket table and retain seven years of data. At the end of each month, the partition structure must apply a sliding window strategy to ensure that a new partition is available for the upcoming month, and that the oldest

  month of data is archived and removed.

  Optimize data loading for the Dimension.SalesTerritory, Dimension.Customer, and Dimension.Date tables.

  Incrementally load all tables in the database and ensure that all incremental changes are processed.

  Maximize the performance during the data loading process for the Fact.Order partition.

  Ensure that historical data remains online and available for querying.

  Reduce ongoing storage costs while maintaining query performance for current data.

  You are not permitted to make changes to the client applications.

  You need to optimize data loading for the Dimension.Customer table.

  Which three Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

  NOTE: You will not need all of the Transact-SQL segments

  Select and Place:

  

  

  ---

  Step 1: USE DB1

  From Scenario: All tables are in a database named DB1. You have a second database named DB2 that contains copies of production data for a development environment.

  Step 2: EXEC sys.sp_cdc_enable_db

  Before you can enable a table for change data capture, the database must be enabled. To enable the database, use the sys.sp_cdc_enable_db stored procedure.

  sys.sp_cdc_enable_db has no parameters.

  Step 3: EXEC sys.sp_cdc_enable_table

  @source schema = N 'schema' etc.

  Sys.sp_cdc_enable_table enables change data capture for the specified source table in the current database.

  Partial syntax:

  sys.sp_cdc_enable_table

  [ @source_schema = ] 'source_schema',

  [ @source_name = ] 'source_name' , [,[ @capture_instance = ] 'capture_instance' ]

  [,[ @supports_net_changes = ] supports_net_changes ]

  Etc.

  References:

  https://docs.microsoft.com/en-us/sql/relational-databases/system-storedprocedures/sys-sp-cdc-enable-table-transact-sql

  https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/syssp-cdc-enable-db-transact-sql

• Question 5:

  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 sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

  You have a Microsoft Azure SQL Data Warehouse instance that must be available six months a day for reporting.

  You need to pause the compute resources when the instance is not being used.

  Solution: You use the Azure portal.

  Does the solution meet the goal?

  A. Yes
  B. No
  A. Yes

  ---

  To pause a SQL Data Warehouse database, use any of these individual methods. Pause compute with Azure portal Pause compute with PowerShell Pause compute with REST APIs

  Note: To pause a database:

  1.

  Open the Azure portal and open your database. Notice that the Status is Online.

  2.

  To suspend compute and memory resources, click Pause, and then a confirmation message appears. Click yes to confirm or no to cancel.

  

  References: https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-manage- compute-overview https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-manage- compute-portal#pause-compute-bk

• Question 6:

  You are implementing a Microsoft SQL Server data warehouse with a multi-dimensional data model.

  Business users observe that the value displayed for the profit margin is the average profit margin across all products. The profit margin must be calculated per product.

  You need to modify the existing model without changing the overall structure of the data model.

  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
  G. non-additive measure

• Question 7:

  You are deploying a new SQL Server Integration Services (SSIS) package to several servers. The package must meet the following requirements:

  A. Use the gacutil command.
  B. Use the dtutil /copy command.
  C. Use the Project Deployment Wizard.
  D. Create an OnError event handler.
  E. Create a reusable custom logging component.
  F. Run the package by using the dtexec /rep /conn command.
  G. Run the package by using the dtexec /dumperror /conn command.
  H. Run the package by using the dtexecui.exe utility and the SQL Log provider.
  I. Add a data tap on the output of a component in the package data flow.
  J. Deploy the package by using an msi file.

  B. Use the dtutil /copy command.

• Question 8:

  You have a data warehouse named DW1.

  In DW1, you plan to create a table named Table1 that will be partitioned by hour. Table1 will contain the last three hours of data.

  You plan to implement a sliding window process for inserting data into Table1.

  You need to recommend the minimum number of partitions that must be included in Table1 to support the planned implementation. The solution must minimize the number of transaction log records created during the insert process.

  How many partitions should you recommend?

  A. 3
  B. 5
  C. 9
  D. 24
  B. 5

• Question 9:

  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 sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

  You are developing a Microsoft SQL Server Integration Services (SSIS) projects. The project consists of several packages that load data warehouse tables.

  You need to extend the control flow design for each package to use the following control flow while minimizing development efforts and maintenance:

  

  Solution: You add the control flow to a control flow package part. You add an instance of the control flow package part to each data warehouse load package. Does the solution meet the goal?

  A. Yes
  B. No
  A. Yes

  ---

  A package consists of a control flow and, optionally, one or more data flows. You create the control flow in a package by using the Control Flow tab in SSIS Designer. References: https://docs.microsoft.com/en-us/sql/integration-services/control-flow/control- flow

• Question 10:

  You create a set of Microsoft SQL Server Integration Services (SSIS) packages to support an ETL process.

  You need to deploy the SSIS packages and meet the following requirements:

  Maximize ease of use for backup processes.

  Manage security of packages from a single place.

  Maintain versioning pf packages.

  Implement parameters to assign values to packages.

  Use stored procedures to run SSIS packages.

  Use SQL Server Agent to run packages.

  Solution: You use the Project Deployment model. You save the packages to team Foundation Server.

  Does this meet the goal?

  A. Yes
  B. No
  B. No