You are reviewing the design of an existing fact table named factSales, which is loaded from a SQL Azure
database by a SQL Server Integration Services (SSIS) package each day. The fact table has approximately 1
billion rows and is dimensioned by product, sales date, and sales time of day

The database administrator is concerned about the growth of the database. Users report poor reporting
performance against this database. Reporting requirements have recently changed and the only remaining
report that uses this fact table reports sales by product name, sale month, and sale year. No other reports will
be created against this table. You need to reduce the report processing time and minimize the growth of the
database. What should you do?

You are designing a data warehouse for a software distribution business that stores sales by
software title. It stores sales targets by softwarecategory. Software titles are classified into
subcategories and categories. Each software title is included in only a single software subcategory,
and each subcategory is included in only a single category. The data warehouse will be a data source
for an Analysis Services cube. The data warehouse contains two fact tables:
factSales, used to record daily sales by softwaretitle
factTarget, used to record the monthly sales targets by software category
Reports must be developed against the warehouse that reports sales by software title, category and
subcategory, and sales targets. You need to design the software title dimension. The solution should
use as few tables as possible while supporting all the requirements. What should you do?

You are reviewing the design of a customer dimension table in an existing data warehouse hosted on
SQL Azure. The current dimension design does not allow the retention of historical changes to
customer attributes such as Postcode. You need to redesign the dimension to enable the full
historical reporting of changes to multiple customer attributes including Postcode. What should you
do?

You are designing an enterprise star schema that will consolidate data from three independent data
marts. One of the data marts is hosted on SQL Azure. Most of the dimensions have the same
structure and content. However, the geography dimension is slightly different in each data mart. You
need to design a consolidated dimensional structurethat will be easy to maintain while ensuring
that all dimensional data from the three original solutions is represented. What should you do?

You develop and deploy a SQL Server Integration Services (SSIS) package. The package is stored in
the file system. You need to execute the package without importing it to the SSIS server. What
should you use to execute the package? (Each correct answer presents a complete solution. Choose
all that apply.)

You work as a senior database administrator at ABC.com. The ABC.com network consists of a single domain
named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment.
You are running a training exercise for Microsoft SQL Server 2012 junior administrators. You are discussing the
use ofSlowly Changing Dimension Transformation Outputs. One of the output options causes Derived Column
transformations to create columns for the expired row and the current row indicators.
Which option is the output that causes this?

You work as a senior database developer at ABC.com.The ABC.com network consists of a single domain
named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment.
You are running a training exercise for Microsoft SQL Server 2012 junior developers. You are discussing the
ValidateExternalMetaData property for a data flow.
Which of the following represents the data type forthis property?

You work as a senior database administrator at ABC.com. The ABC.com network consists of a single domain
named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment.
You are hosting a training exercise for Microsoft SQL Server 2012 administrators. You are currently discussing
Multicast transformation.
Which of the following is TRUE with regards to Multicast transformation? (Choose two.)

You work as a database administrator at ABC.com. The ABC.com network consists of a single domain named
ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment.
You are in the process of creating a SQL Server Integration Services (SSIS) package. The package will be
configured to make use of the GenerateAndPersistNewIndex Fuzzy Lookup transformation option.
Which of the following is the purpose of this option?

You work as a database administrator at ABC.com. The ABC.com network consists of a single domain named
ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment.
You are in the process of creating a SQL Server Integration Services (SSIS) package. You have decided to
make use of Expression Task.
Which of the following is TRUE with regards to Expression Task?