You are implementing a SQL Server Integration Services (SSIS) package that imports Microsoft Excel
workbook data into a Windows Azure SQL Database database. The package has been deployed to a
production server that runs Windows Server 2008 R2 and SQL Server 2012.
The package fails when executed on the production server.
You need to ensure that the package can load the Excel workbook data without errors. You need to use
the least amount of administrative effort to achieve this goal.What should you do?

You are installing the Data Quality Server component of Data Quality Services.
You need to provision the hardware and install the software for the server that runs the Data Quality
Server.
You must ensure that the minimum Data Quality Server prerequisites are met.
What should you do?

HOTSPOT
You are developing a SQL Server Integration Services (SSIS) package.
The package control flow will contain many tasks. The tasks will execute consecutively and none will
execute more than once. Certain groups of tasks will share variable and transaction scope.
You need to group tasks together while enabling them to be collapsed and expanded as a group.
Which item should you use from the SSIS Toolbox? (To answer, select the appropriate item in the
answer area.)

HOTSPOT
You are developing a SQL Server Integration Services (SSIS) package to load data into a SQL Server 2012
database.
The name of the target instance is passed to the package via an Environment variable.
The Environment variable provides the server name to the Project parameter named ServerName.
The OLE DB Project Connection Manager has been parameterized.
You need to configure the Connection Manager property to accept the parameter.
Which property and parameter should you use? (To answer, configure the appropriate option or options
in the dialog box in the answer area.)

HOTSPOT
You are the Master Data Services (MDS) administrator at your company.
An existing user needs to integrate data from other systems into MDS.
You need to give the user permissions to integrate data.
Which user management menu item should you select? (To answer, configure the appropriate option or
options in the dialog box in the answer area.)

DRAG DROP
You administer a large and complex SQL Server Integration Services (SSIS) solution in the SSIS catalog.
You are instructed to execute a package by using PowerShell. You need to create the correct PowerShell
command.
How should you place the four code segments in sequence? (To answer, move the appropriate code
segments from the list of code segments to the answer area and arrange them in the correct order.)

You are designing a data warehouse that contains a customer dimension.
The customer dimension contains the original customer attributes as well as the most recent set of
updated attributes. When a customer is updated, the original customer attributes must be preserved.
The customer dimension design must:
Support a Type 3 Slowly Changing Dimension (SCD) process
Minimize the total storage consumed
You need to design a solution that meets these requirements.
What should you do?

You are designing a data warehouse hosted on Windows Azure SQL Database. The data warehouse
currently includes the dimUser and dimRegion dimension tables and the factSales fact table. The
dimUser table contains records for each user permitted to run reports against the warehouse, and the
dimRegion table contains information about sales regions.
The system is accessed by users from certain regions, 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
regions. Some users must be permitted to see sales data from multiple regions.
What should you do?

You are developing a SQL Server Integration Services (SSIS) package.
The package uses a data flow task to source data from a SQL Server database for loading into a
dimension table in a data warehouse.
You need to create a separate data flow path for data that has been modified since it was last
processed.
Which data flow components should you use to identify modified data? (Each correct answer presents a
complete solution. Choose all that apply.)

You are designing a data warehouse that uses SQL Server 2012.The data warehouse contains a table named factSales that stores product sales. The table has a
clustered index on the primary key, four foreign keys to dimension tables, and an aggregate column for
sales totals. All key columns use the INT data type, and the aggregate column uses the MONEY data
type.
You need to increase the speed of data retrieval from the factSales table.
Which index type should you add to the table?