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

You are designing a complex SQL Server Integration Services (SSIS) project that uses the Project
Deployment model.
The project will contain between 15 and 20 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?

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 performance tuning a SQL Server Integration Services (SSIS) package to load sales data from a
source system into a data warehouse that is hosted on Windows Azure SQL Database.
The package contains a data flow task that has seven source-to-destination execution trees.
Only three of the source-to-destination execution trees are running in parallel.
You need to ensure that all the execution trees run in parallel.
What should you do?

You develop a SQL Server Integration Services (SSIS) package in a project by using the Project
Deployment Model. It is regularly executed within a multi-step SQL Server Agent job.You make changes to the package that should improve performance.
You need to establish if there is a trend in the durations of the next 10 successful executions of the
package. You need to use the least amount of administrative effort to achieve this goal.
What should you do?

You are designing a SQL Server Integration Services (SSIS) package that uses the Fuzzy Lookup
transformation.
The reference data to be used in the transformation changes with every package execution.
Common language runtime (CLR) integration cannot be enabled on the SQL Server database where the
reference table is located.
You need to configure the Fuzzy Lookup transformation in the most efficient manner.
What should you do?

You are designing a data warehouse that uses SQL Server 2012. You are preparing to update the
contents of a fact table that contains a non-clustered columnstore index.
You need to run an update statement against the table.
What should you do first?

You are using SQL Server Data Tools to develop a SQL Server Integration Services (SSIS) project.
The first package that you create in this project contains a package connection that accesses a Microsoft
Excel file. Additional packages in the project must also access this file.You need to define and reuse the Excel file connection in all project packages.
What should you do?

Your company uses a proprietary encryption algorithm to secure sensitive data. A custom cryptographic
assembly was developed in Microsoft .NET and is used in several applications.A SQL Server Integration Services (SSIS) package is importing data into a Windows Azure SQL Database
database. Some of the data must be encrypted with the proprietary encryption algorithm.
You need to design the implementation strategy to meet the requirements while minimizing
development and deployment effort and maximizing data flow performance.
What should you do?

You have Master Data Services (MDS) in a SQL Server deployment. You need to deploy a model that
contains data to the MDS repository. What should you use?