70-463 Implementing a Data Warehouse with Microsoft SQL Server 2012 - Practice Questions

Question 4 (Volume C)

HOTSPOT You are developing a SQL Server Integration Services (SSIS) package.

You use a Data Profiling task to examine the data from a source system. You need to establish:

The minimum and maximum dates for the datetime columns in the source data

The minimum, maximum, and average values for numeric columns in the source data

You need to use the appropriate profile type in the Data Profiling task.

Which profile type should you use? (To answer, select the appropriate profile type in the answer area.) Hot Area:

Answer is in the explanation below.

Question 5 (Volume C)

Reporting from a Star schema is simpler than reporting from a normalized online transactional processing (OLTP) schema.

What are the reasons for wanting simpler reporting? (Choose all that apply.)

Select all that apply, then click Submit answer.

○
A Star schema typically has fewer tables than a normalized schema. Therefore, queries are simpler because they require fewer joins.
○
A Star schema has better support for numeric data types than a normalized relational schema; therefore, it is easier to create aggregates.
○
There are specific Transact-SQL expressions that deal with Star schemas.
○
A Star schema is standardized and narrative; you can find the information you need for a report quickly.

Question 6 (Volume C)

You are developing a SQL Server Integration Services (SSIS) package.

The package uses a data flow task to source data from a SQL Server database for loading into a dimension table in a data warehouse.

You need to create a separate data flow path for data that has been modified since it was last processed.

Which two data flow components should you use to identify modified data? (Each correct answer presents a complete solution. Choose all that apply.)

Select all that apply, then click Submit answer.

○
Multicast
○
Data Conversion
○
Lookup
○
Slowly Changing Dimension
○
Aggregate

Reference / correct answer:

Lookup

Slowly Changing Dimension

C: Lookup Transformation

The transformation that looks up values in a reference table using an exact match.

D: Slowly Changing Dimension

The Slowly Changing Dimension transformation provides the following functionality for managing slowly changing dimensions:

Matching incoming rows with rows in the lookup table to identify new and existing rows.

Identifying incoming rows that contain changes when changes are not permitted.

Identifying inferred member records that require updating.

Identifying incoming rows that contain historical changes that require insertion of new records and the updating of expired records. Detecting incoming rows that contain changes that require the updating of existing records, including expired ones.

Note:

* SQL Server Integration Services provides three different types of data flow components: sources, transformations, and destinations. Sources extract data from data stores such as tables and views in relational databases, files, and Analysis Services databases. Transformations modify, summarize, and clean data. Destinations load data into data stores or create in-memory datasets.

References:

https://docs.microsoft.com/en-us/sql/integration-services/data-flow/transformations/lookup-transformation

https://docs.microsoft.com/en-us/sql/integration-services/data-flow/transformations/slowly-changing-dimension-transformation

70-463 – Implementing a Data Warehouse with Microsoft SQL Server 2012