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

    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 data source will be polled once per hour. The data is updated with multiple important status changes per minute.

    For each captured data change, the before and after values must be included.

    You need to configure the CDC Source component.

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

    Hot Area:

  • Question 242:

    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 Integration Services Deployment Wizard
    B. SQL Server Configuration Manager
    C. SSIS Upgrade Wizard in SQL Server Management Studio
    D. SSIS Upgrade Wizard in SQL Server 2008 BIDS

  • Question 243:

    You are designing an enterprise star schema that will consolidate data from three independent data marts. One of the data marts is hosted on SQL Azure.

    Most of the dimensions have the same structure and content. However, the geography dimension is slightly different in each data mart.

    You need to design a consolidated dimensional structure that will be easy to maintain while ensuring that all dimensional data from the three original solutions is represented.

    What should you do?

    A. Create a conformed dimension for the geography dimension.
    B. Implement change tracking.
    C. Create a degenerate dimension for the geography dimension.
    D. Create a Type 2 slowly changing dimension for the geography dimension.

  • Question 244:

    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 plan to deploy a Microsoft SQL server that will host a data warehouse named DB1.

    The server will contain four SATA drives configured as a RAID 10 array.

    You need to minimize write contention on the transaction log when data is being loaded to the database.

    Solution: You add more data files to DB1.

    Does this meet the goal?

    A. Yes
    B. No

  • Question 245:

    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.

    Hot Area:

  • Question 246:

    You plan to use the dtutil.exe utility with Microsoft SQL Server Integration Services (SSIS) to customize packages. You need to create a new package ID for package1 on Server1. Which dtutil.exe command should you run?

    A. dtutil.exe /FILE c:\repository\packagel.dtsx /DestServer Server! /COPY SQL;package1.dtsx
    B. dtutil.exe /I /FILE c:\repository\packagel.dtsx
    C. dtutil.exe /SQL package1 /COPY OTS;c:\repository\package1.dtsx
    D. dtutil.exe /SQL package1 /DELETE

  • Question 247:

    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 have a database named DB1 that has change data capture enabled.

    A Microsoft SQL Server Integration Services (SSIS) job runs once weekly. The job loads changes from DB1 to a data warehouse by querying the change data capture tables.

    A new version of that integration Services package is released that introduces several errors in the loading process.

    You need to roll back the Integration Services package to the previous version. Which stored procedure should you execute?

    A. catalog.deploy_project
    B. catalog.restore_project
    C. catalog.stop.operation
    D. sys.sp_cdc.addJob
    E. sys.sp.cdc.changejob

  • Question 248:

    You are developing a SQL Server Integration Services (SSIS) package that loads data from a source transactional tables in a data warehouse.

    You discover that one of the data flows loads incorrect data to one of the destination tables.

    You need to identify the cause of the incorrect data flow. Which tool should you use?

    A. SSIS logging.
    B. a breakpoint
    C. a data viewer
    D. the locals window.

  • Question 249:

    You plan to deploy a package to a server that has SQL Server installed. The server contains a SQL Server Integration Services Catalog.

    You need to ensure that the package credentials are encrypted.

    Which protection level should you set for the package?

    A. EncryptSensitivewithUserKey
    B. EncryptAllWithUserKey
    C. ServerStorage
    D. EncryptSensitivewithPassword

  • Question 250:

    DRAG DROP

    You use SQL Server Integration Services (SSIS) for extract, transformation, load (ETL) processing.

    Issues concerning addresses are discovered in the data warehouse that you manage.

    Validation must separate the addresses into three categories:

    Valid addresses

    Autocorrected addresses

    Invalid addresses

    You need to enhance the SSIS packages to perform address validation by using an external service.

    Which transformation should you use? (To answer, drag the appropriate transformation from the list of options to the correct location or locations in the answer area.)

    Select and Place:

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.