Microsoft 70-470 Actual Free Exam Questions & Community Discussion

You need to configure permissions for the Customers dimension.
What should you do? (Each correct answer presents a complete solution. Choose all that apply.)

You have a data warehouse named DW1.
An OLAP cube named Cube1 uses DW1 as its data source. Cube1 uses the MOLAP storage mode. You create a SQL Server Integration Services (SSIS) package that updates DW1, and then
processes Cube1. The package executes each day at 06:00.
You need to configure the storage settings of Cube1. The solution must ensure that
aggregation processing is reduced as much as possible during cube querying.
What is the best storage setting you should you use for Cube1? More than one answer choice may achieve the goal. Select the BEST answer.

You have a SQL Server Reporting Services (SSRS) 2008 instance integrated with an Internet-facing Microsoft SharePoint 2010 farm. PerformancePoint Services and Excel Services are installed and configured.
You need to upgrade to SSRS 2012 with the minimum downtime and hardware requirements.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

You are designing a reporting solution that uses SQL Server Reporting Services (SSRS) in SharePoint integrated mode.
The reporting solution must meet the following requirements:
---
Allow report writers to reuse content between different reports. Allow report writers to modify reusable content in SharePoint. Retain version history for report content.
You need to choose a reporting method that meets the requirements.
What should you use? (More than one answer choice may achieve the goal. Select the BEST answer.)

HOTSPOT
You are using a new installation of SQL Server Reporting Services (SSRS) to create three sales reports that consume data from a stored procedure. The stored procedure is defined in a Windows Azure SQL Database.
All reports must pass USA to the Country parameter of the stored procedure. Users cannot change the Country report parameter value.
You need to configure the report parameter properties.
How should you configure the report parameter properties? To answer, select the appropriate setting or settings in the answer area.

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

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:
- The deployment model must support changing the content of connection strings by
using parameters at execution time.
- The deployment model must automatically log events to the SSISDB database.
- Maximize performance at execution time.
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.)

Topic 1, Scenario 1
General Background
You are the data architect for a company that uses SQL Server 2012 Enterprise Edition. You design data modeling and reporting solutions that are based on a sales data warehouse.
Background
The solutions will be deployed on the following servers:
- ServerA runs SQL Server Database Engine. ServerA is the data warehouse server.
- ServerB runs SQL Server Database Engine, SQL Server Analysis Services (SSAS) in multidimensional mode, and SQL Server Integration Services (SSIS).
- ServerC runs SSAS in tabular mode, SQL Server Reporting Services (SSRS) running in SharePoint mode, and Microsoft SharePoint 2010 Enterprise Edition with SP1.
The data warehouse schema currently contains the tables shown in the exhibit. (Click the Exhibit button.)

Business Requirements
The reporting solution must address the requirements of the sales team, as follows: Team members must be able to view standard reports from SharePoint. Team members must be able to perform ad-hoc analysis by using Microsoft Power View and Excel. Team members can have standard reports delivered to them on a schedule of their choosing. The standard reports: Will use a sales territory hierarchy for organizing data by region. Will be accessible from SharePoint.
The Excel ad-hoc reports:
Will use the same data store as the standard reports.
Will provide direct access to the data store for the sales team and a simplified view
for the executive team.
Technical Requirements
The standard reports must be based on an SSAS cube. The schema of the data warehouse on ServerA must be able to support the ability to slice the fact data by the following dates:
- Order date (OrderDateKey) Due date DueDateKey Ship date (ShipDateKey) design principles to minimize maintenance and complexity. StandardReports.
A hierarchy must be created that consists of the SalesTerritoryCountry and A key performance indicator (KPI) must be created that compares the total
Additions and modifications to the data warehouse schema must adhere to star schema The multidimensional and tabular models will be based on the data warehouse. The tabular and multidimensional models will be created by using SQL Server Data Tools (SSDT). The tabular project is named AdhocReports and the multidimensional project is named The cube design in the StandardReports project must define two measures for the unique count of sales territories (SalesTerritoryKey) and products (ProductKey). A deployment script that can be executed from a command-line utility must be created to deploy the StandardReports project to ServerB. The tabular model in the AdhocReports project must meet the following requirements: - SalesTerritoryRegion columns from the DimSalesTerritory table and the EmployeeName column from the DimEmployee table.
- quantity sold (OrderQuantity) to a threshold value of 1,000.
- A measure must be created to calculate day-over-day (DOD) sales by region based on order date.
SSRS on ServerC must be configured to meet the following requirements:
- It must use a single data source for the standard reports.
- It must allow users to create their own standard report subscriptions.
- The sales team members must be limited to only viewing and subscribing to reports in the Sales Reports library.
A week after the reporting solution was deployed to production, Marc, a salesperson, indicated that he has never received reports for which he created an SSRS subscription.
In addition, Marc reports that he receives timeout errors when running some reports on demand.
A\Batch 1
============================================================================
You need to identify the reports that produce the errors that Marc is receiving.
What should you do?

You are designing a SQL Server Reporting Services (SSRS) report based on a SQL Server Analysis Services (SSAS) cube.
The cube contains a Key Performance Indicator (KPI) to show if a salesperson's sales are off target, slightly off target, or on target.
You need to add a report item that visually displays the KPI status value as a red, yellow, or green circle.
Which report item should you add?

You need to ensure that the Sales measure in the Power View field list meets the requirements.
What should you do? (Each correct answer presents a part of the solution. Choose all that apply.)

You need to develop the executive reports.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

--- --
You are designing a fact table in a SQL Server database.
The fact table must meet the following requirements:
Include a columnstore index.
Allow users to choose up to 10 dimension tables and up to five facts at one time.
Maximize performance of queries that aggregate measures by using any of the 10
dimensions.
Support billions of rows.
Use the most efficient design strategy.
You need to design the fact table to meet the requirements.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

HOTSPOT
You are developing a SQL Server Analysis Services (SSAS) tabular project. The DirectQuery Mode property of the data model has been set to On. The current partition for the table is configured as the DirectQuery partition.
Data is loaded into a data mart throughout the day and must be available in the data model as soon as possible. The model must support querying by clients that issue both Data Analysis Expressions (DAX) queries and Multidimensional Expressions (MDX) queries. Clients issuing DAX queries must receive real-time query results.
You need to select the appropriate Query Mode property for deployment.
Which query mode should you select?
To answer, select the appropriate query mode from the drop-down list in the dialog box.

DRAG DROP
You are designing a SQL Server Analysis Services (SSAS) data model on a very large data warehouse.
The fact tables in the data warehouse contain terabytes of data in tens of billions of rows.
You must support the following features:
Complex attribute/column relationships
Advanced calculations in the data model definition
Advanced calculations using logic deployed in a custom assembly You need to choose the correct SSAS design strategy.
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.)
---