You are designing a SQL Server Integration Services (SS1S) package that uploads a file to
a table named Orders in a SQL Azure database.
The company’s auditing policies have the following requirements:
An entry must be written to a dedicated SQL Server log table named OrderLog.
The entry must be written as soon as the file upload task completes.
You need to meet the company’s policy requirements. Which event handler should you use?

DRAG DROP
You are building a fact table in a data warehouse.
The table must have a columnstore index. The table cannot be partitioned.
You need to design the fact table and load it with data.
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.)

DRAG DROP
You are creating a sales data warehouse. When a product exists in the product dimension,
you update the product name. When a product does not exist, you insert a new record.
In the current implementation, the DimProduct table must be scanned twice, once for the
insert and again for the update. As a result, inserts and updates to the DimProduct table take
longer than expected.
You need to create a solution that uses a single command to perform an update and an
insert.
How should you use a MERGE T-SQL statement to accomplish this goal? (To answer, drag
the appropriate answer choice from the list of options to the correct location or locations in
the answer area.

You may need to drag the split bar between panes or scroll to view content.)

You are designing a data warehouse for a fresh food distribution business that stores sates
by individual product. It stores sales targets by product category. Products are classified into
subcategories and categories.
Each product is included in only a single product 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 product
factProductTarget, used to record the monthly sales targets by product category
Reports must be developed against the warehouse that reports product sales by product,
category and subcategory, and product sales targets.
You need to design the product dimension. The solution should use as few tables as
possible while supporting all the requirements.
What should you do?

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

You are reviewing the design of an existing fact table named factSales, which is loaded
incrementally from a SQL Azure database by a SQL Server Integration Services (SSIS)
package each hour. The fact table has approximately 4 billion rows and is dimensioned by
product, sales date, and sales time of day.

The database administrator is concerned about the rapid growth of the database and users
experience poor reporting performance against this database. Reporting requirements have
recently changed and the only remaining report that uses this fact table reports sales by
product name, sale month, and sale year. No other reports will be created against this table.
You need to reduce the report processing time and minimize the growth of the database.
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?

You are adding a new capability to several dozen SQL Server Integration Services (SSIS)
packages.
The new capability is not available as an SSIS task. Each package must be extended with
the same new capability.
You need to add the new capability to all the packages without copying the code between
packages.
What should you do?

You are creating a SQL Server Integration Services (SSIS) package to retrieve product data
from two different sources. One source is hosted in a SQL Azure database. Each source
contains products for different distributors.
Products for each distributor source must be combined for insertion into a single product
table destination.
You need to select the appropriate data flow transformation to meet this requirement.
Which transformation types should you use? (Each answer represents a complete solution.
Choose all that apply.)

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:
Test
Production
Each Environment defines a single Environment Variable named ConnectionString of type
string. The value of each variable consists of the connection string to the test or production
data warehouses.
You need 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.)