Microsoft 70-767 Online Practice Questions and Exam Preparation

Microsoft 70-767 Online Questions & Answers

• Question 71:

  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 a database administrator for an e-commerce company that runs an online store. The company has the databases described in the following table.

  

  Each day, you publish a Microsoft Excel workbook that contains a list of product names and current prices to an external website. Suppliers update pricing information in the workbook. Each supplier saves the workbook with a unique name.

  Each night, the Products table is deleted and refreshed from MDS by using a Microsoft SQL Server Integration Services (SSIS) package. All files must be loaded in sequence.

  You need to add a data flow in an SSIS package to perform the Excel files import in the data warehouse.

  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
  A. Lookup transformation

  ---

  If you're familiar with SSIS and don't want to run the SQL Server Import and Export Wizard, create an SSIS package that uses the Excel Source and the SQL Server Destination in the data flow.

  

  References: https://docs.microsoft.com/en-us/sql/integration-services/import-export- data/import-data-from-excel-to-sql

• Question 72:

  You are designing a partitioning strategy for a large fact table in a data warehouse. Tens of millions of new records are loaded into the data warehouse weekly, outside of business hours.

  Most queries are generated by reports and by cube processing. Data is frequently queried at the day level and occasionally at the month level.

  You need to partition the table to maximize the performance of queries. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

  A. Partition the fact table by month, and compress each partition.
  B. Partition the fact table by week.
  C. Partition the fact table by year.
  D. Partition the fact table by day, and compress each partition.
  D. Partition the fact table by day, and compress each partition.

• Question 73:

  DRAG DROP

  You deploy a server that has SQL Server installed.

  You deploy a SQL Server Integration Services (SSIS) package to the server.

  You need to automate the execution of the package. The solution must ensure that you receive a notification if the package to execute.

  In which order should you perform all the actions?

  Select and Place:

  

  

• Question 74:

  DRAG DROP

  You are developing a SQL Server Integration Services (SSIS) package.

  The package uses custom functionality that accesses a SQL Server database. The custom functionality must be implemented by using Language Integrated Query (LINQ).

  You need to ensure that the LINQ code can be debugged at design time.

  What should you select from the SSIS Toolbox? (To answer, drag the appropriate item to the correct location in the answer area.)

  Select and Place:

  

  ---

  

• Question 75:

  You manage a knowledge base in Data Quality Services (DQS).

  You need to create a domain that inherits all values, rules, and properties from another domain.

  What should you create?

  A. domain rule
  B. linked domain
  C. term-based relations
  D. new knowledge base
  B. linked domain

  ---

  References: https://docs.microsoft.com/en-us/sql/data-quality-services/create-a-linked-domain?view=sql-server-ver15

• Question 76:

  A database has tables named Table1, Table2, and Table3.

  1.

  Table1 has a foreign key relationship with Table2.

  2.

  Table2 has a foreign key relationship with Table3.

  3.

  Table1 does not have a direct relationship with Table3.

  You need to recommend an appropriate dimension usage relationship.

  What should you recommend?

  A. many-to-one relationship
  B. referenced relationship
  C. regular dimension relationship
  D. fact relationship
  B. referenced relationship

  ---

  A reference dimension relationship between a cube dimension and a measure group exists when the key column for the dimension is joined indirectly to the fact table through a key in another dimension table, as shown in the following illustration.

  

  Incorrect Answers:

  C: A regular dimension relationship between a cube dimension and a measure group exists when the key column for the dimension is joined directly to the fact table. References: https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models-olap-logical-cube-objects/dimension-relationships

• Question 77:

  You install a SQL Server 2016 database engine instance on a production server. A month later, you install SQL Server 2016 Integration Services (SSIS).

  You must develop an SSIS project and deploy it to the server by using the Project Deployment model.

  Operations log records that are outside the configured retention period must be cleaned automatically.

  You need to create the SSIS catalog on the production server and ensure that the operations log cleaning requirement is met.

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

  A. Enable CLR Integration.
  B. Enable FILESTREAM with Full Access.
  C. Enable the Resource Governor.
  D. Change the recovery mode of the msdb database to FULL.
  E. Change the Server-wide Default Logging Level in SSISDB to Verbose.
  F. Start the SQL Server Browser service.
  G. Start the SQL Server Agent service.
  A. Enable CLR Integration.
  G. Start the SQL Server Agent service.

• Question 78:

  HOTSPOT

  You are developing a SQL Server Integration Services (SSIS) package. The package contains a user-defined variable named @Queue which has an initial value of 10. The package control flow contains many tasks that must repeat execution until the @Queue variable equals 0.

  You need to enable the tasks to be grouped together for repeat execution.

  Which item should you add to the package? (To answer, select the appropriate item in the answer area.)

  Hot Area:

  

  

• Question 79:

  HOTSPOT

  You are developing a data flow to load sales data into a fact table. In the data flow, you configure a Lookup Transformation in full cache mode to look up the product data for the sale.

  The lookup source for the product data is contained in two tables.

  You need to set the data source for the lookup to be a query that combines the two tables.

  Which page of the Lookup Transformation Editor should you select to configure the query? To answer, select the appropriate page in the answer area.

  Hot Area:

  

  

• Question 80:

  You develop a SQL Server Integration Services (SSIS) package that imports SQL Azure data into a data warehouse every night.

  The SQL Azure data contains many misspellings and variations of abbreviations. To import the data, a developer used the Fuzzy Lookup transformation to choose the closest- matching string from a reference table of allowed values. The

  number of rows in the reference table is very large.

  If no acceptable match is found, the Fuzzy Lookup transformation passes a null value.

  The current setting for the Fuzzy Lookup similarity threshold is 0.50.

  Many values are incorrectly matched.

  You need to ensure that more accurate matches are made by the Fuzzy Lookup transformation without degrading performance.

  What should you do?

  A. Change the Exhaustive property to True.
  B. Change the similarity threshold to 0.55.
  C. Change the similarity threshold to 0.40.
  D. Increase the maximum number of matches per lookup.
  B. Change the similarity threshold to 0.55.

  ---

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