Dumps4cert.com : Latest Dumps with PDF and VCE Files
2018 Aug Microsoft Official New Released 70-470
100% Free Download! 100% Pass Guaranteed!

Recertification for MCSE: Business Intelligence

Question No: 111 – (Topic 9)

A SQL Server Analysis Services (SSAS) cube contains a large measure group. The fact table supporting the measure group is loaded with new data throughout the day.

You have the following requirements:

->Ensure that the cube displays current data as quickly as possible.

->Maximize availability of the cube.

->Maximize query performance for all aggregation levels.

You need to choose a partitioning strategy that meets the requirements.

Which partitioning strategy should you choose? (More than one answer choice may achieve the goal. Select the BEST answer.)

  1. Create one partition for the current day that uses multidimensional OLAP (MOLAP) with proactive caching as a storage mode.

  2. Create one partition for the current month that uses hybrid OLAP (HOLAP) as a storage mode.

  3. Create one partition for the current day that uses relational OLAP (ROLAP) as a storage mode.

  4. Create one partition for the current day that uses multidimensional OLAP (MOLAP) as a storage mode. Process the partition each night.

Answer: A

Question No: 112 DRAG DROP – (Topic 9)

You develop a SQL Server Integration Services (SSIS) project named Project1 by using SQL Server Data Tools (SSDT). Project1 contains a package named Package1.

You add a project parameter named EnvironmentText to Project1.

In SQL Server Management Studio (SSMS), you create the SSIS catalog. You have the following requirements:

->Configure Package1 so it can run within either a development environment or a test environment.

->Pass the value of an SSIS environment variable to the EnvironmentText project

parameter.

->The value of the environment variable must be different for each SSIS environment.

You need to deploy the SSIS project and configure the SSIS environment.

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

Dumps4Cert 2018 PDF and VCE

Answer:

Dumps4Cert 2018 PDF and VCE

Question No: 113 DRAG DROP – (Topic 9)

You are designing a SQL Server Analysis Services (SSAS) data model on a very large data warehouse.

The fact tables in the data warehouse contain terabytes of data in tens of billions of rows. You must support the following features:

->Complex attribute/column relationships

->Advanced calculations in the data model definition

->Advanced calculations using logic deployed in a custom assembly

You need to choose the correct SSAS design strategy. What should you do?

To answer, drag the appropriate term or terms to the correct location or locations in the answer area. (Answer choices may be used once, more than once, or not all.)

Dumps4Cert 2018 PDF and VCE

Answer:

Dumps4Cert 2018 PDF and VCE

Explanation:

Dumps4Cert 2018 PDF and VCE

Note: Box 1:

The primary reason for building an Analysis Services multidimensional model is to achieve fast performance of ad hoc queries against business data. A multidimensional model is composed of cubes and dimensions that can be annotated and extended to support complex query constructions.

Box 2:

A partition is a container for a portion of the measure group data. Partitions are not seen from MDX queries; all queries reflect the whole content of the measure group, regardless of how many partitions are defined for the measure group. The data content of a partition is defined by the query bindings of the partition, and by the slicing expression.

Box 3:

Multidimensional Expressions (MDX) is the query language that you use to work with and retrieve multidimensional data in Microsoft SQL Server 2005 Analysis Services (SSAS).

Question No: 114 HOTSPOT – (Topic 9)

A SQL Server Analysis Services (SSAS) cube contains billions of rows of data and is rapidly increasing in size. The cube consists of a single measure group and a single partition. The cube is currently processed by using the Process Full process option.

You have the following requirements to reduce the cube processing time:

->Partition the measure group by month.

->Create a staging table that contains only data which is more recent than the last time the cube was processed.

->Do not include data updates or deletions in the staging table.

->Insert records from the staging table into the appropriate partition.

You need to change the process option to meet the requirements.

Which process option should you choose?

To answer, select the appropriate option from the drop-down list in the dialog box.

Dumps4Cert 2018 PDF and VCE

Answer:

Dumps4Cert 2018 PDF and VCE

Explanation:

Dumps4Cert 2018 PDF and VCE

Note: ProcessAdd

ProcessAdd applies only to dimensions and partitions.

ProcessAdd is a new processing option for dimensions that did not exist in Analysis Services 2000. It essentially optimizes ProcessUpdate for the scenario where only new members are added. ProcessAdd never deletes or updates existing members. It only adds new members. The user can restrict the dimension table so that ProcessAdd reads only the new rows.

ProcessAdd for partitions is the equivalent of incremental partition processing in Analysis Services 2000. The user typically specifies an alternate fact table or a filter condition pointing to the new rows. ProcessAdd internally creates a temporary partition, processes it with the specified fact data, and merges it into the target partition.

Question No: 115 – (Topic 9)

You are designing a complex report that displays sales metrics for a customer hierarchy. The customer hierarchy has six levels and contains approximately 1 million members.

You have the following requirements:

->Allow users to easily display and navigate data.

->Minimize report processing time.

->Simplify the report design and maintenance processes.

You need to design a report that meets the requirements.

