EnsurePass
2018 Mar Microsoft Official New Released 70-463
100% Free Download! 100% Pass Guaranteed!
http://www.EnsurePass.com/70-463.html

Implementing a Data Warehouse with Microsoft SQL Server 2012

Question No: 131 – (Topic 2)

You are developing a SQL Server Integration Services (SSIS) package to load data into a Windows Azure SQL Database database. The package consists of several data flow tasks.

The package has the following auditing requirements:

->If a data flow task fails, a Transact-SQL (T-SQL) script must be executed.

->The T-SQL script must be executed only once per data flow task that fails, regardless of the nature of the error.

You need to ensure that auditing is configured to meet these requirements. 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.va!idate_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 OnTaskFailed event text to the table.

Answer: C

Question No: 132 – (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. Decrease the maximum number of matches per lookup.

  2. Change the similarity threshold to 0.55.

  3. Change the Exhaustive property to True.

  4. Increase the maximum number of matches per lookup.

Answer: B

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

Question No: 133 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 2018 PDF and VCE

Answer:

Ensurepass 2018 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.

Question No: 134 DRAG DROP – (Topic 2)

You are the administrator for a Data Quality Server. You are adding a user who must have permission to:

->Edit and execute a project

->View the activity monitoring data

This user must not be able to:

->Perform any kind of knowledge management

->Create or change a knowledge base

->Terminate an activity or perform administrative duties

You need to develop a Transact-SQL (T-SQL) script to meet these requirements.

What should you do? (To answer, drag the appropriate code segment or segments to the correct location or locations in the answer area.)

Ensurepass 2018 PDF and VCE

Answer:

Ensurepass 2018 PDF and VCE

Question No: 135 – (Topic 2)

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

Which tool should you use?

  1. SQL Server Configuration Manager

  2. SQL Server Agent

  3. SSIS Upgrade Wizard in SQL Server Management Studio

  4. SQL Server DTExecUI utility (dtexecui.exe)

Answer: C Explanation:

Use the SSIS Package Upgrade Wizard to migrate packages that were developed in SQL

Server 2005 Integration Services (SSIS) and SQL Server 2008 Integration Services (SSIS) to the package format that the current release of Integration Services uses.

Reference: SSIS Upgrade Wizard (SSIS Package Upgrade Wizard)

Question No: 136 – (Topic 2)

You administer a SQL Server Integration Services (SSIS) solution in the SSIS catalog. A SQL Server Agent job is used to execute a package daily with the basic logging level.

Recently, the package execution failed because of a primary key violation when the package inserted data into the destination table.

You need to identify all previous times that the package execution failed because of a primary key violation.

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

Question No: 137 – (Topic 2)

You are developing a SQL Server Integration Services (SSIS) project with multiple packages to copy data to a Windows Azure SQL Database database.

An automated process must validate all related Environment references, parameter data types, package references, and referenced assemblies. The automated process must run on a regular schedule.

You need to establish the automated validation process by using the least amount of administrative effort.

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

Question No: 138 – (Topic 2)

You are designing a data warehouse for a fresh food distribution business that stores sales by individual product. It stores sales targets by product category. Products are classified into subcategories and categories.

Each product is included in only a single product 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:

鈥actSales, used to record daily sales by product

鈥actProductTarget, used to record the monthly sales targets by product category

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

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

What should you do?

  1. Create two product tables, dimProduct and dimProductCategory. Connect

    factSales to dimProduct and factProductTarget to dimProductCategory with foreign key constraints. Direct the cube developer to use key granularity attributes.

  2. Create one product table, dimProduct, which contains product detail, category, and subcategory columns. Connect factSales to dimProduct with a foreign key constraint. Direct the cube developer to use a non-key granularity attribute for factProductTarget.

  3. Create three product tables, dimProduct, dimProductCategory, and dimProductSubcategory, and a fourth bridge table that joins products to their appropriate category and subcategory table records with foreign key constraints. Direct the cube developer to use key granularity attributes.

  4. Create three product tables, dimProduct, dimProductCategory, and dimProductSubcategory. Connect factSales to all three product tables and connect factProductTarget to dimProductCategory with foreign key constraints. Direct the cube

developer to use key granularity attributes.

Answer: B

Question No: 139 HOTSPOT – (Topic 2)

You are designing a SQL Server Integration Services (SSIS) package configuration strategy.

The package configuration must meet the following requirements:

鈥nclude multiple properties in a configuration.

鈥upport several packages with different configuration settings.

You need to select the appropriate configuration. Which configuration type should you use? To answer, select the appropriate option from the drop-down list in the dialog box.

Ensurepass 2018 PDF and VCE

Answer:

Ensurepass 2018 PDF and VCE

Question No: 140 – (Topic 2)

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

Ensurepass 2018 PDF and VCE

The database administrator is concerned about the rapid growth of the database and users experience 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?

  1. Create an indexed view over the fact table to sum orderTotal by month.

  2. Create a view over the fact table to sum orderTotal by month.

  3. Change the granularity of the fact table to month.

  4. Partition the fact table by productKey.

Answer: C

Topic 3, Volume C

100% Free Download!
Download Free Demo:70-463 Demo PDF
100% Pass Guaranteed!
Download 2018 EnsurePass 70-463 Full Exam PDF and VCE

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

2018 EnsurePass IT Certification PDF and VCE

Leave a Reply

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