You are developing a SQL Server Analysis Services (SSAS) cube.
You are writing the following Multidimensional Expressions (MDX) statement for use by a
calculated measure. The measure computes the sales amount for the same time period of the
previous year. (Line numbers are included for reference only.)

You need to complete the MDX statement.
Which MDX function should you use in line 03? To answer, select the appropriate MDX function in
the functions list.
Hot Area:

You are developing a SQL Server Analysis Services (SSAS) cube.
You need to reuse a measure group from a different database.
In SQL Server Data Tools (SSDT), 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.)
Select and Place:

You are developing a SQL Server PowerPivot model to monitor the rate of production of defective
products.
The PowerPivot model contains many tables. The FactProduction table is configured as shown in
the following diagram.

The model requires a key performance indicator (KPI) named Defective Units to measure the rate
of production of defective units. The rate of production of defective units is calculated by dividing
the sum of the Defects column by the sum of the Units column.
The KPI has the following requirements.
If the rate of production of defective products equals or exceeds 5% then the KPI must report a
status of off target (red).
If the rate of production of defective products is less than 5% and equals or exceeds 2% then the
KPI must report a status of slightly off target (yellow).
If the rate of production of defective products less than 2% then the KPI must report a status of
on target (green).
You need to develop the KPI to meet the requirements.
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.)
Select and Place:

You are designing a SQL Server Analysis Services (SSAS) cube that contains two measure
groups named Sales History and Current Sales.
The Sales History measure group has the following characteristics:
Data changes on a monthly basis.
The measure group contains a very high data volume.
Queries that use the measure group only reference aggregate data.
The measure group includes SUM, MIN, MAX, and COUNT aggregate functions.
The Current Sales measure group has the following characteristics:
Data changes frequently.
The measure group contains a low data volume.
Queries that use the measure group often reference non-aggregate data.
The measure group includes SUM, MIN, MAX, and COUNT aggregate functions.
You need to select a storage mode for each measure group. The solution must meet the following
requirements:
For the Sales History measure group, query performance must be optimized over data latency.
For the Current Sales measure group, data latency must be optimized over query performance.

What should you do? To answer, select the appropriate storage mode for each measure group in
the answer area.

Hot Area:

You have a database named DB1. DB1 contains three tables named FactSales, DimCustomer,
and DimEmployee. A sample from DimEmployee is shown in the following table.

A sample from DimCustomer is shown in the following table.

A sample from FactSales is shown in the following table.

You create a cube named Sales that uses the three tables. FactSales is used as a fact table,
DimCustomer is used to create a dimension named Customer, and DimEmployee is used to
create a dimension named Employee.
Users report the following issues:
When browsing the Customer dimension, the users see a blank member in the state hierarchy
under United Kingdom.
When browsing the Employee dimension, the sales data for Sales Managers and Sales
Supervisors is NOT displayed.
You need to resolve the issues by configuring the properties of the dimension attributes.
What should you do? To answer, select the appropriate value for each property in the answer
area.

Hot Area:

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

You manage a SQL Server Reporting Services (SSRS) instance in native mode. You are building
a shared dataset for your weekly performance reports. The shared dataset uses a data source that
is configured to use credentials that are stored in the Report Server.
You have a predefined shared schedule to perform cleanup and maintenance tasks for SSRS.
You need to enable caching on the shared dataset. You also need to use an existing shared
schedule to discard the cache.
Which four 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.)
Select and Place:

You are developing a SQL Server Analysis Services (SSAS) tabular project based on a Microsoft
Azure SQL Database database. The ProcessingOption property for the project is set to Do Not
Process.

Several calculated columns have been added to a table. The project has been deployed to the
production server.
You need to ensure that newly added data is processed on the production server.
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.)
Select and Place:

You are developing a SQL Server Analysis Services (SSAS) multidimensional project.
You identify that a dimension, which has a large number of attributes, is issuing a separate
(SELECT DISTINCT) query for each attribute even though they all come from a single database
table. The table does not have a large number of rows.
You need to configure the processing of the dimension to issue only a single SQL query to the
underlying database, and processing must continue if any errors are encountered.
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.)

Select and Place:

You are developing a SQL Server Analysis Services (SSAS) multidimensional project that is
configured to source data from a Microsoft Azure SQL Database 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.)
Select and Place: