[2021-New] Microsoft 70-470 Dumps With Update Exam Questions (131-140)

70-470 Dumps

70-470 Exam Questions - Online Test

70-470 Premium VCE File

Learn More 100% Pass Guarantee - Dumps Verified - Instant Download
150 Lectures, 20 Hours

Q1. - (Topic 10)

You are developing a SQL Server Analysis Services (SSAS) tabular project.

You need to grant the minimum permissions necessary to enable users to query data in a

data model.

Which role permission should you use?

A. Explorer

B. Process

C. Administrator

D. select

E. Browser

F. Read

Answer: F

Q2. HOTSPOT - (Topic 10)

You are developing a SQL Server Analysis Services (SSAS) cube.

You are writing the following Multidimensional Expressions (MDX) statement for use by a calculated measure. The measure computes the sales amount for the same time period of the previous year. (Line numbers are included for reference only.)

You need to complete the MDX statement.

Which MDX function should you use in line 03? To answer, select the appropriate MDX function in the functions list.

Answer:

Q3. - (Topic 5)

You need to identify changes in the financial database.

What should you do?

A. Add SQL Server replication to each table.

B. Extract data from the current partition of each table.

C. Add a timestamp column to each table.

D. Perform a full extract of each table.

E. Enable change data capture on each table.

Answer: E

Q4. - (Topic 3)

You need to select an appropriate tool for creating the Regional Sales report.

Which tool or tools should you use? (Each correct answer presents a complete solution. Choose all that apply.)

A. Excel 2010, using the CUBE functions

B. Power View, using a Matrix

C. Excel 2010, using a PivotTable

D. Report Builder, using a Matrix

Answer: B,C,D

Explanation:

B: Working with a matrix in Power View A matrix is a type of visualization that is similar to a table in that it is made up of rows and columns. However, a matrix can be collapsed and expanded by rows and/or columns. If it contains a hierarchy, you can drill down/drill up.

C: Using an Excel Pivot Table, connecting to the Cube, will give you the option to drill down the cube. Using conditional formatting you can highlight specific value ranges.

D: Matrices provide functionality similar to crosstabs and pivot tables. At run time, as the report data and data regions are combined, a matrix grows horizontally and vertically on the page. Values in matrix cells display aggregate values scoped to the intersection of the row and column groups to which the cell belongs. You can format the rows and columns to highlight the data you want to emphasize. You can also include drilldown toggles that initially hide detail data; the user can then click the toggles to display more or less detail as needed.

* From scenario: Management has requested a new report named Regional Sales. This report must be based on the Sales cube and must allow users to filter by a specific year and present a grid with every region on the columns and the Products hierarchy on the rows. The hierarchy must initially be collapsed and allow the user to drill down through the hierarchy to analyze sales. Additionally, sales values that are less than $5000 must be highlighted in red.

Q5. DRAG DROP - (Topic 9)

You are configuring an Excel Services service application in a Microsoft SharePoint farm.

Users will deploy Excel workbooks to SharePoint libraries that allow interaction with PivotTables through Excel Services. PivotTable data is sourced from secured SQL Server Analysis Services (SSAS) cubes and PowerPivot models inside published workbooks.

You need to ensure that users can refresh the PivotTables from within Excel Services without a warning message appearing.

What should you do?

To answer, drag the appropriate term or terms to the correct location or locations in the answer area. (Use only terms that apply.)

Answer:

Q6. - (Topic 4)

You need to roll back the compatibility level of the Research database.

What should you do?

A. Restore a backup of the previous version of the database.

B. Use an ALTER DATABASE statement to set the compatibility option.

C. Change the CompatibilityLevel property in the XMLA script, and then execute the script.

D. In SQL Server Management Studio (SSMS), change the compatibility level in the database properties.

Answer: A

Q7. - (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

Q8. - (Topic 10)

You are developing a SQL Server Analysis Services (SSAS) tabular project. A model defines a measure named Profit and includes a table named Date. The table includes year, semester, quarter, month, and date columns. The Date column is of data type Date. The table contains a set of contiguous dates.

You need to create a measure to report on year-over-year growth of profit.

What should you do? (Each answer presents a complete solution. Choose all that apply.)

A. Use the Business Intelligence Wizard and then use the Define time intelligence enhancement.

B. Define the following calculation.Year Over Year Profit Growth:=CALCULATE([Profit], DATEADD('Date'[Date], 1, YEAR))

C. Define the following calculation.Year Over Year Profit Growth:=[Profit] -CALCULATE([Profit], SAMEPERIODLASTYEAR('Date'[Date]))

D. Define the following calculation.Year Over Year Profit Growth:=[Profit] -CALCULATE([Profit], PARALLELPERIOD('Date'[Date], -12, MONTH))

Answer: A,D

Explanation: A: SSAS Provides feature called "Time Intelligence Wizard". This feature will

provide neat GUI to achieve the same purpose which we were trying by MDX code [using

the PARALLELPERIOD function].

Example:

Lets explore the "Time Intelligence Wizard":

1) In BIDS, Click "Cube" in menu bar and select "Add business Intelligence"

2) Click "Time Intelligence Wizard" on next screen.

3) "Choose Target Hierarchy and Calculations".screen

http://4.bp.blogspot.com/_ntCExJSVw8o/TKm091Wyh_I/AAAAAAAAMV8/dzpwjOPJ_wI/s4 00/TIW_CTH.JPG Etc.

D: Variance analysis for SSAS OLAP cubes is not a simple matter of adding a calculated field to a pivot table. Planning along with the use of the ParallelPeriod MDX functions allows us to quickly create a variance infrastructure for a particular measure. Furthermore, by utilizing a date hierarchy in the Parallel Period function, we can easily traverse down the hierarchy for any attribute below the parallel period level noted in the function (i.e., parallel period based on Year can show either one year back per year, quarter, or month). Although, other methods exist, the parallel period method can be easily followed and applied to various measures.

Q9. 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:

Q10. - (Topic 1)

You need to identify the reports that produce the errors that Marc is receiving.

What should you do?

A. Write a query by using the Subscriptions table in the report server database.

B. Use the Windows Event Viewer to search the Application log for errors.

C. Write a query by using the ExecutionLog3 view in the report server database.

D. Search the ReportServerService_<timestamp>.log file for errors.

Answer: C