You are creating a SQL Server Master Data Services (MDS) model for a company. The source data for
the company is stored in a single table that contains the manager-to-subordinate relationships. You
need to create a hierarchy representing the organizational structure of the company. Which
hierarchy type should you use?

You are completing the installation of the Data Quality Server component of SQL Server Data Quality
Services (DQS). You need to complete the post-installation configuration. What should you do?

You are the data steward for a Business Intelligence project. You must identify duplicate rows stored
in a SQL Server table and output discoveries to a CSV file. A Data Quality Services (DQS) knowledge
base has been created to support this project. You need to produce the CSV file with the least
amount of development effort. 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 a data warehouse with two fact tables. The first table contains sales per month
and the second table contains orders per day. Referential integrity must be enforced declaratively.
You need to design a solution that can join a single time dimension to both fact tables. What should
you do?

DRAG DROP
You are building a SQL Server Integration Services (SSIS) package to load product data sourced from
a SQL Azure database to a data warehouse. Before the product data is loaded, you create a batch
record by using an Execute SQL task named Create Batch. After successfully loading the product
data, you use another Execute SQL task named Set Batch Success to mark the batch as successful.

You need to create and execute an Execute SQL task to mark the batch as failed if either the Create
Batch or Load Products task fails. Which three steps 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 designing a SQL Server Integration Services (SSIS) package. The package moves order-related
data to a staging table named Order. Every night the staging data is truncated and then all the recent
orders from the online store database are inserted into the staging table. Your package must meet
the following requirements:
• If the truncate operation fails, the package execution must stop and report an error.
• If the Data Flow task that moves the data to the staging table fails, the entire refresh
operation must be rolled back.
• For auditing purposes, a log entry must be entered in a SQL log table after each execution of
the Data Flow task.
The TransactionOption property for the package is set to Required. You need to design the package
to meet the requirements. How should you design the control flow for the package? (To answer,
drag the appropriate setting from the list of settings to the correct location or locations in the
answer area.)

DRAG DROP
You develop a SQL Server Integration Services (SSIS) project by using the Project Deployment model.
The project contains many packages. It is deployed on a server named Development!. The project
will be deployed to several servers that run SQL Server 2012. The project accepts one required
parameter. The data type of the parameter is a string. A SQL Agent job is created that will call the

master.dtsx package in the project. A job step is created for the SSIS package. The job must pass the
value of an SSIS Environment Variable to the project parameter. The value of the Environment
Variable must be configured differently on each server that runs SQL Server. The value of the
Environment Variable must provide the server name to the project parameter. You need to
configure SSIS on the Development1 server to pass the Environment Variable to the package. Which
four actions should you perform in sequence by using SQL Server Management Studio? (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 to insert new data into a data
mart. The package uses a Lookup transformation to find matches between the source and
destination. The data flow has the following requirements:
• New rows must be inserted.
• Lookup failures must be written to a flat file.
In the Lookup transformation, the setting for rows with no matching entries is set to Redirect rows
to no match output. You need to configure the package to direct data into the correct destinations.
How should you design the data flow outputs? (To answer, drag the appropriate transformation
from the list of answer options to the correct location in the answer area.)

You are editing a SQL Server Integration Services (SSIS) package that contains three Execute SQL
tasks and no other tasks. The three Execute SQL tasks modify products in staging tables in
preparation for a data warehouse load. The package and all three Execute SQL product tasks have
their TransactionOption property set to Supported. You need to ensure that if any of the three
Execute SQL product tasks fail, all three tasks will roll back their changes. What should you do?