Ucertify offers free demo for 70-470 exam. "Recertification for MCSE: Business Intelligence", also known as 70-470 exam, is a Microsoft Certification. This set of posts, Passing the Microsoft 70-470 exam, will help you answer those questions. The 70-470 Questions & Answers covers all the knowledge points of the real exam. 100% real Microsoft 70-470 exams and revised by experts!
Q161. - (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.
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
* 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.
* 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
Q162. - (Topic 4)
You need to use SQL Server Management Studio (SSMS) to make the SSAS databases available for application testing.
What should you do?
A. Restore the SSAS databases from the latest backup to SSAS01.
B. Script the databases as a Create script to a new window and then execute the script on SSAS01.
C. Detach the SSAS databases from the SSASCluster, and then attach them to SSAS01.
D. Use the Import/Export Wizard to copy the databases from the production server to the development server.
Q163. - (Topic 10)
You are conducting a design review of a multidimensional project.
In the geography dimension, all non-key attributes relate directly to the key attribute. The
underlying data of the geography dimension supports relationships between attributes.
You need to increase query and dimension processing performance.
What should you do?
A. For the geography dimension, set the ProcessingMode property to LazyAggregations
B. For the dimension attributes of the geography dimension, define appropriate attribute relationships.
C. For the geography dimension, set the ProcessingPriority property to 1.
D. For the dimension attributes of the geography dimension, set the GroupingBehavior property to EncourageGrouping.
Q164. - (Topic 10)
You develop a SQL Server Analysis Services (SSAS) tabular project. The tabular model loads data from a SQL Server 2012 relational database each day.
You define a connection.
You need to ensure that the connection minimizes the attack surface area of the server.
How should you define the impersonation information for the connection? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Use the credentials of the SQL Server Analysis Services (SSAS) service account. Grant least privilege to this account in the source database.
B. Use your domain credentials. Grant least privilege to your account in the source database.
C. Create and use a new Windows domain account. Grant least privilege to this account in the source database.
D. Use SQL Server authentication.
Q165. - (Topic 10)
You are designing a SQL Server Reporting Services (SSRS) report. The report defines a single SQL Server data source and dataset.
You need to include additional data sourced from a SQL Azure database in the report.
What should you do?
A. Create a SQL Azure dataset that uses the existing data source.
B. Create a SQL Azure data source and then add a dataset that uses the new data source.
C. Create a SQL Server data source and then add a dataset that uses the new data source.
D. Generate an Atom-compliant data feed for the report.
Q166. - (Topic 10)
You are troubleshooting query performance for a SQL Server Analysis Services (SSAS) cube.
A user reports that a Multidimensional Expressions (MDX) query is very slow.
You need to identify the MDX query statement in a trace by using SQL Server Profiler.
Which event class should you use?
A. Progress Report Begin
B. Query Begin
C. Execute MDX Script Begin
D. Calculate Non Empty Begin
E. Get Data From Aggregation
F. Query Subcube
Q167. - (Topic 10)
You are designing a SQL Server Reporting Services (SSRS) report for a bank. The bank has Automated Teller Machines (ATMs) in several regions. ATM operational data is stored in a SQL Azure database.
The report must use a map to display the location and status of the ATMs as shown in the following exhibit. (Click the Exhibit button.)
You need to ensure that the report displays only a user selected map region.
Which source of spatial data should you use for the map?
A. SQL Server spatial query
B. Bing Maps layer
C. ESRI shape file
D. Map gallery
Q168. - (Topic 9)
You are modifying a SQL Server Reporting Services (SSRS) report for a SQL Server Analysis Services (SSAS) cube. The report defines a report parameter of data type Date/Time with which users can filter the report by a single date. The parameter value cannot be directly used to filter the Multidimensional Expressions (MDX) query for the dataset.
You need to ensure that the report displays data filtered by the user-entered value. You must achieve this goal by using the least amount of development effort.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Edit the dataset query parameter. Change the Value property of the report parameter to an expression that uses the same format as the date dimension member key value.
B. Edit the dataset query parameter. Change the Name property of the dataset query parameter so that it points to a name value for each date dimension member.
C. Edit the dataset query parameter. Create a subcube subquery that uses the StrToSet MDX function and accepts the report parameter value.
D. Change the dataset query to Transact-SQL (T-SQL). Use the OPENROWSET function to query the cube. Output the cube results to the T-SQL query and use a Convert function to change the report parameter value into the same format as the date dimension member.
Q169. - (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
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.
Q170. - (Topic 9)
A group of report writers develop reports. The report writers currently use Report Builder
1.0 and report models. The report writers are not trained on SQL Server query writing. Their report assignments come from various departments within the organization.
The company is upgrading to SQL Server 2012 with SQL Server Reporting Services (SSRS) in native mode. After the upgrade, reports will retrieve data from a large new data warehouse that will reside on an instance of the SQL Server 2012 Database Engine. Reports for each department will retrieve data from only a subset of the data warehouse tables.
You are designing the data access strategy. You have the following requirements:
. Ensure that report writers can create only reports that display data which they have database permissions to view. . Minimize the effort required to update connection strings for all reports developed by the report writers. . Minimize the number of fields visible in a specific report for report writers who work for multiple departments.
You need to design the data access strategy.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Create report models by using SQL Server Data Tools (SSDT). Include data sources and data source views for each department's data requirements. Configure each data source to use integrated security.
B. Create one shared data source that uses integrated security. Create an embedded dataset for each report.
C. Create one shared data source that uses integrated security. Create one shared dataset that includes all tables required by the report writers.
D. Create one shared data source that uses integrated security. Create a shared dataset for each department's data requirements.
Q171. - (Topic 8)
You need to deploy a solution for the planned self-service reports that will be used by the sales department managers.
What is the best solution you should deploy? More than one answer choice may achieve the goal. Select the BEST answer.
A. A filter
B. A KPI
C. A calculated column
D. A measure
Q172. - (Topic 4)
You need to implement the Customer Sales and Manufacturing data models.
What should you do? (Each correct answer presents a partial solution. Choose all that apply.)
A. Use the Database Synchronization Wizard to upgrade the database to tabular mode.
B. Use SQL Server Integration Services (SSIS) to copy the database design to the SSAS instance, and specify tabular mode as the destination.
C. Use SQL Server Data Tools (SSDT) to redevelop and deploy the projects.
D. Use the current SSAS instance.
E. Install a new instance of SSAS in tabular mode.
C: Tabular models are authored in SQL Server Data Tools (SSDT) using new tabular model project templates. You can import data from multiple sources, and then enrich the model by adding relationships, calculated columns, measures, KPIs, and hierarchies. Models can then be deployed to an instance of Analysis Services where client reporting applications can connect to them. Deployed models can be managed in SQL Server Management Studio just like multidimensional models. They can also be partitioned for optimized processing and secured to the row-level by using role based security.
E: If you are installing Analysis Services to use the new tabular modeling features, you must install Analysis Services in a server mode that supports that type of model. The server mode is Tabular, and it is configured during installation. After you install the server in this mode, you can use it host solutions that you build in tabular model designer. A tabular mode server is required if you want tabular model data access over the network.
From scenario: / Deploy a data model to allow the ad-hoc analysis of data. The data model must be cached and source data from an OData feed. / All SSAS databases other than the Research database must be converted to tabular BI Semantic Models (BISMs) as part of the upgrade to SSAS 2012. The Research team must have access to the Research database for modeling throughout the upgrade. To facilitate this, you detach the Research database and attach it to SSAS01.
The Business Intelligence Semantic Model (BISM) is a single unified BI platform which has both multi-dimensional as well as tabular data modeling capabilities to offer best of both worlds and choice for the developer.
Reference: Install Analysis Services in Tabular Mode
Reference: Tabular Modeling (SSAS Tabular)
Q173. DRAG DROP - (Topic 10)
You are developing a SQL Server Analysis Services (SSAS) multidimensional project that is configured to source data from a SQL Azure database. The cube is processed each night at midnight.
The largest partition in the cube takes 12 hours to process, and users are unable to access the cube until noon. The partition must be available for querying as soon as possible after processing commences.
You need to ensure that the partition is available for querying as soon as possible, without using source data to satisfy the query.
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.)
Q174. DRAG DROP - (Topic 10)
You are developing reports based on the SQL Server Analysis Services (SSAS) cube named ProcessedOrders.
A Multidimensional Expressions (MDX) query must include a query-scoped calculated member, which computes average sales per order item. The query must also return the set of three states in a query-scoped named set named East Coast Customers.
You need to define the calculations in an MDX query to meet the requirements.
Which four MDX segments should you insert in sequence before a SELECT statement? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)
Q175. - (Topic 9)
You are defining a named set by using Multidimensional Expressions (MDX) in a sales cube.
The cube includes a Product dimension that contains a Category hierarchy and a Color attribute hierarchy.
You need to return only the blue products in the Category hierarchy.
Which set should you use? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Option A
B. Option B
C. Option C
D. Option D
Q176. - (Topic 10)
You are developing a SQL Server Analysis Services (SSAS) multidimensional database.
The underlying data source does not have a time dimension table.
You need to implement a time dimension.
What should you do?
A. Use the SQL Server Data Tools Dimension Wizard and generate a time table in the data source.
B. Create a time dimension by using the Define dimension intelligence option in the Business Intelligence Wizard.
C. Create a time dimension by using the Define time intelligence option in the Business Intelligence Wizard.
D. Add an existing SSAS database time dimension as a cube dimension.