You are designing a data warehouse for a software distribution business that stores sales by software
title. It stores sales targets by software category. Software titles are classified into subcategories and
categories. Each software title is included in only a single software subcategory, and each subcategory is
included in only a single category. The data warehouse will be a data source for an Analysis Services
cube.
The data warehouse contains two fact tables:
 factSales, used to record daily sales by software title
 factTarget, used to record the monthly sales targets by software
category
Reports must be developed against the warehouse that reports sales by software title, category and
subcategory, and sales targets.
You need to design the software title dimension. The solution should use as few tables as possible while
supporting all the requirements.
What should you do?

You are designing a data warehouse hosted on SQL Azure. The data warehouse currently includes the
dimUser and dimDistrict dimension tables and the factSales fact table. The dimUser table contains
records for each user permitted to run reports against the warehouse; and the dimDistrict table contains
information about sales districts.
The system is accessed by users from certain districts, as well as by area supervisors and users from the
corporate headquarters.
You need to design a table structure to ensure that certain users can see sales data for only certain
districts. Some users must be permitted to see sales data from multiple districts.
What should you do?

You are reviewing the design of a customer dimension table in an existing data warehouse hosted on
SQL Azure.
The current dimension design does not allow the retention of historical changes to customer attributes
such as Postcode.
You need to redesign the dimension to enable the full historical reporting of changes to multiple
customer attributes including Postcode.
What should you do?

You are implementing the indexing strategy for a fact table in a data warehouse. The fact table is named
Quotes. The table has no indexes and consists of seven columns:
• [ID]
• [QuoteDate]
• [Open]
• [Close]
• [High]
• [Low]
• [Volume]
Each of the following queries must be able to use a columnstore index:
• SELECT AVG ([Close]) AS [AverageClose] FROM Quotes WHERE [QuoteDate] BETWEEN
‘20100101’ AND ‘20101231’.
• SELECT AVG([High] – [Low]) AS [AverageRange] FROM Quotes WHERE [QuoteDate]
BETWEEN ‘20100101’ AND ‘20101231’.
• SELECT SUM([Volume]) AS [SumVolume] FROM Quotes WHERE [QuoteDate] BETWEEN
‘20100101’ AND ‘20101231’.
You need to ensure that the indexing strategy meets the requirements. The strategy must also minimize
the number and size of the indexes.
What should you do?

You are designing an enterprise star schema that will consolidate data from three independent data
marts. One of the data marts is hosted on SQL Azure.
Most of the dimensions have the same structure and content. However, the geography dimension is
slightly different in each data mart.
You need to design a consolidated dimensional structure that will be easy to maintain while ensuring
that all dimensional data from the three original solutions is represented.
What should you do?

To facilitate the troubleshooting of SQL Server Integration Services (SSIS) packages, a logging
methodology is put in place.
The methodology has the following requirements:
• The deployment process must be simplified.
• All the logs must be centralized in SQL Server.
• Log data must be available via reports or T-SQL.
• Log archival must be automated.
You need to configure a logging methodology that meets the requirements while minimizing the amount
of deployment and development effort.
What should you do?

You are developing a SQL Server Integration Services (SSIS) project that copies a large amount of rows
from a SQL Azure database. The project uses the Package Deployment Model. This project is deployed to
SQL Server on a test server.
You need to ensure that the project is deployed to the SSIS catalog on the production server.
What should you do?

You are developing a SQL Server Integration Services (SSIS) package.
To process complex scientific data originating from a SQL Azure database, a custom task component is
added to the project.
You need to ensure that the custom component is deployed on a test environment correctly.
What should you do?

DRAG DROP
A SQL Server Integration Services (SSIS) project has been deployed to the SSIS catalog. The project
includes a project Connection Manager to connect to the data warehouse.The SSIS catalog includes two Environments:
• Development
• QA
Each Environment defines a single Environment Variable named ConnectionString of type string. The
value of each variable consists of the connection string to the development or QA data warehouses.
You need to be able to execute deployed packages by using either of the defined Environments.
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.)

You develop and deploy a SQL Server Integration Services (SSIS) package.
The package is stored in the file system.
You need to execute the package without importing it to the SSIS server.
What should you use to execute the package? (Each correct answer presents a complete solution.
Choose all that apply.)