Microsoft 70-767 Online Practice Questions and Exam Preparation

Microsoft 70-767 Online Questions & Answers

• Question 191:

  You have a server that has Data Quality Services (DQS) installed.

  You create a matching policy that contains one matching rule.

  You need to configure the Similarity of Similar percentage that defines a match.

  Which similarity percentage will always generate a similarity score of 0?

  A. 55
  B. 80
  C. 70
  D. 75
  A. 55

  ---

  The minimum similarity between the values of a field is 60%. If the calculated matching score for a field of two records is less than 60, the similarity score is automatically set to 0. References: https://docs.microsoft.com/en-us/sql/data-quality-services/create-a-matching-policy? view=sql-server-2017

• Question 192:

  You are designing a complex SQL Server Integration Services (SSIS) project that uses the Project Deployment model.

  The project will contain between 15 and 20 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. Copy and paste the connection manager details into each package.
  B. Implement project connection managers.
  C. Implement package connection managers.
  D. Implement parent package variables in all packages.
  B. Implement project connection managers.

• Question 193:

  You have a secured database that contains all of the customer data for your company. You plan to use a project deployment model.

  You need to create a SQL Server Integration Services (SSIS) package that connects to the database by using a dedicated username and password. The solution must ensure that the password used for the connection string is encrypted.

  Which two actions should you perform? (Select Two)

  A. Select the Sensitive check box for the catalog environment.
  B. Set the sensitive property of the package parameter to True.
  C. Modify the set_execution parameter_value stored procedure.
  D. Set the package protection level to EncrypSensitiveWithPassword.
  C. Modify the set_execution parameter_value stored procedure.
  D. Set the package protection level to EncrypSensitiveWithPassword.

• Question 194:

  You have Master Data Services (MDS) in a SQL Server deployment. You need to deploy a model that contains data to the MDS repository. What should you use?

  A. the Model Deployment Wizard
  B. the MDSModelDeploy tool
  C. the Master Data Services Configuration Manager tool
  D. the Integration Services Deployment Wizard
  B. the MDSModelDeploy tool

  ---

  Explanation: https://msdn.microsoft.com/en-us/library/ff486956.aspx

• Question 195:

  DRAG DROP

  You are editing a SQL Server Integration Services (SSIS) package that uses checkpoints. The package performs the following steps:

  1. Download a sales transaction file by using FTP.

  2. Truncate a staging table.

  3. Load the contents of the file to the staging table.

  4. Merge the data with another data source for loading to a data warehouse.

  

  The checkpoints are currently working such that if any of the four steps fail, the package will restart from the failed step the next time it executes.

  You need to modify the package to ensure that if either the Truncate Staging Table or the Load Sales to Staging task fails, the package will always restart from the Truncate Staging Table task the next time the package runs.

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

  You are designing a SQL Server Integration Services (SSIS) data flow to load sales transactions from a source system into a data warehouse hosted on Windows Azure SQL Database. One of the columns in the data source is named

  ProductCode.

  Some of the data to be loaded will reference products that need special processing logic in the data flow.

  You need to enable separate processing streams for a subset of rows based on the source product code.

  Which Data Flow transformation should you use?

  A. Script Component
  B. Audit
  C. Destination Assistant
  D. Data Conversion
  A. Script Component

  ---

  Explanation: The Script component can be used as a source, a transformation, or a destination. This component supports one input and multiple outputs. Depending on how the component is used, it supports either an input or outputs or

  both. The script is invoked by every row in the input or output.

  If used as a source, the Script component supports multiple outputs. If used as a transformation, the Script component supports one input and multiple outputs.

  If used as a destination, the Script component supports one input.

