Want to Pass 70-470 Exam In Next HOURS? Download Now →
January 12, 2018

Top Microsoft 70-470 answers Choices


Free Instant Download NEW 70-470 Exam Dumps (PDF & VCE):
Available on: https://www.certshared.com/exam/70-470/


Q33. HOTSPOT - (Topic 9) 

A school stores information about teachers, students, classes, and enrollments in a Microsoft Azure SQL Database database. The database includes a table that maps the user IDs of teachers to the subjects they teach. 

Teachers access reports in a SQL Server Reporting Services (SSRS) instance by using their credentials. 

You are developing a report that displays a table of class enrollments for a specific subject. The report will prompt teachers to select from their mapped subjects. The table is based on a dataset named ClassData. To minimize report execution time, the ClassData dataset has been configured to cache all class enrollment data. 

You need to ensure that the report displays the correct class enrollment data. 

Which item should you configure? 

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

Answer: 


Q34. - (Topic 9) 

You deploy a PowerPivot workbook to a document library in a Microsoft SharePoint site. Workbook data comes from two different sources: Source A and Source B. 

The workbook contains three small lookup tables from Source A, and five tables with a total of 20 million rows from Source B. Data from Source A is updated at 9:00 A.M. and data from Source B is updated throughout the day. 

You have the following requirements: 

. Refresh the PowerPivot workbook with updated lookup data as soon as possible. . Minimize load on the source systems and the SharePoint environment during business hours. . Minimize user involvement in the data refresh process. 

You enable automatic data refresh for the workbook. 

You need to configure a data refresh schedule for the workbook that meets the requirements. 

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

A. Set the default schedule to refresh outside of business hours. Do not manually refresh the workbook. 

B. Set the default schedule to refresh outside of business hours. Manually refresh the workbook at 9:00 A.M. every day. 

C. Set the default schedule to refresh at 9:00 A.M. every day. Create a separate schedule for Source B's connection to refresh outside of business hours. 

D. Set the default schedule to refresh at 3:00 P.M. every day. Do not create individual source schedules. 

E. Set the default schedule to refresh at 9:00 A.M. every day. Do not create individual source schedules. 

Answer:


Q35. DRAG DROP - (Topic 10) 

You are developing a SQL Server Analysis Services (S5AS) cube. The cube consists of a 

single measure group. 

The measure group consists of one partition that uses MOLAP. 

The proactive caching policy has the following requirements: 

.. 

The cache must be updated when data is changed in the table named tbIOrders. Changes must be notified through the use of the XML for Analysis (XMLA) NotifyTableChange command. 

You need to configure the proactive caching policy to meet the requirements. 

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

Answer: 


Q36. - (Topic 10) 

... 

You are designing a SQL Server Reporting Services (SSRS) report to display vineyard names and their year-to-date (YTD) grape yield. Grape yield values are classified in three bands: 

High Yield 

Medium Yield 

Low Yield 

You add a table to the report. Then you define two columns based on the fields named VineyardName and YTDGrapeYield. 

You need to set the color of the vineyard text to red, yellow, or blue, depending on the value of the YTD grape yield values. 

What should you do? 

A. Add an indicator to the table. 

B. Use an expression for the Style property of the vineyard text box. 

C. Use an expression for the Font property of the vineyard text box. 

D. Use an expression for the TextDecoration property of the vineyard text box. 

E. Use an expression for the Color property of the vineyard text box. 

Answer:


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

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

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

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

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

Answer:


Q38. - (Topic 10) 

You are designing a SQL Server Reporting Services (SSRS) report that sources data from a SQL Azure database. 

The report must display the value and status of a Key Performance Indicator (KPI). 

Which report item should you use? (Each answer presents a complete solution. Choose all that apply.) 

A. Sparkline 

B. Gauge 

C. Indicator 

D. Data Bar 

E. Image 

Answer: B,C 

Explanation: B: The Gauge data region is a one-dimensional data region that displays a 

single value in your dataset. An individual gauge is always positioned inside a gauge panel, 

where you can add child or adjacent gauges..

You can use gauges to perform many tasks in a report: 

Display key performance indicators (KPIs) in a single radial or linear gauge. 

Place a gauge inside a table or matrix to illustrate values inside each cell. 

Use multiple gauges in a single gauge panel to compare data between fields. 

C: Indicators are minimal gauges that convey the state of a single data value at a glance. 

The icons that represent indicators and their states are simple and visually effective even 

when used in small sizes. 

You can use state indicators in your reports to show the following: 

Trends by using trending-up, flat (no change), or trending-down arrows. 

State by using commonly recognized symbols such as checkmarks and exclamation 

marks. 

Conditions by using commonly recognized shapes such traffic lights and signs. 

Ratings by using common recognized shapes and symbols that show progress such 

number of quadrants in a square and stars. 

Incorrect: Not A, not D: Sparklines and data bars are small, simple charts that convey a lot of information in a little space, often inline with text. Sparklines and data bars are often used in tables and matrices. Their impact comes from viewing many of them together and being able to quickly compare them one above the other, rather than viewing them singly. They make it easy to see the outliers, the rows that are not performing like the others. Although they are small, each sparkline often represents multiple data points, often over time. Data bars can represent multiple data points, but typically illustrate only one. 


Q39. - (Topic 4) 

You need to configure security for the SSRS instance on SSRS01 to connect to SSAS and minimize downtime. 

What should you do? (Each correct answer presents part of the solution. Choose all that apply.) 

A. Register a service principal name for the Report Server service. 

