You are implementing a SQL Server Integration Services (SSIS) package that loads data hosted in a SQL
Azure database into a data warehouse. The source system contains redundant or inconsistent data. When the
package finds invalid data, the row containing the invalid data must be omitted but it must also be written to a
text file for further analysis. You need to establish the best technique to log these invalid rows while keeping the
amount of development effort to a minimum. What should you do?

You are troubleshooting an existing SQL Server Integration Services (SSIS) package. On several occasions,
the package execution does not finish and no data seems to have been transferred. You need to ensure that
package logging occurs. Your solution must minimizedeployment and development efforts. What should you
do?

To facilitate the troubleshooting of SQL Server Integration Services (SSIS) packages, a logging methodology is
put in place. The methodology has the following requirements:
The deployment process must be simplified.
All the logs must be centralized in SQL Server.
Log data must be available via reports or T-SQL.
Log archival must be automated.
You need to configure a logging methodology that meets the requirements while minimizing the amount of
deployment and development effort. What should you do?

To ease the debugging of packages, you standardize the SQL Server Integration Services (SSIS) package
logging methodology. The methodology has the following requirements:
Centralized logging in SQL Server
Simple deployment
Availability of log information through reports or T-SQL
Automatic purge of older log entries
Configurable log details
You need to configure a logging methodology that meets the requirements while minimizing the amount of
deployment and development effort. What should you do?

A SQL Server Integration Services (SSIS) package ona computer is failing periodically in production. The
package was created less than one year ago and was deployed to the SSIS catalog. Sometimes the packageis
started on a SQL Agent schedule; sometimes the package is started manually by an SSIS developer by using
the Object Explorer in SQL Server Management Studio. You need to identify the authenticated user responsible
for starting the package each time it failed in thepast. Where can you find this information?

You maintain a SQL Server Integration Services (SSIS) package. The package was developed by using SQL
Server 2008 Business Intelligence Development Studio (BIDS). The package includes custom scripts that must
be upgraded. You need to upgrade the package to SQLServer 2012. Which tool should you use?

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?