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

    You manage a SQL Server Master Data Services (MDS) environment. A new application requires access to the product data that is available in the MDS repository.

    You need to design a solution that gives the application access to the product data with the least amount of development effort.

    What should you do?

    A. Create a Subscription View in MDS.
    B. Access the product entity tables in the MDS database directly.
    C. Use SQL Server Integration Services (SSIS) to extract the data and put it in a staging database.
    D. Use change data capture on the product entity tables.

  • Question 82:

    You are installing the Data Quality Server component of Data Quality Services.

    You need to provision the hardware and install the software for the server that runs the Data Quality Server.

    You must ensure that the minimum Data Quality Server prerequisites are met.

    What should you do?

    A. Install SQL Server 2016 Database Engine.
    B. Install Microsoft SharePoint Server 2010 Enterprise Edition with PowerPivot.
    C. Make sure the server has at least 4 GB of RAM.
    D. Install Microsoft Internet Explorer 6.0 SP1 or later.

  • Question 83:

    You are reviewing the design of an existing fact table named factSales, which is loaded from a SQL Azure database by a SQL Server Integration Services (SSIS) package each day. The fact table has approximately 1 billion rows and is dimensioned by product, sales date, and sales time of day.

    The database administrator is concerned about the growth of the database. Users report poor reporting performance against this database. Reporting requirements have recently changed and the only remaining report that uses this fact table

    reports sales by product name, sale month, and sale year. No other reports will be created against this table.

    You need to reduce the report processing time and minimize the growth of the database.

    What should you do?

    A. Partition the table by product type.
    B. Create a view over the fact table to aggregate sales by month.
    C. Change the granularity of the fact table to month.
    D. Create an indexed view over the fact table to aggregate sales by month.

  • Question 84:

    You are administering SQL Server Integration Services (SSIS) permissions on a production server that runs SQL Server 2016.

    Package developers in your company must have permission to perform the following tasks only on their own projects:

    View projects and packages, View Environments, Validate packages, Execute packages.

    You need to grant rights to the developers without assigning unnecessary privileges.

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

    A. Add developer logins to the db_ssisltduser role in the msdb database.
    B. Add developer logins to the db_ssisoperator role in the msdb database.
    C. Grant Execute permission in the projects for the developer logins.
    D. Grant Read permission in the SSIS catalog folder, the projects, and the Environments.
    E. Add developer logins to the ssis_admin role in the SSISDB database.
    F. Grant Modify permission in the projects for the developer logins.

  • Question 85:

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

    The package sources data from an HTML web page that lists product stock levels.

    You need to implement a data flow task that reads the product stock levels from the HTML web page.

    Which data flow sources should you use? Select Two

    A. Raw File source
    B. XML source
    C. Custom source component
    D. Flat File source
    E. script component

  • Question 86:

    You deploy a Microsoft Azure SQL Data Warehouse instance. The instance must be available eight hours each day.

    You need to pause Azure resources when they are not in use to reduce costs.

    What will be the impact of pausing resources? To answer, select the appropriate options in the answer area.

    NOTE: Each correct selection is worth one point.

    Hot Area:

  • Question 87:

    You are creating a SQL Server Integration Services (SSIS) package to retrieve product data from two different sources. One source is hosted in a SQL Azure database. Each source contains products for different distributors.

    Products for each distributor source must be combined for insertion into a single product table destination.

    You need to select the appropriate data flow transformation to meet this requirement.

    Which transformation types should you use? (Each answer represents a complete solution. Choose all that apply.)

    A. Slowly Changing Dimension
    B. pivot
    C. Lookup
    D. Union All
    E. Merge

  • Question 88:

    You need to ensure that a downstream system can consume data in a Master Data Services (MDS) system. What should you configure?

    A. a Data Collector
    B. a knowledgebase
    C. a matching policy
    D. a subscription view

  • Question 89:

    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.

    Each night you receive a comma separated values (CSV) file that contains different types of rows. Each row type has a different structure. Each row in the CSV file is unique. The first column in every row is named Type. This column identifies

    the data type.

    For each data type, you need to load data from the CSV file to a target table. A separate table must contain the number of rows loaded for each data type.

    Solution: You create a SQL Server Integration Services (SSIS) package as shown in the exhibit. (Click the Exhibit tab.)

    Does the solution meet the goal?

    A. Yes
    B. No

  • Question 90:

    You have a SQL server integration Services (SSIS) package named package1. You discover that the Data Flow task for Package1 runs slower than expected. You need to reduce the amount of time required to run the Data Flow task. Which two actions should you perform? (Select Two)

    A. Configure the package to run by using the CallerInfo option.
    B. Modify the DefaultBufferSize package setting.
    C. Modify the ForceExecutionResult Package setting.
    D. Modify the DefaultBufferMaxRows package setting.
    E. Configure the package to run by using MaxConcurrent option.

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.