B. Register a service principal name for the Analysis Services service. 

C. Restart the IIS service. 

D. Configure SSRS01 to use the Negotiate authentication type. 

E. Configure SSRS01 to use the Custom authentication type. 

Answer: A,D 

Explanation: A (not B): If you are deploying Reporting Services in a network that uses the Kerberos protocol for mutual authentication, you must create a Service Principal Name (SPN) for the Report Server service if you configure it to run as a domain user account. 

D (not E): 

* See step 6 below. 

To register an SPN for a Report Server service running as a domain user 

. ... ... 

Install Reporting Services and configure the Report Server service to run as a 

domain user account. Note that users will not be able to connect to the report 

server until you complete the following steps. 

Log on to the domain controller as domain administrator. 

Open a Command Prompt window. 

Copy the following command, replacing placeholder values with actual values that 

are valid for your network: 

Setspn -a http/<computer-name>.<domain-name>:<port><domain-user-account> 

Run the command. 

Open the RsReportServer.config file and locate the <AuthenticationTypes> 

section. Add <RSWindowsNegotiate/> as the first entry in this section to enable 

NTLM. 

* RSWindowsNegotiate. If you initially set the Windows service account for the report server to NetworkService or LocalSystem in Reporting Services Configuration Manager, RSWindowsNegotiate is added to the RSReportServer.config file as the default setting. With this setting, the report server can accept requests from client applications requesting Kerberos or NTLM authentication. If Kerberos is requested and the authentication fails, the report server switches to NTLM authentication and prompts the user for credentials unless the network is configured to manage authentication transparently. 

Using RSWindowsNegotiate is your best option because it provides the greatest flexibility for multiple clients in an intranet environment. 

Not C: IIS is not mention in this scenario. 

Note: 

* From scenario: 

/ A single-server deployment of SQL Server 2008 R2 Reporting Services (SSRS) in native 

mode is installed on a server named SSRS01. The Reporting Server service is configured 

to use a domain service account. 

Reference: Register a Service Principal Name (SPN) for a Report Server 


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

Answer: 


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

Answer: 


Q42. - (Topic 6) 

You need to select and configure a tool for the monitoring solution. What should you choose? 

A. Performance Monitor configured with the MSAS11:Storage Engine Query counter 

B. Performance Monitor configured with the MSAS11:Processing counter 

C. SQL Server Profiler configured with the Query Processing: Query Subcube event 

D. SQL Server Profiler configured with the Queries Events: Query Begin event 

Answer:


Q43. HOTSPOT - (Topic 9) 

You are developing a SQL Server Reporting Services (SSRS) solution. 

You plan to create reports based on the results of a currency exchange SOAP web service 

call. 

You need to configure a shared data source. 

Which data source type should you use? 

To answer, select the appropriate type from the drop-down list in the answer area. 

Answer: 


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

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

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

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

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

Answer:


Q45. - (Topic 1) 

You need to create the hierarchy in the AdhocReports project in time for the next production release cycle. 

What should you do? 

A. Multi-select all of the columns, right-click the columns, and then click the Create Hierarchy command. Check in the changes before the next release cycle. 

B. Use the RELATED() function to consolidate the columns in the DimSalesTerritory table, multi-select the columns, right-click the columns, and then click the Create Hierarchy command. Check in the changes before the next release cycle. 

C. Use the RELATEDTABLEQ function to consolidate the tables, multi-select the columns in the hierarchy, right-click the columns, and then click the Create Hierarchy command. Check in the changes before the next release cycle. 

D. Use the RELATED() function to consolidate the columns in the DimEmployee table, multi-select the columns, right-click the columns, and then click the Create Hierarchy command. Check in the changes before the next release cycle. 

Answer:


Q46. - (Topic 4) 

You need to re-establish subscriptions on SSRS01. What should you do? 

A. Manually failover the active node. 

B. Install prerequisites and upgrade shared components on Node1 and Node2. 

C. Generate a SQL Server 2012 configuration file by running the SQL Server Setup executable. 

D. Upgrade Node1 by using the SQL Server 2012 Upgrade wizard. 

Answer:

Explanation: 

SSRS reports are scheduled by SQL server Agent jobs. Start the SQL Server Agent on SSRS01. 


Q47. - (Topic 9) 

You are designing a SQL Server Integration Services (SSIS) solution. The solution will contain an SSIS project that includes several SSIS packages. Each SSIS package will define the same connection managers and variables. 

You have the following requirements: 

. Ensure that the deployment model supports changing the content of connection strings by using parameters at execution time. . Ensure that the deployment model automatically starts from calls to the 

catalog.start_execution stored procedure in the SSISDB database. 

. Maximize performance at execution time. 

. Minimize development effort. 

You need to design a solution that meets the requirements. 

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

A. Use a package deployment model. Use a SQL Server package configuration with a common filter. Change the contents of the SSIS Configurations table at runtime. 

B. Use a project deployment model. Configure connections in an XML configuration file referenced by an environment variable that corresponds to the SQL Server environment of each SSIS package. 

C. Use a package deployment model. Save each SSIS package to a file share that can be accessed from all environments. 

D. Use a project deployment model. Modify connection manager properties to use project parameters. Ensure that the SSISDB database is created. 

Answer:


Q48. - (Topic 6) 

You need to create the factCustomerContact table. 

Which type of table should you create? 

A. A fact table with a non-additive measure 

B. A factless fact table 

C. A periodic snapshot fact table 

D. A fact table with an additive measure 

Answer: