Microsoft 70-767 Online Practice Questions and Exam Preparation

Microsoft 70-767 Online Questions & Answers

• Question 321:

  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 implementing the data load process for a data warehouse.

  The data warehouse uses daily partitions to store data added or modified during the last 60 days. Older data is stored in monthly partitions.

  You need to ensure that the ETL process can modify the partition scheme during the data load process.

  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
  E. an Execute SQL task

• Question 322:

  You are developing a Microsoft SQL Server Master Data Services (MDS) solution.

  The model contains an entity named Product. The Product entity has three user-defined attributes named Category, Subcategory, and Price, respectively.

  You need to ensure that combinations of values stored in the Category and Subcategory attributes are unique.

  What should you do?

  A. Create an attribute group that consists of the Category and Subcategory attributes. Publish a business rule for the attribute group.
  B. Publish a business rule that will be used by the Product entity.
  C. Create a derived hierarchy based on the Category and Subcategory attributes. Use the Category attribute as the top level for the hierarchy.
  D. Set the value of the Attribute Type property for the Category and Subcategory attributes to Domain-based.
  B. Publish a business rule that will be used by the Product entity.

  ---

  In Master Data Services, business rule actions are the consequence of business rule condition evaluations. If a condition is true, the action is initiated.

  The Validation action "must be unique": The selected attribute must be unique independently or in combination with defined attributes.

  Incorrect Answers:

  A: In Master Data Services, attribute groups help organize attributes in an entity. When an entity has lots of attributes, attribute groups improve the way an entity is displayed in the Master Data Manager web application.

  C: A Master Data Services derived hierarchy is derived from the domain-based attribute relationships that already exist between entities in a model.

  D: In Master Data Services, a domain-based attribute is an attribute with values that are populated by members from another entity. You can think of a domain- based attribute as a constrained list; domain-based attributes prevent users from entering attribute values that are not valid. To select an attribute value, the user must pick from a list.

  References: https://docs.microsoft.com/en-us/sql/master-data-services/business-rule-actions-master-data-services

• Question 323:

  You have a database named DB1. You create a Microsoft SQL Server Integration Services (SSIS) package that incrementally imports data from a table named Customers. The package uses an OLE DB data source for connections to DB1.

  The package defines the following variables.

  

  You need to create a DML statements that updates the LastKeyByTable table.

  How should you complete the Transact-SQL statement?

  To answer, select the appropriate Transact-SQL segments in the dialog box in the answer area.

  Hot Area:

  To support incremental data loading, you create a table by running the following Transact-SQL segment:

  

  

• Question 324:

  You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse hosted on SQL Azure.

  The package uses a Foreach container to process text files found in a folder. The package must be deployed to a single server by using the Project Deployment model.

  Multiple SQL Server Agent jobs call the package. Each job is executed on a different schedule. Each job passes a different folder path to the package. You need to configure the package to accept the folder path from each job.

  Which package configuration should you use?

  A. .dtsConfig file
  B. Registry Entry
  C. Environment Variable
  D. Parent Package Variable
  E. XML Configuration File
  C. Environment Variable

  ---

  Reference: http://msdn.microsoft.com/en-us/library/ms345179.aspx http://msdn.microsoft.com/en-us/library/ms141708.aspx http://msdn.microsoft.com/en-us/library/hh213214.aspx http://msdn.microsoft.com/en-us/library/hh213296.aspx http://msdn.microsoft.com/en-us/library/hh213293.aspx

• Question 325:

  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 the series.

  Start of repeated scenario

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

  1.

  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.

  2.

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

  3.

  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.

  4.

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

  5.

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

  6.

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

  7.

  Ensure that historical data remains online and available for querying.

  8.

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

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

  End of repeated scenario

  You need to implement the data partitioning strategy.

  How should you partition the Fact.Order table?

  A. Create 17,520 partitions.
  B. Use a granularity of one day.
  C. Use a granularity of one month.
  D. Create 1,460 partitions.
  B. Use a granularity of one day.

  ---

  We create on partition for each day, which means that a granularity of one day is used.

  Note: If we calculate the partitions that are needed, we get: 7 years times 365 days is 2,555. Make that 2,557 to provide for leap years.

  From scenario: Partition the Fact.Order table and retain a total of seven years of data. The Fact.Order table is optimized for weekly reporting, but the company wants to change it to daily. Maximize the performance during the data loading process for the Fact.Order partition.

  Reference: https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-partition

• Question 326:

  DRAG DROP

  You need to design a data load strategy for a data warehouse fact table. The solution must use an ordered data load.

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

  You create a Master Data Services (MDS) model that manages the master data for a Product dimension. The Product dimension has the following properties:

  1.

  All the members of the Product dimension have a product type, a product subtype, and a unique product name.

  2.

  Each product has a single product type and a single product subtype.

  3.

  The product type has a one-to-many relationship to the product subtype.

  You need to ensure that the relationship between the product name, the product type, and the product subtype is maintained when products are added to or updates in the database.

  What should you add to the model?

  A. a subscription view
  B. a derived hierarchy
  C. a recursive hierarchy
  D. an explicit hierarchy
  B. a derived hierarchy

  ---

  A Master Data Services derived hierarchy is derived from the domain-based attribute relationships that already exist between entities in a model.

  You can create a derived hierarchy to highlight any of the existing domain-based attribute relationships in the model.

  Incorrect Answers:

  C: In Master Data Services, a recursive hierarchy is a derived hierarchy that includes a recursive relationship. A recursive relationship exists when an entity has a domain-based attribute based on the entity itself.

  D: In Master Data Services, an explicit hierarchy organizes members from a single entity in any way you specify. The structure can be ragged and unlike derived hierarchies, explicit hierarchies are not based on domain-based attribute relationships.

  References: https://docs.microsoft.com/en-us/sql/master-data-services/derived-hierarchies-master-data-services

• Question 328:

  You are the data steward for a Business Intelligence project.

  You must identify duplicate rows stored in a SQL Server table and output discoveries to a CSV file. A Data Quality Services (DQS) knowledge base has been created to support this project.

  You need to produce the CSV file with the least amount of development effort.

  What should you do?

  A. Create an Integration Services package and use a Data Profiling transform.
  B. Create a custom .NET application based on the Knowledgebase class.
  C. Create a data quality project.
  D. Create a CLR stored procedure based on the Knowledgebase class.
  E. Create a Master Data Services (MDS) business rule.
  C. Create a data quality project.

  ---

  Reference: http://msdn.microsoft.com/en-us/library/hh213052.aspx

• Question 329:

  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 the series.

  Start of repeated scenario

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

  1.

  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.

  2.

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

  3.

  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.

  4.

  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.

  5.

  Ensure that historical data remains online and available for querying.

  6.

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

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

  End of repeated scenario

  You need to implement the data partitioning strategy.

  How should you partition the Fact.Order table?

  A. Create 17,520 partitions.
  B. Create 2,557 partitions.
  C. Use a granularity of one month.
  D. Create 1,460 partitions.
  B. Create 2,557 partitions.

  ---

  We create on partition for each day, which means that a granularity of one day is used.

  Note: If we calculate the partitions that are needed, we get: 7 years times 365 days is 2,555. Make that 2,557 to provide for leap years. From scenario: Partition the Fact.Order table and retain a total of seven years of data. The Fact.Order table is optimized for weekly reporting, but the company wants to change it to daily. Maximize the performance during the data loading process for the Fact.Order partition. Reference: https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-partition

• Question 330:

  You have a database Ihat includes a table named dbo.sales. The table contains two billion rows. You created the table by running the following Transact-SQL statement:

  

  You run the following queries against the dbo.sales table. All of the queries perform poorly.

  

  The ETL process that populates the table uses bulk insert to load 10 million rows each day. The process currently takes six hours to load the records.

  The value of the Refund column is equal to 1 for only 0.01 percent of the rows in the table. For all other rows, the value of the Refund column is equal to 0.

  You need to maximize the performance of queries and the ETL process.

  Which index type should you use for each query? To answer, select the appropriate index types in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area: