You work in the Business Intelligence (BI) department of a multinational company.
To share its sales data between the various subsidiaries, the company has requested a new
corporate BI solution that meets the following requirements:
The solution must use SQL Server Analysis Services (SSAS) multidimensional or tabular
Business Intelligence Semantic Model (BISM).
The model must incrementally add 10 million fact rows of sales data per month.
The model must be translated to English, German, Chinese, or Spanish based on users’
locale.
The model must be able to contain the most recent 36 months of data, in order to let users
query the data.
You need to select the appropriate model type and partitioning strategy to meet the
requirements.
What should you do? (More than one answer choice may achieve the goal. Select the BEST
answer.)

A multidimensional SQL Server Analysis Services (SSAS) database will be tested next
week.
During the test period, users will access the database for 30 days. Multidimensional
Expressions (MDX) queries generated during the test period must represent the variety of
queries that will be used in the production environment.
After testing completes, you need to implement aggregations for every partition in the
solution while minimizing development effort. You need to ensure that the aggregations are
optimal.
What should you do? (More than one answer choice may achieve the goal. Select the BEST
answer.)

You are developing a SQL Server Analysis Services (SSAS) cube for the accounts
department.
You create a measure group named Exchange Rate that consists of measures pertaining to
currency exchange rates. One of the measures in this group is named Average Rate and it
will be used to report the average currency exchange rate over time.
Currently the AggregationFunction property for the Average Rate measure is set to Sum.
You need to ensure that Average Rate measure reports the average of the currency
exchange rate over time.
Which value should you select for the AggregationFunction property for the Average Rate
measure? To answer, select the appropriate setting in the answer area.

HOTSPOT
You are developing a SQL Server Analysis Services (SSAS) cube.
Revenue must be compared to a goal and described by a status and a trend. Revenue, goal,
status, and trend will be defined by Multidimensional Expressions (MDX) expressions.
You need to add the Revenue measure.
Which tab should you select? (To answer, select the appropriate tab in the answer area.)

You are designing a SQL Server Analysis Services (SSAS) cube based on a Microsoft Azure
SQL Database data warehouse.
You need to implement a degenerate dimension.
What should you do?

You are designing a SQL Server Analysis Services (SSAS) cube for the sales department at
your company.

The sales department has the following requirements for the cube:
Include a year-over-year (YOY) calculation
Include a month-over-month (MOM) calculation
You need to ensure that the calculations are implemented in the cube.
Which Multidimensional Expressions (MDX) function should you use?

DRAG DROP
You are using Multidimensional Expressions (MDX) to query a SQL Server Analysis
Services (SSAS) cube.
You need to compute the aggregate value of the 10 most-ordered produces in the Product
Categories hierarchy. The Product level is the lowest in the hierarchy.
Which functions should you use to complete the MDX query? (To answer, drag the
appropriate functions from the list of functions to the correct locations in the answer area.)

You are restructuring an existing cube. One of the measures in the cube is Amount. The
Sum aggregation function is used for the Amount measure. The cube includes a dimension
named Account and the dimension’s Type property is set to Accounts. The Account
dimension includes an account type attribute.
You need to ensure that the Amount measure aggregates correctly according to the account
type classification. Development effort must be minimized.
What should you do? (More than one answer choice may achieve the goal. Select the BEST
answer.)

You are developing a SQL Server Analysis Services (SSAS) cube.
You must create a four-level hierarchy for the employee dimension. Each level must be
associated with an attribute in the employee dimension table. Two thirds of the dimension
data contain values for all four attributes. The remainder of the dimension data contains
values for the first three of the four attributes only.
You need to create the hierarchy so that logically missing members will not be shown by the
reporting tool.
Which type of hierarchy should you create?

You are adding dimensions and a new measure group named Accounts Receivable to an
existing SQL Server Analysis Services (SSAS) cube.
Date is one of the existing dimensions in the SSAS database. The underlying fact table for
the measure group is associated with multiple dates, including InvoiceDate DueDate and
PaymentDate.
You need to ensure that users can slice the Accounts Receivable measures by InvoiceDate,
DueDate, and PaymentDate. You also need to ensure that the time required to process the
database is minimized.
What should you do? (More than one answer choice may achieve the goal. Select the BEST
answer.)