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?

You are designing an Extract, Transform and Load (ETL) solution that loads data into
dimension tables. The ETL process involves many transformation steps.
You need to ensure that the design can provide:
Auditing information for compliance and business user acceptance
Tracking and unique identification of records for troubleshooting and error correction
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?

You are creating a Data Quality Services (DQS) solution. You must provide statistics on the
accuracy of the data.
You need to use DQS profiling to obtain the required statistics.
Which DQS activity 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 implementing a new SQL Server Integration Services (SSIS) 2012 package that
loads data from various flat files and a Windows Azure SQL Database database.
Daily transactions must be loaded into a staging database. All the SSIS tasks will use the
CurrentDate variable as the transaction date.
You need to set the CurrentDate variable to the current execution date when the package
starts. You need to achieve this goal by using the least amount of development effort.
What should you use to set the variable?

You are developing a SQL Server Integration Services (SSIS) package to implement an
incremental data load strategy. The package reads data from a source system that uses the
SQL Server change data capture (CDC) feature.
You have added a CDC Source component to the data flow to read changed data from the
source system.
You need to add a data flow transformation to redirect rows for separate processing of
insert, update, and delete operations.
Which data flow transformation should you use?

You are developing a SQL Server Integration Services (SSIS) package.
You need to design a package to change a variable value during package execution by
using the least amount of development effort.
What should you use?

You are designing a SQL Server Integration Services (SSIS) project that uses the Project
Deployment Model.
The project will contain 10 packages. All the packages must connect to the same data
source and destination.
You need to define and reuse the connection managers in all the packages by using the
least development effort.
What should you do?

HOTSPOT

You are developing a SQL Server Integration Services (SSIS) package to load data into a
SQL Server 2012 database.
The package is allowed to connect to only one database. An Environment variable contains
the name of the database.
The OLE DB project connection manager has been parameterized.
You need to configure the connection manager property to accept the value of the
Environment variable.
Which property should you use? (To answer, configure the appropriate option or options in
the dialog box in the answer area.)