[Free] 2017(Oct) EnsurePass Pass4sure Microsoft 70-767 Dumps with VCE and PDF 71-80

Ensurepass.com : Ensure you pass the IT Exams
2017 Oct Microsoft Official New Released 70-767
100% Free Download! 100% Pass Guaranteed!
http://www.EnsurePass.com/70-767.html

Implementing a Data Warehouse using SQL

Question No: 71 – (Topic 2)

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.

Answer: D

Question No: 72 – (Topic 2)

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.

Answer: G

Question No: 73 HOTSPOT – (Topic 2)

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.)

Ensurepass 2017 PDF and VCE

Answer:

Ensurepass 2017 PDF and VCE

Question No: 74 DRAG DROP – (Topic 2)

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.)

Ensurepass 2017 PDF and VCE

Answer:

Ensurepass 2017 PDF and VCE

Question No: 75 DRAG DROP – (Topic 2)

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:

->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.)

Ensurepass 2017 PDF and VCE

Answer:

Ensurepass 2017 PDF and VCE

Explanation:

Box 1:

Ensurepass 2017 PDF and VCE

Box 2:

Ensurepass 2017 PDF and VCE

Box 3:

Ensurepass 2017 PDF and VCE

We need to add references to the Test and Production environments to the project. Then we can map the variables in the project to the environment variables defined in the environments.

When you execute a package in a project that references multiple environments (Test and Production in this case), we can select which environment the package runs under.

Question No: 76 – (Topic 2)

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.

Answer: C

Question No: 77 HOTSPOT – (Topic 2)

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.)

Ensurepass 2017 PDF and VCE

Ensurepass 2017 PDF and VCE

Answer:

Ensurepass 2017 PDF and VCE

Question No: 78 HOTSPOT – (Topic 2)

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.)

Ensurepass 2017 PDF and VCE

Answer:

Ensurepass 2017 PDF and VCE

Question No: 79 – (Topic 2)

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.

Ensurepass 2017 PDF and VCE

Ensurepass 2017 PDF and VCE

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

Answer: D

Question No: 80 DRAG DROP – (Topic 2)

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,)

Ensurepass 2017 PDF and VCE

Answer:

Ensurepass 2017 PDF and VCE

Explanation:

Box 1: Open the Control Flow designer of the package. Box 2: Add and edit a For Loop container.

Box 3: Configure the EvalExpression property.

Note:

* You create the control flow in a package by using the control flow designer.

Integration Services includes three types of containers that you can use in a control flow.

/ Foreach Loop container

/ For Loop container

/ Sequence container

* For Loop Container

The For Loop container defines a repeating control flow in a package. The loop implementation is similar to the For looping structure in programming languages. In each repeat of the loop, the For Loop container evaluates an expression and repeats its workflow until the expression evaluates to False.

The For Loop container uses the following elements to define the loop:

/ An optional initialization expression that assigns values to the loop counters.

/ An evaluation expression that contains the expression used to test whether the loop should stop or continue.

/ An optional iteration expression that increments or decrements the loop counter.

100% Ensurepass Free Download!
Download Free Demo:70-767 Demo PDF
100% Ensurepass Free Guaranteed!
Download 2017 EnsurePass 70-767 Dumps

EnsurePass ExamCollection Testking
Lowest Price Guarantee Yes No No
Up-to-Dated Yes No No
Real Questions Yes No No
Explanation Yes No No
PDF VCE Yes No No
Free VCE Simulator Yes No No
Instant Download Yes No No

2017 EnsurePass IT Certification PDF and VCE

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.