Microsoft 70-767 Online Practice Questions and Exam Preparation

Microsoft 70-767 Online Questions & Answers

• Question 41:

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

  You are creating a SQL Server Integration Services (SSIS) package that implements a Type 3 Slowly Changing Dimension (SCD).

  You need to add a task or component to the package that allows you to implement the SCD logic.

  What should you use?

  A. a Data Conversion component
  B. an Execute SQL task that executes a MERGE statement on the database
  C. a Merge component
  D. an Expression task
  C. a Merge component

  ---

  Note: Type of Slowly Changing Dimensions Slowly Changing Dimensions are categorized into three types named: Type 1, Type 2, and Type3. The Type 1 SCD does not maintain the history of changing attributes, it overwrites values of the

  attributes. Type 2 maintains historical values for changing attributes. Type 3 that we do not use much maintains separate columns for changed attributes. SSIS SCD wizard supports both Type 1 and Type 2.

  Using MERGE instead of SCD wizard Replacement of SCD wizard with MERGE is not a straightforward technique. If the SCD has both Type 1 and Type 2 types attributes, they need to be handled separately.

• Question 43:

  You are designing a SQL Server Integration Services (SSIS) project that uses the Project Deployment Model. The project will contain 10 packages. All the packages must connect to the same data source and destination. You need to define and reuse the connection managers in all the packages by using the least development effort.

  What should you do?

  A. Implement parent package variables.
  B. Copy and paste connection managers from one package to the others.
  C. Implement project connection managers.
  D. Implement package connection managers.
  C. Implement project connection managers.

• Question 44:

  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 with an SSIS catalog to store and manage packages.

  Does this meet the goal?

  A. Yes
  B. No
  A. Yes

• Question 45:

  DRAG DROP

  You are maintaining a SQL Server Integration Services (SSIS) package. The package uses custom functionality that is implemented in Microsoft Visual C#.

  The implementation of the custom functionality changes overtime. The design of the package allows you to deploy new releases of the custom functionality without redeploying the entire package.

  You need to implement and deploy an update to the custom functionality without requiring package redeployment.

  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:

  

  

• Question 46:

  DRAG DROP

  You are creating a SQL Server Integration Services (SSIS) package to populate a fact table from a source table. The fact table and source table are located in a SQL Azure database. The source table has a price field and a tax field. The OLE

  DB source uses the data access mode of Table.

  You have the following requirements:

  The fact table must populate a column named TotalCost that computes the sum of the price and tax columns.

  Before the sum is calculated, any records that have a price of zero must be discarded.

  You need to create the SSIS package in SQL Server Data Tools.

  In what sequence should you order four of the listed components for the data flow task? (To answer, move the appropriate components from the list of components to the answer area and arrange them in the correct order.)

  Select and Place:

  

  

• Question 47:

  You are designing an Extract, Transform and Load (ETL) solution that loads data into dimension tables. The ETL process involves many transformation steps. You need to ensure that the design can provide:

  Auditing information for compliance and business user acceptance Tracking and unique identification of records for troubleshooting and error correction

  What should you do?

  A. Develop a Master Data Services (MDS) solution.
  B. Develop a Data Quality Services (DQS) solution.
  C. Create a version control repository for the ETL solution.
  D. Develop a custom data lineage solution.
  D. Develop a custom data lineage solution.

• Question 48:

  You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse. You add an Execute SQL task to the control flow. The task must execute a simple INSERT statement. The task has the following requirements:

  A. INSERT INTO dbo.Table (variablevalue) VALUES ($StringVar)
  B. INSERT INTO dbo.Table (variablevalue) VALUES (0)
  C. INSERT INTO dbo.Table (variablevalue) VALUES (@0)
  D. INSERT INTO dbo.Table (variablevalue) VALUES (?)

  D. INSERT INTO dbo.Table (variablevalue) VALUES (?)

  ---

  Explanation: References:

  http://msdn.microsoft.com/en-us/library/ms141003.aspx http://msdn.microsoft.com/en-us/library/ms140355.aspx http://msdn.microsoft.com/en-us/library/cc280502.aspx

• Question 49:

  HOTSPOT

  You are a data warehouse developer responsible for developing data cleansing processes.

  Duplicate employees exist in an employee dimension.

  You need to map, discover, and manage domain values based on the employee dimension.

  Which Data Quality Services (DQS) option should you use? (To answer, select the appropriate option in the answer area.)

  Hot Area:

  

  

• Question 50:

  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. Create a time mapping table.
  B. Change the level of granularity in both fact tables to be the same.
  C. Merge the fact tables.
  D. Create a view on the sales table.
  B. Change the level of granularity in both fact tables to be the same.