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

DRAG DROP
You are developing a SQL Server Integration Services (SSIS) package that imports data into a data
warehouse.You are developing the part of the SSIS package that populates the ProjectDates dimension table. The
business key of the ProjectDates table is the ProjectName column.
The business user has given you the dimensional attribute behavior for each of the four columns in the
ProjectDates table:
• ExpectedStartDate – New values should be tracked over time.
• ActualStartDate – New values should not be accepted.
• ExpectedEndDate – New values should replace existing values.
• ActualEndDate – New values should be tracked over time.
You use the SSIS Slowly Changing Dimension Transformation.
You must configure the Change Type value for each source column.
Which Change Type values should you select? (To answer, drag the appropriate value from the list of
values to the correct location or locations in the answer area.)