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

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.
Ensure that the solution supports role-based security and partitions.
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.)

You are defining a named set by using Multidimensional Expressions (MDX) in a sales cube.
The cube includes a Customer dimension that contains a Geography hierarchy and a
Gender attribute hierarchy.
You need to return only the female customers in the Geography hierarchy.
Which set should you use? (More than one answer choice may achieve the goal. Select the
BEST answer.)

HOTSPOT
You are designing a SQL Server Integration Services (SSIS) package configuration strategy.
The package configuration must meet the following requirements:
Include multiple properties in a configuration.
Force packages to load all settings in the configuration.
Support Encrypting File System (EFS) formats.
You need to select the appropriate configuration.
Which configuration type should you use?
To answer, select the appropriate option from the drop-down list in the dialog box.

You are designing a SQL Server Integration Services (SSIS) solution that will load multiple
Online Transactional Processing (OLTP) data sources into a SQL Server data mart.
You have the following requirements:
Ensure that the process supports the creation of an exception report that details possible
duplicate key values, null ratios within columns, and column-length distributions of values.
Ensure that users can generate the exception report in an XML format.
Use the minimum development effort.
You need to design the SSIS solution to meet the requirements.
What should you do? (More than one answer choice may achieve the goal. Select the BEST
answer.)

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

HOTSPOT
You are setting role permissions for a SQL Server Analysis Service (SSAS) cube.
You plan to grant Read access for specific attribute members only to specific Active
Directory security groups.
You need to enter a Multidimensional Expressions (MDX) expression to configure the
attribute member access.
On which tab should you enter the MDX expression? To answer, select the appropriate tab
in the answer area.

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 redesigning a SQL Server Analysis Services (SSAS) database that contains a cube
named Sales. Before the initial deployment of the cube, partition design was optimized for
processing time. The cube currently includes five partitions named FactSalesl through
FactSales5. Each partition contains from 1 million to 2 million rows.
The FactSales5 partition contains the current year’s information. The other partitions contain
information from prior years; one year per partition. Currently, no aggregations are defined
on the partitions.
You remove fact rows that are more than five years old from the fact table in the data source
and configure query logs on the SSAS server.
Several queries and reports are running very slowly.
You need to optimize the partition structure and design aggregations to improve query
performance and minimize administrative overhead.
What should you do? (More than one answer choice may achieve the goal. Select the BEST
answer.)

HOTSPOT
A school stores information about teachers, students, classes, and enrollments in a
Windows Azure SQL Database database. The database includes a table that maps the user
IDs of teachers to the subjects they teach.
Teachers access reports in a SQL Server Reporting Services (SSRS) instance by using their
credentials.
You are developing a report that displays a table of class enrollments for a specific subject.
The report will prompt teachers to select from their mapped subjects. The table is based on
a dataset named ClassData. To minimize report execution time, the ClassData dataset has
been configured to cache all class enrollment data.
You need to ensure that the report parameter displays the correct subjects for each teacher.
Which item should you configure? To answer, select the appropriate item in the answer
area.