How should you design the report? (More than one answer choice may achieve the goal. Select the BEST answer.)

  1. Retrieve all customers and use drilldown groupings to show or hide hierarchy levels.

  2. Add a report part for each hierarchy level.

  3. Add a subreport for each hierarchy level.

  4. Display only the children of the current hierarchy level and drill through to the selected member.

Answer: D

Question No: 116 – (Topic 9)

A company runs SQL Server Database Engine and SQL Server Reporting Services (SSRS) in native mode. Reports are based on data that is cached in multiple shared datasets. Source data is archived each day at midnight for regulatory compliance purposes. The shared datasets may continue to cache data that should not be used in reports. Shared report schedules are often paused during nightly server maintenance windows.

Reports must not return archived data.

You need to create a fully automated solution to ensure that reports do not deliver archived data.

What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

  1. Write a script that calls the flushcache method to clear individual items from the SSRS cache. Create a SQL Server Agent job that runs rs.exe with the script as an input file, and schedule the job to run every day after the archive process completes.

  2. Create a SQL Server Agent job that uses a Transact-SQL (T-SQL) step to truncate the dbo.ExecutionCache table in the ReportServerTempDB database. Schedule the job to run every day after the archive process completes.

  3. Create a SQL Server Agent job that restarts the SQL Server Reporting Services service. Schedule the job to run every day after the archive process completes.

  4. Create a shared schedule. Configure the datasets to expire on the shared schedule.

Answer: A

Question No: 117 DRAG DROP – (Topic 9)

You plan to deploy a SQL Server Integration Services (SSIS) project by using the project deployment model.

You need to monitor control flow tasks to determine whether any of them are running longer than usual.

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

Dumps4Cert 2018 PDF and VCE

Answer:

Dumps4Cert 2018 PDF and VCE

Explanation:

Box 1: Connect to the SSISDB database.

Box 2:

Dumps4Cert 2018 PDF and VCE

Box 3: Execute the query Note:

  • execution_component_phases

    Displays the time spent by a data flow component in each execution phase.

  • The following example uses the catalog.execution_component_phases view to find the total amount of time that a specific package has spent executing in all phases (active_time), and the total elapsed time for the package (total_time).

use SSISDB

select package_name, task_name, subcomponent_name, execution_path, SUM(DATEDIFF(ms,start_time,end_time)) as active_time,

DATEDIFF(ms,min(start_time), max(end_time)) as total_time from catalog.execution_component_phases

where execution_id = 1841

group by package_name, task_name, subcomponent_name, execution_path order by package_name, task_name, subcomponent_name, execution_path

* catalog.executables

This view displays a row for each executable in the specified execution.

An executable is a task or container that you add to the control flow of a package.

*(incorrect) catalog.executions (SSISDB Database)

Displays the instances of package execution in the Integration Services catalog. Packages that are executed with the Execute Package task run in the same instance of execution as the parent package.

This view displays a row for each instance of execution in the catalog.

* (incorrect) catalog.operation_messages

Displays messages that are logged during operations in the Integration Services catalog. This view displays a row for each message that is logged during an operation in the catalog. The message can be generated by the server, by the package execution process, or by the execution engine.

Question No: 118 – (Topic 9)

You are creating a Multidimensional Expressions (MDX) calculation for Projected Revenue in a cube.

For Product A, Projected Revenue is defined as 150 percent of the Total Sales of the product. For all other products, Projected Revenue is defined as 110 percent of the Total Sales of the product.

You need to calculate the Projected Revenue as efficiently as possible.

Which calculation should you use? (More than one answer choice may achieve the goal. Select the BEST answer.)

Dumps4Cert 2018 PDF and VCE

  1. Option A

  2. Option B

  3. Option C

  4. Option D

Answer: C

Question No: 119 HOTSPOT – (Topic 9)

A company maintains separate environments for test, production, and quality assurance. The company uses the project deployment model for SQL Server Integration Services (SSIS) deployments.

You create an SSIS project to perform a weekly refresh of the company#39;s data warehouse and cubes. The project has three packages.

You need to configure the project to ensure so that the test and production teams can run each package without manually adding server-specific information.

Which program should you use?

To answer, select the appropriate program in the answer area.

Dumps4Cert 2018 PDF and VCE

Answer:

Dumps4Cert 2018 PDF and VCE

Explanation:

SQL Server Management Studio

Question No: 120 HOTSPOT – (Topic 9)

You are developing a SQL Server Analysis Services (SSAS) cube. A dimension named Customer is based on the DimCustomer table. A subset of the data source view is shown in the following graphic.

Dumps4Cert 2018 PDF and VCE

You need to relate the Customer dimension to the AccountBalance measure group.

Which relationship type should you choose? To answer, select the appropriate option from the drop-down list in the dialog box.

Dumps4Cert 2018 PDF and VCE

Dumps4Cert 2018 PDF and VCE

Answer:

Dumps4Cert 2018 PDF and VCE

100% Dumps4cert Free Download!
70-470 PDF
100% Dumps4cert Pass Guaranteed!
70-470 Dumps

Dumps4cert 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

Leave a Reply

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

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