70-470 Exam
Latest 70-470: Pass4sure real training tools from 41 to 50
Testking 70-470 Questions are updated and all 70-470 answers are verified by experts. Once you have completely prepared with our 70-470 exam prep kits you will be ready for the real 70-470 exam without a problem. We have Down to date Microsoft 70-470 dumps study guide. PASSED 70-470 First attempt! Here What I Did.
Q41. DRAG DROP - (Topic 3)
You need to extend the schema design to store the product dimension data.
Which design should you use?
To answer, drag the appropriate table or tables to the correct location or locations in the answer area. (Fill from left to right. Answer choices may be used once, more than once, or not all.)
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: A
Q43. - (Topic 1)
You need to ascertain why Marc did not receive his reports.
What should you do?
A. Search the ReportServerService_<timestamp>.log file for errors.
B. Search the registry for errors.
C. Use the Windows Event Viewer to search the Application log for errors.
D. Use SQL Server Management Studio to search the SQL Server logs for errors.
Answer: B
Q44. - (Topic 3)
You need to fix the PowerPivot data refresh problem by using the least amount of administrative effort.
What should you do?
A. Use the PowerPivot Configuration Tool and select the Upgrade Features, Services, Applications and Solutions option.
B. Use the PowerPivot Configuration Tool and select the Configure or Repair PowerPivot for SharePoint option.
C. Reinstall SSAS in PowerPivot for SharePoint mode by using the SQL Server 2012 installation media.
D. In SharePoint Central Administration, create a target application and configure the
PowerPivot service application settings to use the target application.
Answer: B
Q45. - (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
Answer: C
Q46. DRAG DROP - (Topic 10)
You are making changes to a cube named Sales.
You must increase the value of the measure named Sales Amount by 10%. The increase must be applied only to the children of the member named East in the dimension named Customer.
You need to complete the Multidimensional Expressions (MDX) statement in the calculations section of the Sales cube.
Which statement fragments should you use? (To answer, drag the appropriate statement component to the correct location or locations in the answer area. Use only components that apply.)
Answer:
264. - (Topic 10)
You are developing a SQL Server Analysis Services (SSAS) tabular project that will be used by the finance, sales, and marketing teams.
The sales team reports that the model is too complex and difficult to use. The sales team does not need any information other than sales related resources in the tabular model. The finance and marketing teams need to see all the resources in the tabular model.
You need to implement a solution that meets the needs of the sales team while minimizing development and administrative effort.
What should you do?
A. Create a separate tabular model for each team.
B. Hide the non-sales columns from the client tools.
C. Create a security role for the sales team.
D. Create a perspective for the sales team.
Answer: D
Q47. - (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
Q48. - (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.
Q49. DRAG DROP - (Topic 7)
You are creating the Australian states query.
Which Multidimensional Expressions (MDX) calculation should you use to complete the
query?
To answer, drag the appropriate calculation to the answer area.
Answer:
85. DRAG DROP - (Topic 7)
You need to configure the LoadFactFreightCosts package to address the data integrity issues.
Which data flow component should you use?
To answer, drag the appropriate data flow component to the answer area.
Answer:
Topic 8, Tailspin Toys Case B
Overview
Tailspin Toys is a manufacturing company that has offices across the United States,
Europe, and Asia.
Tailspin Toys plans to implement a business intelligence (BI) solution for its US-based
headquarters to manage the sales data, including information on customer transactions,
products, sales quotas, and bonuses.
Existing Environment
Data Sources
Tailspin Toys currently stores data in line-of-business applications, relational databases,
flat files, and the following;
. A Microsoft Excel spreadsheet named MarketResearch.xlsx. The spreadsheet is stored on a network drive in a directory owned by an analyst. . A tabular model named Research.xlsx used in PowerPivot for Excel. Research.xlsx uses MarketResearch.xlsx as one of its data sources.
Network The network contains an Active Directory forest named tailspintoys.com. The forest contains a Microsoft SharePoint Server 2013 server farm.
Implementation Plans
Databases Tailspin Toys plans to build a star schema data warehouse named DB1. DB1 will be loaded from several different sources and will be updated nightly to contain new sales data.
DB1 will contain the following table types: . A fact table to store transactional data, including transaction date, productID, customerID, quantity, and sales amounts. . Dimension tables to store information about each customer, each product, each date, and each sales department user.
BI Semantic Models
Tailspin Toys plans to deploy the following BI semantic models:
. A multidimensional cube named CUBE1 that will store sales data. CUBE1 will be based on DB1 and will be hosted in SQL Server Analysis Services (SSAS). CUBE1 will contain two distinct count measures named UniqueCustomers and UniqueProducts. The measures are expected to aggregate hundreds of millions of rows from DB1.
. A tabular model named SalesCommission that will contain information about sales department user quotas and commissions. . A tabular model named Research that will contain the migrated model from Research.xlsx. . An instance of SSAS in tabular mode named Tabular.
Planned Reports and Queries
Tailspin Toys plans to implement the following reports and queries: . Power View reports that use data from the Research model. . Reports for each year the company recorded sales data that used the
SalesCommission model. The reports will use the Dates_Between() and the
DatesInPeriod() DAX functions in queries. . Reports that use CUBE1 that contain the following query statements:
. A report named SalesByCategory that uses CUBE1 and the following query statement: (Line numbers are included for reference only.)
Self-Service Reporting
Tailspin Toys plans to deploy the following self-service reports: . Reports created by sales department specialists that use CUBE1 and contain drillthroughs, maps, sparklines, and Key Performance Indicators (KPIs). The reports will be stored in a SharePoint Server document library named Library1. . Reports created by sales department managers that use the SalesCommission model. The reports will contain visualizations that show sales department users their current sales as compared to their quota. . Power Pivot models stored in a SharePoint Server document library that is configured as a PowerPivot Gallery named Gallery1.
Requirements
Data Security Requirements Sales department users browsing CUBE1 must be able to view the sales data that relates to their respective customers only.
Access to reports must be controlled by using SharePoint permissions.
ETL Requirements Tailspin Toys identifies the following extract, transformation, and load (ETL) requirements: . Nightly updates of DB1 must support the incremental load of dimension and fact tables on separate schedules. Fact data may be loaded before dimension data. . ETL processes must be able to update dimension attributes without losing context for historical facts.
. Referential integrity between dimension and fact tables must be maintained at all times.
Cube Performance Requirements The design of CUBE1 must minimize the processing time of the UniqueCustomers and Unique Products measures. The time required to process CUBE1 each night must be minimized.
Data Refresh Requirements The Research model must be refreshed nightly without interrupting the workflow of the analyst.
Q50. DRAG DROP - (Topic 9)
...
You are designing a self-service business intelligence and reporting environment.
Business analysts will create and publish PowerPivot for Microsoft Excel workbooks and create reports by using SQL Server Reporting Services (SSRS) and Power View. When the data models become more complex and the data volume increases, the data models will be replaced by IT-hosted server-based models.
You have the following requirements:
Maintain the self-service nature of the reporting environment.
Reuse existing reports.
Add calculated columns to the data models.
You need to create a strategy for implementing this process.
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.)
Answer: