Microsoft 70-467 Exam Practice Questions (P. 5)

You are developing the database schema for a SQL Server Analysis Services (SSAS) BI Semantic Model (BISM). The BISM will be based on the schema displayed in the following graphic.

You have the following requirements:
✑ Ensure that queries of the data model correctly display average student age by class and average class level by student.
✑ Minimize development effort.
You need to design the data model.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

DRAG DROP -
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.)
Select and Place:

You are modifying a star schema data mart that feeds order data from a SQL Azure database into a SQL Server Analysis Services (SSAS) cube. The data mart contains two large tables that include flags and indicators for some orders. There are 100 different flag columns, each with 10 different indicator values. Some flags reuse indicators. The tables both have a granularity that matches the fact table.
You have the following requirements:
✑ Allow users to slice data by all flags and indicators.
✑ Modify the date dimension table to include a surrogate key of a numeric data type and add the surrogate key to the fact table.
✑ Use the most efficient design strategy for cube processing and queries.
You need to modify the schema.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

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

An existing cube dimension that has 30 attribute hierarchies is performing very poorly. You have the following requirements:
✑ Implement drill-down browsing.
✑ Reduce the number of attribute hierarchies but ensure that the information contained within them is available to users on demand.
✑ Optimize performance.
You need to redesign the cube dimension to meet the requirements.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)