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

You are designing a SQL Server Integration Services (SSIS) data flow to load sales transactions from a
source system into a data warehouse hosted on Windows Azure SQL Database. One of the columns in
the data source is named ProductCode.
Some of the data to be loaded will reference products that need special processing logic in the data
flow.
You need to enable separate processing streams for a subset of rows based on the source product code.
Which Data Flow transformation should you use?

You are designing a SQL Server Integration Services (SSIS) 2012 package that imports data from a
Windows Azure SQL Database database into a SQL Server database.
The SSIS package has the following requirements:
 Every night, a very large amount of data is imported into the staging
database.
 Package processing time must be minimized.
 The package must run on its own dedicated server when it is deployed
to production.
 Transaction log activity when data is imported must be minimized.
You need to design the package to meet the requirements.
Which destination component should you use?

You are administering SQL Server Integration Services (SSIS) permissions on a production server that
runs SQL Server 2012.
Quality Assurance (QA) testers in the company must have permission to perform the following tasks on
specific projects:
 View and validate projects and packages
 View Environments and Environment variables
 Execute packages
You need to grant the minimum possible privileges to the QA testers.
What should you do? (Each correct answer presents part of the 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?

You are designing a data warehouse that contains a product dimension.
The product dimension contains the original product attributes as well as the most recent set of
updated attributes. When a product is updated, the original product attributes must be preserved.
The product 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?