MCTS SSIS 2012 Implementing a Data Warehouse

2. Implementing a Data Warehouse

1. Lesson 1: Implementing Dimensions and Fact Tables
1. Creating a Data Warehouse Database
2. Implementing Dimensions
3. Implementing Fact Tables
1. Practice: Implementing Dimensions and Fact Tables
4. Lesson Summary
5. Lesson Review
2. Lesson 2: Managing the Performance of a Data Warehouse
1. Indexing Dimensions and Fact Tables
2. Indexed Views
1. Using Appropriate Query Techniques
3. Data Compression
4. Columnstore Indexes and Batch Processing
1. Practice: Loading Data and Using Data Compression and Columnstore Indexes
5. Lesson Summary
6. Lesson Review
3. Lesson 3: Loading and Auditing Loads
1. Using Partitions
2. Data Lineage
1. Practice: Performing Table Partitioning
3. Lesson Summary
4. Lesson Review
4. Case Scenarios
1. Case Scenario 1: Slow DW Reports
2. Case Scenario 2: DW Administration Problems
5. Suggested Practices
1. Test Different Indexing Methods
2. Test Table Partitioning

Lesson Summary

1. Lesson 1: Implementing Dimensions and Fact Tables

■■ In this lesson, you learned about implementing a data warehouse.

■■ For a data warehouse database, you should use the Simple recovery model.

■■ When creating a database, allocate enough space for data files and log files to prevent autogrowth of the files.

■■ Use surrogate keys in dimensions in which you expect SCD Type 2 changes.

■■ Use computed columns.

Lesson Review

1. Which database objects and object properties can you use for autonumbering?

(Choose all that apply.)

A. IDENTITY property

B. SEQUENCE object

C. PRIMARY KEY constraint

D. CHECK constraint

2. What columns do you add to a table to support Type 2 SCD changes? (Choose all that

apply.)

A. Member properties

B. Current row flag

C. Lineage columns

D. Surrogate key

3. What is an inferred member?

A. A row in a fact table added during dimension load

B. A row with aggregated values

C. A row in a dimension added during fact table load

D. A computed column in a fact table

Answers:

Lesson 1

1. Correct Answers: A and B

A. Correct: The IDENTITY property autonumbers rows.

B. Correct: You can use the new SQL Server 2012 SEQUENCE object for

autonumbering.

C. Incorrect: Primary keys are used to uniquely identify rows, not for

autonumbering.

D. Incorrect: Check constraints are used to enforce data integrity, not for

autonumbering.

2. Correct Answers: B and D

A. Incorrect: Member properties are dimension columns used for additional information

on reports only.

B. Correct: You need a current flag for denoting the current row when you implement

Type 2 SCD changes.

C. Incorrect: Lineage columns are used, as their name states, to track the lineage

information.

D. Correct: You need a new, surrogate key when you implement Type 2 SCD changes.

3. Correct Answer: C

A. Incorrect: You do not add rows to a fact table during dimension load.

B. Incorrect: You do not create rows with aggregated values.

C. Correct: A row in a dimension added during fact table load is called an inferred

member.

D. Incorrect: A computed column is just a computed column, not an inferred member.

2.Lesson 2: Managing the Performance of a Data Warehouse

■■ In this lesson, you learned how to optimize data warehouse query performance.

■■ In a DW, you should not use many nonclustered indexes.

■■ Use small, integer surrogate columns for clustered primary keys.

■■ Use indexed views.

■■ Use columnstore indexes and exploit batch processing.

Lesson Review

1. Which types of data compression are supported by SQL Server? (Choose all that apply.)

A. Bitmap

B. Unicode(my word it automatically applied when u choose row or page compression)

C. Row

D. Page

2. Which operators can benefit from batch processing? (Choose all that apply.)

A. Hash Join

B. Merge Join

C. Scan

D. Nested Loops Join

E. Filter

3. Why would you use indexed views? (Choose all that apply.)

A. To speed up queries that aggregate data

B. To speed up data load

C. To speed up selective queries

D. To speed up queries that involve multiple joins

Answers:

1. Correct Answers: B, C, and D

A. Incorrect: SQL Server does not support bitmap compression.

B. Correct: SQL Server supports Unicode compression. It is applied automatically

when you use either row or page compression.

C. Correct: SQL Server supports row compression.

D. Correct: SQL Server supports page compression.

2. Correct Answers: A, C, and E

A. Correct: Hash joins can use batch processing.

B. Incorrect: Merge joins do not use batch processing.

C. Correct: Scan operators can benefit from batch processing.

D. Incorrect: Nested loops joins do not use batch processing.

E. Correct: Filter operators use batch processing as well.

3. Correct Answers: A and D

A. Correct: Indexed views are especially useful for speeding up queries that aggregate

data.

B. Incorrect: As with any indexes, indexed views only slow down data load.

C. Incorrect: For selective queries, nonclustered indexes are more appropriate.

D. Correct: Indexed views can also speed up queries that perform multiple joins.

Lesson 3: Loading and Auditing Loads

■■ Table partitioning is extremely useful for large fact tables with columnstore indexes.

■■ Partition switch is a metadata operation only if an index is aligned with its base table.

■■ You can add lineage information to your dimensions and fact tables to audit changes

to your DW on a row level.

Lesson Review

1. The database object that maps partitions of a table to filegroups is called a(n)

A. Aligned index

B. Partition function

C. Partition column

D. Partition scheme

2. If you want to switch content from a nonpartitioned table to a partition of a partitioned

table, what conditions must the nonpartitioned table meet? (Choose all that apply.)

A. It must have the same constraints as the partitioned table.

B. It must have the same compression as the partitioned table.

C. It must be in a special PartitionedTables schema.

D. It must have a check constraint on the partitioning column that guarantees that all

of the data goes to exactly one partition of the partitioned table.

E. It must have the same indexes as the partitioned table.

3. Which of the following T-SQL functions is not very useful for capturing lineage

information?

A. APP_NAME()

B. USER_NAME()

C. DEVICE_STATUS()

D. SUSER_SNAME()

Test Table Partitioning

In order to understand table partitioning thoroughly, you should test it with aligned and

nonaligned indexes.

■■ Practice 1 Partition the FactInternetSales table in the AdventureWorkDW2012 sample

database. Create aligned nonclustered indexes on all foreign keys of the fact table

included in joins of the query from the previous practice. Run the query and check the

execution plan.

■■ Practice 2 Create nonaligned nonclustered indexes on all foreign keys of the fact

table included in joins of the query from the previous practice. Run the query and

check the execution plan again.

Answers:

1. Correct Answer: D

A. Incorrect: Aligned indexes are indexes with the same partitioning as their base

table.

B. Incorrect: The partition function does logical partitioning.

C. Incorrect: The partition column is the column used for partitioning.

D. Correct: The partition scheme does physical partitioning.

2. Correct Answers: A, B, D, and E

A. Correct: It must have the same constraints as the partitioned table.

B. Correct: It must have the same compression as the partitioned table.

C. Incorrect: There is no special schema for partitioned tables.

D. Correct: It must have a check constraint to guarantee that all data goes to a single

partition.

E. Correct: It must have the same indexes as the partitioned table.

3. Correct Answer: C

A. Incorrect: The APP_NAME() function can be useful for capturing lineage

information.

B. Incorrect: The USER_NAME() function can be useful for capturing lineage

information.

C. Correct: There is no DEVICE_STATUS() function in T-SQL.

D. Incorrect: The SUSER_SNAME() function can be useful for capturing lineage

information.