• Question 197:

  You are designing a data warehouse hosted on Windows Azure SQL Database. The data warehouse currently includes the dimUser and dimRegion dimension tables and the factSales fact table. The dimUser table contains records for each

  user permitted to run reports against the warehouse, and the dimRegion table contains information about sales regions.

  The system is accessed by users from certain regions, as well as by area supervisors and users from the corporate headquarters.

  You need to design a table structure to ensure that certain users can see sales data for only certain regions. Some users must be permitted to see sales data from multiple regions.

  What should you do?

  A. For each region, create a view of the factSales table that includes a WHERE clause for the region.
  B. Create a userRegion table that contains primary key columns from the dimUser and dimRegion tables.
  C. Add a region column to the dimUser table.
  D. Partition the factSales table on the region column.
  B. Create a userRegion table that contains primary key columns from the dimUser and dimRegion tables.

• Question 198:

  You are developing a SQL Server Integration Services (SSIS) project that contains a project Connection Manager and multiple packages.

  All packages in the project must connect to the same database. The server name for the database must be set by using a parameter named ParamConnection when any package in the project is executed.

  You need to develop this project with the least amount of development effort.

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

  A. Create a package parameter named ConnectionName in each package.
  B. Edit each package Connection Manager. Set the ConnectionName property to @[$Project::ParamConnection].
  C. Edit the project Connection Manager in Solution Explorer. Set the ConnectionName property to @ [$Project::ParamConnection].
  D. Set the Sensitive property of the parameter to True.
  E. Create a project parameter named ConnectionName.
  F. Set the Required property of the parameter to True.
  C. Edit the project Connection Manager in Solution Explorer. Set the ConnectionName property to @ [$Project::ParamConnection].
  E. Create a project parameter named ConnectionName.
  F. Set the Required property of the parameter to True.

  ---

  Explanation: C: From question: " The server name for the database must be set by using a parameter named ParamConnection when any package in the project is executed."

  E: SSIS 2016 has introduced the concept of Project level connection managers. An SSIS project is generally more than one package. To simplify lives, the SSIS team now allows for the sharing of common resources across projects, connection managers being one of those resources.

  F: When a parameter is marked as required, a server value or execution value must be specified for that parameter. Otherwise, the corresponding package does not execute. Although the parameter has a default value at design time, it will never be used once the project is deployed.

  Note:

  * Integration Services (SSIS) parameters allow you to assign values to properties within packages at the time of package execution. You can create project parameters at the project level and package parameters at the package level. Project parameters are used to supply any external input the project receives to one or more packages in the project. Package parameters allow you to modify package execution without having to edit and redeploy the package.

  Reference: Integration Services (SSIS) Parameters

• Question 199:

  You are a database administrator for an e-commerce company that runs an online store. The company has the databases described in the following table.

  

  Each month, eight partner companies send your company comma-separated values (CSV) files that contain their sales data.

  You need to create a Microsoft SQL Server Integration Services (SSIS) package to load the CSV files in parallel to a single table in DB2. You must minimize the number of transformation steps that are required.

  What should you use?

  A. Lookup transformation
  B. Merge transformation
  C. Merge Join transformation
  D. MERGE statement
  E. Union All transformation
  F. Balanced Data Distributor transformation
  G. Sequential container
  H. Foreach Loop container
  F. Balanced Data Distributor transformation

• Question 200:

  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 thescenario is exactly the same in each question in the 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 to 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.

  The following requirements must be met:

  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.SalesTerritory, Dimension.Customer, and Dimension.Date tables.

  Which technology should you use for each table?

  To answer, select the appropriate technologies in the answer area.

  Hot Area:

  

  

  ---

  Box 1: Temporal table

  Box 2: Temporal table

  Compared to CDC, Temporal tables are more efficient in storing historical data as it ignores insert actions.

  Box 3: Change Data Capture (CDC)

  By using change data capture, you can track changes that have occurred over time to your table. This kind of functionality is useful for applications, like a data warehouse load process that need to identify changes, so they can correctly apply

  updates to track historical changes over time.

  CDC is good for maintaining slowly changing dimensions.

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

  The Dimension.SalesTerritory and Dimension.Customer tables are frequently updated.

  References:

  https://www.mssqltips.com/sqlservertip/5212/sql-server-temporal-tables-vs-change-data-capture-vs-change-tracking--part-2/

  https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-table-usage-scenarios?view=sql-server-2017