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

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

HOTSPOT
You are developing a SQL Server Analysis Services (SSAS) cube. A dimension named
Category is based on the DimCategory table.
A subset of the data source view is shown in the following graphic.

You need to relate the Category dimension to the Sales measure group.
Which relationship type should you choose?
To answer, select the appropriate option from the drop-down list in the dialog box.

You are designing a partitioning strategy for a large fact table in a Manufacturing data
warehouse.
Tens of millions of new inventory fact records are loaded into the data warehouse weekly,
outside of business hours. Most queries against the database are generated by reports and
by cube processing. Data is frequently queried at the day level and occasionally at the
month level.

A SQL Server Analysis Services (SSAS) cube contains a large measure group. The fact
table supporting the measure group is loaded with new data throughout the day.
You have the following requirements:
Ensure that the cube displays current data as quickly as possible.
Maximize availability of the cube.
Maximize query performance for all aggregation levels.
You need to choose a partitioning strategy that meets the requirements.
Which partitioning strategy should you choose? (More than one answer choice may achieve
the goal. Select the BEST answer.)

You are designing an extract, transform, load (ETL) process for loading data from a SQL
Azure database into a large fact table in a data warehouse each day with the prior day’s
sales data.
The ETL process for the fact table must meet the following requirements:
Load new data in the shortest possible time.
Remove data that is more than 36 months old.
Minimize record locking.
Minimize impact on the transaction log.
You need to design an ETL process that meets the requirements.
What should you do? (More than one answer choice may achieve the goal. Select the BEST
answer.)

DRAG DROP
You are validating whether a SQL Server Integration Services (SSIS) package named
Master.dtsx in the SSIS catalog is executing correctly.
You need to display the number of rows in each buffer passed between each data flow
component of the package.
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.)