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

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

Question No.61

DRAG DROP

You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse. You are developing the part of the SSIS package that populates the ProjectDates dimension table.

The business key of the ProjectDates table is the ProjectName column. The business user has given you the dimensional attribute behavior for each of the four columns in the ProjectDates table:

image

image

ExpectedStartDate – New values should be tracked over time. ActualStartDate – New values should not be accepted.

image

image

ExpectedEndDate – New values should replace existing values. ActualEndDate – New values should be tracked over time.

You use the SSIS Slowly Changing Dimension Transformation.

You must configure the Change Type value for each source column. Which settings should you select?

To answer, select the appropriate setting or settings in the answer area. Each Change Type may be used once, more than once, or not at all.

image

Correct Answer:

image

Question No.62

You are the data steward for a Business Intelligence project.

You must identify duplicate rows stored in a SQL Server table and output discoveries to a CSV file. A Data Quality Services (DQS) knowledge base has been created to support this project.

You need to produce the CSV file with the least amount of development effort. What should you do?

  1. Create an Integration Services package and use a Data Profiling transform.

  2. Create a custom .NET application based on the Knowledgebase class.

  3. Create a data quality project.

  4. Create a CLR stored procedure based on the Knowledgebase class.

  5. Create a Master Data Services (MDS) business rule.

Correct Answer: C

Explanation:

http://msdn.microsoft.com/en-us/library/hh213052.aspx

Question No.63

You are creating a SQL Server Master Data Services (MDS) model. This model is used to store a master list of products.

An attribute must be added to the Product entity to define the sales manager responsible for each product.

You need to create an attribute in the Product entity that prevents users from entering invalid sales manager values.

Which type of attribute should you create?

  1. Recursive

  2. Explicit

  3. Domain-based

  4. User-defined

  5. Derived

  6. Parent

Correct Answer: C

Explanation:

http://msdn.microsoft.com/en-us/library/bb190163.aspx http://msdn.microsoft.com/en-us/library/ee633737.aspx http://msdn.microsoft.com/en-us/library/ee633759.aspx http://msdn.microsoft.com/en-us/library/ee633745.aspx http://msdn.microsoft.com/en-us/library/ee633724.aspx http://msdn.microsoft.com/en-us/library/ee633733.aspx http://msdn.microsoft.com/en-us/library/ff487058.aspx

Question No.64

You are developing a SQL Server Integration Services (SSIS) package to load data into a SQL Server table on ServerA. The package includes a data flow and is executed on ServerB. The destination table has its own identity column.

The destination data load has the following requirements:

image

image

image

The identity values from the source table must be used. Default constraints on the destination table must be ignored. Batch size must be 100,000 rows.

You need to add a destination and configure it to meet the requirements. Which destination should you use?

  1. OLE DB Destination with Fast Load

  2. SQL Server Destination

  3. ADO NET Destination without Bulk Insert

  4. ADO NET Destination with Bulk Insert

  5. OLE DB Destination without Fast Load

Correct Answer: A

Explanation:

http://msdn.microsoft.com/en-us/library/ms141237.aspx http://msdn.microsoft.com/en-us/library/ms139821.aspx

http://msdn.microsoft.com/en-us/library/ms141095.aspx

Question No.65

DRAG DROP

You are developing a SQL Server Integration Services (SSIS) package to insert new data into a data mart. The package uses a Lookup transformation to find matches between the source and destination.

The data flow has the following requirements:

image

New rows must be inserted.

image

Lookup failures must be written to a flat file.

In the Lookup transformation, the setting for rows with no matching entries is set to Redirect rows to no match output. You need to configure the package to direct data into the correct destinations.

How should you design the data flow outputs?

To answer, drag the appropriate transformation from the list of answer options to the correct location in the answer area.

image

Correct Answer:

image

Question No.66

You are designing a SQL Server Integration Services (SSIS) package that uses the Fuzzy Lookup transformation.

The reference data to be used in the transformation does not change.

