1. Home
  2. Microsoft
  3. 70-767

Microsoft 70-767 Online Practice Questions and Exam Preparation

70-767 Exam Details

  • Exam Code
    :70-767
  • Exam Name
    :Implementing a SQL Data Warehouse
  • Certification
    :Microsoft Certifications
  • Vendor
    :Microsoft
  • Total Questions
    :402 Q&As
  • Last Updated
    :Jan 28, 2022

Microsoft 70-767 Online Questions & Answers

  • Question 51:

    HOTSPOT

    You are the data steward at your company.

    Duplicate customers exist in a Microsoft Excel workbook. You create a Data Quality Services (DQS) knowledge base and matching policy to identify these duplicate customers.

    You need to identify the duplicate customers.

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

    Hot Area:

  • Question 52:

    You have a data warehouse named DW1 that contains 20 years of data. DW1 contains a very large fact table. New data is loaded to the fact table monthly.

    Many reports query DW1 for the past year of data.

    Users frequently report that the reports are slow.

    You need to modify the fact table to minimize the amount of time it takes to run the reports. The solution must ensure that other reports can continue to be generated from DW1.

    What should you do?

    A. Move the historical data to SAS disks and move the data from the past year to SSD disks. Run the ALTER TABLE statement.
    B. Move all the data to SSD disks. Load and archive the data by using partition switching.
    C. Move all the data to SAS disks. Load and archive the data by using partition switching.
    D. Move the historical data to SAS disks and move the data for the past year to SSD disks. Create a distributed partitioned view.

  • Question 53:

    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 run the package by using a SQL Server Agent job and use the Job Activity Monitor to view the progress of the job.

    Does the solution meet the goal?

    A. Yes
    B. No

  • Question 54:

    You maintain a SQL Server Integration Services (SSIS) package. The package was developed by using SQL Server 2008 Business Intelligence Development Studio (BIDS).

    The package includes custom scripts that must be upgraded.

    You need to upgrade the package to SQL Server 2016.

    Which tool should you use?

    A. SQL Server dtexec utility (dtexec.exe)
    B. SQL Server DTExecUI utility (dtexecui.exe)
    C. SSIS Upgrade Wizard in SQL Server Data Tools
    D. SQL Server Integration Services Deployment Wizard

  • Question 55:

    HOTSPOT

    You are developing a SQL Server Integration Services (SSIS) package. The data source for the data flow task is a table that has been configured as a change data capture (CDC) table. You are using a CDC Source component to obtain the CDC data.

    The CDC Source component has the following requirements:

    The output must include metadata columns that indicate which source columns have changed.

    The output must return only one change row per source row that is modified in the current CDC processing range.

    You need to configure the CDC Source component.

    Which CDC processing mode should you select? (To answer, configure the appropriate option or options in the dialog box in the answer area.)

    Hot Area:

  • Question 56:

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

    HOTSPOT

    You are designing a SQL Server Integration Services (SSIS) package configuration strategy. The package configuration must meet the following requirements:

    Include multiple properties in a configuration.

    Support several packages with different configuration settings.

    You need to select the appropriate configuration.

    Which configuration type should you use? To answer, select the appropriate option from the drop-down list in the dialog box.

    Hot Area:

  • Question 58:

    You develop a SQL Server Integration Services (SSIS) project by using the Package Deployment Model. A package in the project extracts data from a Windows Azure SQL Database database.

    The package is deployed to SQL Server.

    The package is not producing the desired results.

    You need to generate the .mdmp and .tmp debug files in order to troubleshoot the issues.

    What should you do?

    A. Execute the catalog.add_data_tap stored procedure with the package execution_id.
    B. Execute the catalog.create_execution_dump stored procedure with the package execution_id.
    C. Run the DTEXEC utility with the /DumpOnError option.
    D. Run the DTEXEC utility with the /Reporting V option.

  • Question 59:

    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

    Contoso. Ltd. has a Microsoft SQL Server environment that includes SQL Server Integration Services (SSIS), a data warehouse, and SQL Server Analysis Services (SSAS) Tabular and multidimensional models.

    The data warehouse stores data related to your company sales, financial transactions and financial budgets. All data for the data warenouse originates from the company's business financial system.

    The data warehouse includes the following tables:

    The company plans to use Microsoft Azure to store older records from the data warehouse. You must modify the database to enable the Stretch Database capability.

    Users report that they are becoming confused about which city table to use for various queries. You plan to create a new schema named Dimension and change the name of the dbo.du_city table to Diamension.city. Data loss is not

    permissible, and you must not leave traces of the old table in the data warehouse.

    Pal to create a measure that calculates the profit margin based on the existing measures.

    You must implement a partitioning scheme few the fact. Transaction table to move older data to less expensive storage. Each partition will store data for a single calendar year, as shown in the exhibit (Click the Exhibit button.) You must align

    the partitions.

    You must improve performance for queries against the fact.Transaction table. You must implement appropriate indexes and enable the Stretch Database capability.

    End of repeated scenario

    You need to resolve the problems reported about the dia__city table.

    How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag

    the split bar between panes or scroll to view content.

    Select and Place:

  • Question 60:

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

    The package uses a data flow task to source data from a SQL Server database for loading into a dimension table in a data warehouse.

    You need to create a separate data flow path for data that has been modified since it was last processed.

    Which data flow components should you use to identify modified data? (Each correct answer presents a complete solution. Choose all that apply.)

    A. Multicast
    B. Data Conversion
    C. Lookup
    D. Slowly Changing Dimension
    E. Aggregate

Related Exams:

Tips on How to Prepare for the Exams

Nowadays, the certification exams become more and more important and required by more and more enterprises when applying for a job. But how to prepare for the exam effectively? How to prepare for the exam in a short time with less efforts? How to get a ideal result and how to find the most reliable resources? Here on Vcedump.com, you will find all the answers. Vcedump.com provide not only Microsoft exam questions, answers and explanations but also complete assistance on your exam preparation and certification application. If you are confused on your 70-767 exam preparations and Microsoft certification application, do not hesitate to visit our Vcedump.com to find your solutions here.