You deploy a PowerPivot workbook to a document library in a Microsoft SharePoint site.
Workbook data comes from two different sources: Source A and Source B.

The workbook contains three small lookup tables from Source A, and five tables with a total
of 20 million rows from Source B. Data from Source A is updated at 9:00 A.M. and data from
Source B is updated throughout the day.
You have the following requirements:
Refresh the PowerPivot workbook with updated lookup data as soon as possible.
Minimize load on the source systems and the SharePoint environment during business
hours.
Minimize user involvement in the data refresh process.
You enable automatic data refresh for the workbook.
You need to configure a data refresh schedule for the workbook that meets the
requirements.
What should you do? (More than one answer choice may achieve the goal. Select the BEST
answer.)

You need to design a data warehouse schema to support a multidimensional cube. The fact
table will contain multiple columns representing order dates, shipping dates, and billing
dates. The cube will contain a measure group based on the fact table.
What is the best design to achieve the goal? More than one answer choice may achieve the
goal. Select the BEST answer.

You are creating a product dimension table and a SQL Server Integration Services (SSIS)
package that will load the table.
You need to keep a history in the table of changes to the names of products.
Which three actions should you perform? Each correct answer presents part of the solution.

DRAG DROP
You are designing a business intelligence (BI) solution for a hotel booking system. Each
hotel booking may have more than one guest.
The BI solution contains a fact table named Booking, a factless fact table named
Booking_Guest and a dimension named Guest.
Booking contains an entry for each booking. Booking_Guest contains an entry for each
guest in each booking. Guest contains an entry for each guest.
You need to define the many-to-many relationship in SQL Server Analysis Services (SSAS)
for the hotel booking system.
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.

HOTSPOT
You are designing aggregations for a SQL Server Analysis Services (SSAS) cube.
You need to ensure that every aggregation includes the Category attribute.
Which option should you select? To answer, select the appropriate option button in the
answer area.

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

You have a data warehouse named DW1.
An OLAP cube named Cube1 uses DW1 as its data source. Cube1 uses the MOLAP
storage mode.
You create a SQL Server Integration Services (SSIS) package that updates DW1, and then
processes Cube1. The package executes each day at 06:00.
You need to configure the storage settings of Cube1. The solution must ensure that
aggregation processing is reduced as much as possible during cube querying.
What is the best storage setting you should you use for Cube1? More than one answer
choice may achieve the goal. Select the BEST answer.

HOTSPOT
You have an OLAP cube named Cube1. Cube1 has two partitions named partition1 and
partition2.
You need to configure the storage mode for each cube partition.
The solution must meet the following requirements:
Users must be able to retrieve aggregate data from partition1 as quickly as possible.
The processing time for partition1 and partition2 must be minimized.
The least possible amount of storage must be used for partition2.
What should you do? To answer, select the appropriate storage mode for each partition in
the answer area.

DRAG DROP
You install SQL Server Integration Services (SSIS) and develop an SSIS project to load a
data warehouse. The project defines 10 parameters that have different values for the
development, test and production environments. Some packages have additional
parameters defined.
You plan to deploy the SSIS packages to one server. The server will also host the
production data warehouse.
You need to deploy and configure the packages.
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.

The Sales database includes a table named Products that contains 42 columns, and two
tables that record all product sales transactions. The database does not store aggregated
data. The database is continually maintained to provide optimal indexing. The database
server is sized appropriately.
The Marketing department requests a delimited text file that includes seven fields containing
product information from the Products table.
You have the following requirements:
Display the ProductID in the first column. Organize the remaining columns alphabetically by
column heading.
Sort rows in ProductID order.
Aggregate product units sold.
Minimize the time required to return the results.
You create a SQL Server Integration Services (SSIS) package and add the appropriate
connection managers and a Data Flow task.
You need to develop the Data Flow task.
What should you add to the Data Flow task? (More than one answer choice may achieve the
goal. Select the BEST answer.)