You need to reuse the Fuzzy Lookup match index to increase performance and reduce maintenance.

What should you do?

  1. Select the GenerateAndPersistNewIndex option in the Fuzzy Lookup Transformation Editor.

  2. Select the GenerateNewIndex option in the Fuzzy Lookup Transformation Editor.

  3. Select the DropExistingMatchlndex option in the Fuzzy Lookup Transformation Editor.

  4. Execute the sp_FuzzyLookupTableMaintenanceUninstall stored procedure.

  5. Execute the sp_FuzzyLookupTableMaintenanceInvoke stored procedure.

Correct Answer: A

Explanation:

http://msdn.microsoft.com/en-us/library/ms137786.aspx

Question No.67

You are designing a data warehouse for a software distribution business that stores sales by software title. It stores sales targets by software category. Software titles are classified into subcategories and categories. Each software title is included in only a single software subcategory, and each subcategory is included in only a single category. The data warehouse will be a data source for an Analysis Services cube.

The data warehouse contains two fact tables:

image

factSales, used to record daily sales by software title

image

factTarget, used to record the monthly sales targets by software category

Reports must be developed against the warehouse that reports sales by software title, category and subcategory, and sales targets.

You need to design the software title dimension. The solution should use as few tables as possible while supporting all the requirements.

What should you do?

  1. Create three software tables, dimSoftware, dimSoftwareCategory, and dimSoftwareSubcategory and a fourth bridge table that joins software titles to their appropriate category and subcategory table records with foreign key constraints. Direct the cube developer to use key granularity attributes.

  2. Create three software tables, dimSoftware, dimSoftwareCategory, and dimSoftwareSubcategory. Connect factSales to all three tables and connect factTarget to dimSoftwareCategory with foreign key constraints. Direct the cube developer to use key granularity attributes.

  3. Create one table, dimSoftware, which contains Software Detail, Category, and Subcategory columns. Connect factSales to dimSoftware with a foreign key constraint. Direct the cube developer to use a non-key granularity attribute for factTarget.

  4. Create two tables, dimSoftware and dimSoftwareCategory. Connect factSales to dimSoftware and factTarget to dimSoftwareCategory with foreign key constraints. Direct the cube developer to use key granularity attributes.

Correct Answer: C

Question No.68

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. Change the similarity threshold to 0.55.

  3. Change the similarity threshold to 0.40.

  4. Increase the maximum number of matches per lookup.

Correct Answer: B

Explanation:

http://msdn.microsoft.com/en-us/library/ms137786.aspx

Question No.69

You are completing the installation of the Data Quality Server component of SQL Server Data

Quality Services (DQS).

You need to complete the post-installation configuration. What should you do?

  1. Install the Analysis Services OLE DB Provider.

  2. Make the data available for DQS operations.

  3. Run the Data Quality Server Installer.

  4. Install ADOMD.NET.

Correct Answer: C

Explanation:

http://msdn.microsoft.com/en-us/library/ff877917.aspx http://msdn.microsoft.com/en-us/library/gg492277.aspx

Question No.70

Occasionally a job that executes an existing SQL Server Integration Services (SSIS) package does not complete and nothing is processed.

You need to ensure that package logging occurs. Your solution must minimize deployment and development efforts.

What should you do?

  1. Create a reusable custom logging component.

  2. Use the gacutil command.

  3. Use the Project Deployment Wizard.

  4. Run the package by using the dtexec /rep /conn command.

  5. Add a data tap on the output of a component in the package data flow.

  6. Create an OnError event handler.

  7. Use the dtutil /copy command.

  8. Deploy the package by using an msi file.

  9. Run the package by using the dtexec /dumperror /conn command.

  10. Run the package by using the dtexecui.exe utility and the SQL Log provider.

  11. Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration.

Correct Answer: J

Explanation:

http://msdn.microsoft.com/en-us/library/ms140246.aspx http://msdn.microsoft.com/en-us/library/hh231187.aspx

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>