[Free] 2018(June) Dumps4cert Microsoft 70-470 Dumps with VCE and PDF Download 181-190

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

Recertification for MCSE: Business Intelligence

Question No: 181 – (Topic 9)

You are redesigning a SQL Server Analysis Services (SSAS) database that contains a cube named Sales. Before the initial deployment of the cube, partition design was optimized for processing time. The cube currently includes five partitions named FactSalesl through FactSales5. Each partition contains from 1 million to 2 million rows.

The FactSales5 partition contains the current year#39;s information. The other partitions contain information from prior years; one year per partition. Currently, no aggregations are defined on the partitions.

You remove fact rows that are more than five years old from the fact table in the data source and configure query logs on the SSAS server.

Several queries and reports are running very slowly.

You need to optimize the partition structure and design aggregations to improve query performance and minimize administrative overhead.

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

  1. Use the Aggregation Design Wizard to create aggregations for the current partitions.

  2. Combine all the partitions into a single partition. Use the Usage-Based Optimization Wizard to create aggregations.

  3. Combine all the partitions into a single partition. Use the Aggregation Design Wizard to create aggregations.

  4. Use the Usage-Based Optimization Wizard to create aggregations for the current partitions.

Answer: D

Question No: 182 HOTSPOT – (Topic 9)

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

You create an SSIS project to perform a daily refresh of the data warehouse and data models. The project has five packages.

You need to configure the project to ensure that the development, 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

Question No: 183 – (Topic 9)

You are designing a SQL Server Integration Services (SSIS) solution that will load multiple Online Transactional Processing (OLTP) data sources into a SQL Server data mart.

You have the following requirements:

->Ensure that the process supports the creation of an exception report that details possible duplicate key values, null ratios within columns, and column-length distributions of values.

->Ensure that users can generate the exception report in an XML format.

->Use the minimum development effort.

You need to design the SSIS solution to meet the requirements.

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

BEST answer.)

  1. Use a Data Profiling task. Use a Data Flow task to extract the XML output of the Data Profiling task into a SQL Server table. Query the table to view the exceptions.

  2. Use Data Flow tasks to process the clean data.

  3. Use a Data Profiling task. Read the exceptions in Data Profile Viewer.

  4. Design a stored procedure that examines data for common dirty data patterns. Use an Execute SQL task.

Answer: C Explanation:

Ref: http://technet.microsoft.com/en-us/library/bb895263.aspx

Question No: 184 – (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 purged 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 purged data.

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

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

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

  2. 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 purge process completes.

  3. Create a SQL Server Agent job that uses a Transact-SQL (T-SQL) step to delete the data from the dbo.ExecutionCache table in the ReportServerTempDB database. Schedule the job to run every day after the purge process completes.

  4. Republish the cached datasets by using SQL Server Data Tools.

Answer: B

Question No: 185 DRAG DROP – (Topic 9)

You are validating whether a SQL Server Integration Services (SSIS) package named Master.dtsx in the SSIS catalog is executing correctly.

You need to display the number of rows in each buffer passed between each data flow component of the package.

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:

Dumps4Cert 2018 PDF and VCE

Box 2:

Dumps4Cert 2018 PDF and VCE

Box 3:

Dumps4Cert 2018 PDF and VCE

Note:

* You are going to become very very familiar indeed with [catalog].[executions]. It is a view that provides a record of all package executions on the server and, most importantly, it contains [execution_id] – the identifier for each execution and the field to which all other objects herein will be related.

Question No: 186 – (Topic 9)

You are designing a partitioning strategy for a large fact table in a data warehouse.

Tens of millions of new records are loaded into the data warehouse weekly, outside of business hours. Most queries are generated by reports and by cube processing. Data is frequently queried at the day level and occasionally at the month level.

You need to partition the table to maximize the performance of queries.

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

  1. Partition the fact table by month, and compress each partition.

  2. Partition the fact table by week.

  3. Partition the fact table by year.

  4. Partition the fact table by day, and compress each partition.

Answer: D

Question No: 187 – (Topic 9)

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

For Customer A, Projected Revenue is defined as 150 percent of the Total Sales for the customer. For all other customers, Projected Revenue is defined as 110 percent of the Total Sales for the customer.

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: 188 DRAG DROP – (Topic 9)

You are the database administrator of a SQL Server 2012 data warehouse implemented as a single database on a production server. The database is constantly updated by using SQL Server Integration Services (SSIS) packages and SQL Server Analysis Services (SSAS) cube writeback operations.

The database uses the full recovery model. A backup strategy has been implemented to minimize data loss in the event of hardware failure.

SQL Server Agent jobs have been configured to implement the following backup operations:

->A full database backup every day at 12:00 A.M.

->Differential database backups every day at 6:00 A.M., 12:00 P.M., and 6:00 P.M.

->Transaction log backups every hour on the hour.

At 2:38 P.M. a SSIS package corrupts the data in a fact table. The corruption cannot be undone. You are notified at 3:15 P.M. You immediately take the database offline to prevent further data access and modification.

You need to restore the data warehouse and minimize downtime and data loss.

Which four 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:

Dumps4Cert 2018 PDF and VCE

Box 2:

Dumps4Cert 2018 PDF and VCE

Box 3:

Dumps4Cert 2018 PDF and VCE

Box 4:

Dumps4Cert 2018 PDF and VCE

Note:

* (box 1)

/ For a database using the full or bulk-logged recovery model, in most cases you must back up the tail of the log before restoring the database. Restoring a database without first backing up the tail of the log results in an error, unless the RESTORE DATABASE statement contains either the WITH REPLACE or the WITH STOPAT clause, which must specify a time or transaction that occurred after the end of the data backup.

/ If the database is online and you plan to perform a restore operation on the database, before starting the restore operation, back up the tail of the log using WITH NORECOVERY:

BACKUP LOG database_name TO lt;backup_devicegt; WITH NORECOVERY

  • To restore a database to a specific point in time or transaction, specify the target recovery point in a STOPAT, STOPATMARK, or STOPBEFOREMARK clause.

  • (incorrect, box 4): The STOPBEFOREMARK and STOPATMARK options have two parameters, mark_name and lsn_number. The mark_name parameter, which identifies a transaction mark in a log backup, is supported only in RESTORE LOG statements.

The lsn_number parameter, which specifies a log sequence number, is supported in both RESTORE DATABASE statements and RESTORE LOG statements.

Question No: 189 DRAG DROP – (Topic 9)

You are the administrator of a SQL Server Integration Services (SSIS) catalog. You have access to the original password that was used to create the SSIS catalog.

A full database backup of the SSISDB database on the production server is made each day. The server used for disaster recovery has an operational SSIS catalog.

Information that is encrypted in the SSISDB database must not be lost.

You need to document the disaster recovery solution that restores the production SSIS catalog to the disaster recovery server.

Which three steps should you document 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: Restore the SSISDB database from the last backup. Box 2: Restore the SSISDB master key.

Box 3: Open the SSISDB database master key

Note:

To Restore the SSIS Database

->Restore the SSISDB database from the backup by using the Restore Database dialog box in SQL Server Management Studio.

->Restore the master key (Use this method if you have the original password that was used to create SSISDB), open master key decryption by password = #39;LS1Setup!#39; -#39;Password used when creating SSISDB#39;Alter Master Key Add encryption by Service Master Key

Question No: 190 – (Topic 9)

You are defining a named set by using Multidimensional Expressions (MDX) in a sales cube.

The cube includes a Customer dimension that contains a Geography hierarchy and a Gender attribute hierarchy.

You need to return only the female customers in the Geography hierarchy.

Which set 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: A

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

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 *