[Free] 2019(Nov) EnsurePass Microsoft 70-767 Dumps with VCE and PDF 71-80

Get Full Version of the Exam
http://www.EnsurePass.com/70-767.html

Question No.71

You are designing a data warehouse with two fact tables. The first table contains sales per month and the second table contains orders per day.

Referential integrity must be enforced declaratively.

You need to design a solution that can join a single time dimension to both fact tables. What should you do?

  1. Create a view on the sales table.

  2. Partition the fact tables by day.

  3. Create a surrogate key for the time dimension.

  4. Change the level of granularity in both fact tables to be the same.

Correct Answer: D

Question No.72

You are developing a SQL Server Integration Services (SSIS) project by using the Project Deployment Model. All packages in the project must log custom messages.

You need to produce reports that combine the custom log messages with the system-generated log messages. What should you do?

  1. Use an event handler for OnError for the package.

  2. Use an event handler for OnError for each data flow task.

  3. Use an event handler for OnTaskFailed for the package.

  4. View the job history for the SQL Server Agent job.

  5. View the All Messages subsection of the All Executions report for the package.

  6. Store the System::SourceID variable in the custom log table.

  7. Store the System::ServerExecutionID variable in the custom log table.

  8. Store the System::ExecutionInstanceGUID variable in the custom log table.

  9. Enable the SSIS log provider for SQL Server for OnError in the package control flow.

  10. Enable the SSIS log provider for SQL Server for OnTaskFailed in the package control flow,

  11. Deploy the project by using dtutil.exe with the /COPY DTS option.

  12. Deploy the project by using dtutil.exe with the /COPY SQL option.

  13. Deploy the .ispac file by using the Integration Services Deployment Wizard.

  14. Create a SQL Server Agent job to execute the SSISDB.catalog.validate_project stored procedure.

  15. Create a SQL Server Agent job to execute the SSISDB.catalog.validate_package stored procedure.

  16. Create a SQL Server Agent job to execute the SSISDB.catalog.create_execution and SSISDB.catalog.start_execution stored procedures.

  17. Create a table to store error information. Create an error output on each data flow destination that writes OnError event text to the table.

  18. Create a table to store error information. Create an error output on each data flow destination that writes OnTaskFailed event text to the table.

Correct Answer: G

Question No.73

HOTSPOT

You are the Master Data Services (MDS) administrator at your company.

An existing user must be denied access to a certain hierarchy node for an existing model. You need to configure the user#39;s permissions.

Which user management menu item should you select?

To answer, configure the appropriate option or options in the dialog box in the answer area.

image

Correct Answer:

image

Question No.74

DRAG DROP

You are building a fact table in a data warehouse.

The table must have a columnstore index. The table cannot be partitioned. You need to design the fact table and load it with data.

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.

image

Correct Answer:

image

Question No.75

DRAG DROP

A SQL Server Integration Services (SSIS) project has been deployed to the SSIS catalog. The project includes a project Connection Manager to connect to the data warehouse.

The SSIS catalog includes two Environments:

image

image

Test Production

Each Environment defines a single Environment Variable named ConnectionString of type string. The value of each variable consists of the connection string to the test or production data warehouses.

You need to execute deployed packages by using either of the defined Environments. 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.

image

Correct Answer:

image

Question No.76

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?

  1. Change the Exhaustive property to True.

  2. Decrease the maximum number of matches per lookup.

  3. Change the similarity threshold to 0.85.

  4. Increase the maximum number of matches per lookup.

Correct Answer: C

Question No.77

HOTSPOT

You are developing a SQL Server Integration Services (SSIS) package. An Execute SQL task in the package checks product stock levels and sets a package variable named InStock to TRUE or FALSE depending on the stock level found.

After the successful execution of the Execute SQL task, one of two data flow tasks must run, depending on the value of the InStock variable.

You need to set the precedence constraints.

Which value for the evaluation operation should you use?

To answer, select the appropriate option for the evaluation operation in the answer area.

image

Correct Answer:

image

Question No.78

HOTSPOT

A SQL Server Integration Services (SSIS) package is designed to download data from a financial database hosted in SQL Azure.

The connection string to the financial database is defined as a project parameter named FinConStr. The parameter value must be stored securely and must be set explicitly every time the package is executed.

You need to configure the parameter to meet the requirements. What should you do?

To answer, configure the appropriate option or options in the dialog box in the answer area.

image

Correct Answer:

image

Question No.79

You administer a Microsoft SQL Server 2016 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit.

image

image

Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table. You need to assign the appropriate constraints and table properties to ensure data integrity and visibility. On which column in the Employee table should you create a unique constraint?

  1. DateHired

  2. DepartmentID

  3. EmployeelD

  4. EmployeeNum

  5. FirstName

  6. JobTitle

  7. LastName

  8. MiddleName

  9. ReportsToID

Correct Answer: D

Question No.80

DRAG DROP

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

The package contains several tasks that must repeat until an expression evaluates to FALSE. You need to add and configure a container to enable this design.

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.

image

Correct Answer:

image

Get Full Version of the Exam
70-767 Dumps
70-767 VCE and PDF

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>