Exam 70-463 · Implementing a Data Warehouse With Microsoft® SQL Server® 2012 · Training Kit by Dejan Sarka, Matija Lah -- Read -- Imperial Library of Trantor

Data Preparation Tasks Workflow Tasks Data Movement Tasks SQL Server Administration Tasks SQL Server Maintenance Tasks Analysis Services Tasks The Script Task Custom Tasks

Containers

Practice: Determining the Control Flow

Lesson Summary Lesson Review

Lesson 3: Precedence Constraints

Practice: Determining Precedence Constraints Lesson Summary Lesson Review

Case Scenarios

Case Scenario 1: Creating a Cleanup Process Case Scenario 2: Integrating External Processes

Suggested Practices

A Complete Data Movement Solution

Answers

Lesson 1 Lesson 2 Lesson 3 Case Scenario 1 Case Scenario 2

5. Designing and Implementing Data Flow

Before You Begin Lesson 1: Defining Data Sources and Destinations

Creating a Data Flow Task Defining Data Flow Source Adapters

Adding a Data Flow Source Adapter by Using the Source Assistant Configuring the Data Flow Source Adapter

Defining Data Flow Destination Adapters

Configuring the Data Flow Destination Adapter

SSIS Data Types

Using Fast Parse Practice: Implementing Simple Data Flows

Lesson Summary Lesson Review

Lesson 2: Working with Data Flow Transformations

Selecting Transformations

Logical Row-Level Transformations Multi-Input and Multi-Output Transformations Multi-Row Transformations Advanced Data-Preparation Transformations

Using Transformations

Resolving Column References Practice: Using Data Flow Transformations

Lesson Summary Lesson Review

Lesson 3: Determining Appropriate ETL Strategy and Tools

ETL Strategy

ETL Architecture

Lookup Transformations

Using a Lookup Transformation Using the Cache Transform Transformation with the Lookup Transformation

Sorting the Data Set-Based Updates

Practice: Enhancing Data Flow Transformations

Lesson Summary Lesson Review

Case Scenario

Case Scenario: New Source System

Suggested Practices

Create and Load Additional Tables

Answers

Lesson 1 Lesson 2 Lesson 3 Case Scenario

III. Enhancing SSIS Packages

6. Enhancing Control Flow

Before You Begin Lesson 1: SSIS Variables

System and User Variables Variable Data Types Variable Scope Property Parameterization

Practice: Creating a User Variable and Parameterizing a Task

Lesson Summary Lesson Review

Lesson 2: Connection Managers, Tasks, and Precedence Constraint Expressions

Expressions Property Expressions Precedence Constraint Expressions

Practice: Using Expressions to Parameterize SSIS Objects

Lesson Summary Lesson Review

Lesson 3: Using a Master Package for Advanced Control Flow

Separating Workloads, Purposes, and Objectives Harmonizing Workflow and Configuration The Execute Package Task The Execute SQL Server Agent Job Task The Execute Process Task

Practice: Creating and Configuring a Master Package

Lesson Summary Lesson Review

Case Scenarios

Case Scenario 1: Complete Solutions Case Scenario 2: Data-Driven Execution

Suggested Practices

Consider Using a Master Package

Answers

Lesson 1 Lesson 2 Lesson 3 Case Scenario 1 Case Scenario 2

7. Enhancing Data Flow

Before You Begin Lesson 1: Slowly Changing Dimensions

Defining Attribute Types Inferred Dimension Members Using the Slowly Changing Dimension Task Effectively Updating Dimensions

Checking Attribute Changes Set-Based Update Logic Practice: Implementing Slowly Changing Dimension Logic

Lesson Summary Lesson Review

Lesson 2: Preparing a Package for Incremental Load

Using Dynamic SQL to Read Data

Using the OLE DB Source Adapter Using the ODBC or ADO Net Source Adapter

Implementing CDC by Using SSIS

Enabling CDC on the Database SSIS CDC Components

ETL Strategy for Incrementally Loading Fact Tables

Practice: Implementing Change Data Capture (CDC) by Using SSIS

Lesson Summary Lesson Review

Lesson 3: Error Flow

Using Error Flows

Practice: Using Error Flow

Lesson Summary Lesson Review

Case Scenario

Case Scenario: Loading Large Dimension and Fact Tables

Suggested Practices

Load Additional Dimensions

Answers

Lesson 1 Lesson 2 Lesson 3 Case Scenario

8. Creating a Robust and Restartable Package

Before You Begin Lesson 1: Package Transactions

Defining Package and Task Transaction Settings Transaction Isolation Levels Manually Handling Transactions

Practice: Implementing Transactions

Lesson Summary Lesson Review

Lesson 2: Checkpoints

Implementing Restartability Checkpoints

Practice: Setting and Observing Checkpoints in a Package

Lesson Summary Lesson Review

Lesson 3: Event Handlers

Using Event Handlers

Practice: Implementing Event Handlers

Lesson Summary Lesson Review

Case Scenario

Case Scenario: Auditing and Notifications in SSIS Packages

Suggested Practices

Use Transactions and Event Handlers

Answers

Lesson 1 Lesson 2 Lesson 3 Case Scenario

9. Implementing Dynamic Packages

Before You Begin Lesson 1: Package-Level and Project-Level Connection Managers and Parameters

Using Project-Level Connection Managers Parameters

Using Parameters Defining Parameters

Build Configurations in SQL Server 2012 Integration Services

Creating Build Configurations Using Build Configurations

Property Expressions

Practice: Implementing Parameters

Lesson Summary Lesson Review

Lesson 2: Package Configurations

Implementing Package Configurations

Creating a Configuration Creating an XML File Configuration Creating a SQL Server Configuration Adding Properties to Your Configuration Sharing, Ordering, and Editing Your Configurations Practice: Using Package Configurations

Lesson Summary Lesson Review

Case Scenario

Case Scenario: Making SSIS Packages Dynamic

Suggested Practices

Use a Parameter to Incrementally Load a Fact Table

Answers

Lesson 1 Lesson 2 Case Scenario

10. Auditing and Logging

Before You Begin Lesson 1: Logging Packages

Log Providers

Selecting a Log Provider

Configuring Logging

Inheritance of Log Settings Log Configuration Templates Practice: Configuring SSIS Logging

Lesson Summary Lesson Review

Lesson 2: Implementing Auditing and Lineage

Auditing Techniques

Row Count Data Flow Transformation Component Aggregate Data Flow Transformation Component Audit Data Flow Transformation Component

Correlating Audit Data with SSIS Logs Retention

Practice: Implementing Elementary Auditing

Lesson Summary Lesson Review

Lesson 3: Preparing Package Templates

SSIS Package Templates

Creating an SSIS Package Template Using an SSIS Package Template Practice: Creating and Using an SSIS Package Template

Lesson Summary Lesson Review

Case Scenarios

Case Scenario 1: Implementing SSIS Logging at Multiple Levels of the SSIS Object Hierarchy Case Scenario 2: Implementing SSIS Auditing at Different Levels of the SSIS Object Hierarchy

Suggested Practices

Add Auditing to an Update Operation in an Existing Execute SQL Task Create an SSIS Package Template in Your Own Environment

Answers

Lesson 1 Lesson 2 Lesson 3 Case Scenario 1 Case Scenario 2

IV. Managing and Maintaining SSIS Packages

11. Installing SSIS and Deploying Packages

Before You Begin Lesson 1: Installing SSIS Components

Preparing an SSIS Installation

Development vs. Production Hardware and Software Requirements Security Considerations 64-Bit Environments vs. 32-Bit Environments

Installing SSIS

Upgrading SSIS SSIS Tools Practice: Installing SSIS

Lesson Summary Lesson Review

Lesson 2: Deploying SSIS Packages

SSISDB Catalog

Configuring SSISDB

SSISDB Objects

Folders Projects and Packages Parameters Server Environments, Server Variables, and Server Environment References Operations

Project Deployment

Practice: Deploying SSIS Projects

Lesson Summary Lesson Review

Case Scenarios

Case Scenario 1: Using Strictly Structured Deployments Case Scenario 2: Installing an SSIS Server

Suggested Practices

Upgrade Existing SSIS Solutions

Answers

Lesson 1 Lesson 2 Case Scenario 1 Case Scenario 2

12. Executing and Securing Packages

Before You Begin Lesson 1: Executing SSIS Packages

On-Demand SSIS Execution

SQL Server Management Studio DTExecUI Transact-SQL, Windows PowerShell, the SSIS Managed API, and DTExec

Automated SSIS Execution

SQL Server Agent

Jobs and Job Steps Schedules SQL Server Agent Jobs vs. the Master Package Concept

Monitoring SSIS Execution

Operations Validations Executions Logging Levels SSIS Monitoring in SQL Server Management Studio Practice: Starting and Monitoring SSIS Processes

Lesson Summary Lesson Review

Lesson 2: Securing SSIS Packages

SSISDB Security

Principals Securables Permissions

Permission Inheritance Default Permissions

Practice: Managing SSISDB Permissions

Lesson Summary Lesson Review

Case Scenarios

Case Scenario 1: Deploying SSIS Packages to Multiple Environments Case Scenario 2: Remote Executions

Suggested Practices

Improve the Reusability of an SSIS Solution

Answers

Lesson 1 Lesson 2 Case Scenario 1 Case Scenario 2

13. Troubleshooting and Performance Tuning

Before You Begin Lesson 1: Troubleshooting Package Execution

Design-Time Troubleshooting

Debugging the Control Flow with Breakpoints Using Data Viewers in the Data Flow Using Other Methods for Debugging

Production-Time Troubleshooting

Troubleshooting Packages by Using the SSISDB Catalog Using Data Taps Practice: Troubleshooting Packages

Lesson Summary Lesson Review

Lesson 2: Performance Tuning

SSIS Data Flow Engine

Data Buffer Architecture Transformation Types Execution Trees Backpressure Mechanism

Data Flow Tuning Options

Buffer Optimization Data Flow Tips for Performance

Parallel Execution in SSIS Troubleshooting and Benchmarking Performance

Using the SSISDB Catalog to Benchmark Package Performance Monitoring SSIS Performance with Performance Counters Practice: Observing Package Execution

Lesson Summary Lesson Review

Case Scenario

Case Scenario: Tuning an SSIS Package

Suggested Practice

Get Familiar with SSISDB Catalog Views

Answers

Lesson 1 Lesson 2 Case Scenario

V. Building Data Quality Solutions

14. Installing and Maintaining Data Quality Services

Before You Begin Lesson 1: Data Quality Problems and Roles

Data Quality Dimensions

Completeness Accuracy Information Consistency Data Quality Soft Dimensions Data Quality Schema Dimensions

Data Quality Activities and Roles

Practice: Checking for Data Quality Issues

Lesson Summary Lesson Review

Lesson 2: Installing Data Quality Services

DQS Architecture DQS Installation

Practice: Installing Data Quality Services

Lesson Summary Lesson Review

Lesson 3: Maintaining and Securing Data Quality Services

Performing Administrative Activities with Data Quality Client Performing Administrative Activities with Other Tools

Practice: Monitoring DQS Activity

Lesson Summary Lesson Review

Case Scenario

Case Scenario: Data Warehouse Not Used

Suggested Practices

Analyze the AdventureWorksDW2012 Database Review Data Profiling Tools

Answers

Lesson 1 Lesson 2 Lesson 3 Case Scenario

15. Implementing Master Data Services

Before You Begin Lesson 1: Defining Master Data

What Is Master Data? Master Data Management MDM Challenges

Practice: Defining Master Data

Lesson Summary Lesson Review

Lesson 2: Installing Master Data Services

Master Data Services Architecture MDS Installation

Practice: Installing Master Data Services

Lesson Summary Lesson Review

Lesson 3: Creating a Master Data Services Model

MDS Models and Objects in Models MDS Objects

Practice: Creating an MDS Model

Lesson Summary Lesson Review

Case Scenarios

Case Scenario 1: Introducing an MDM Solution Case Scenario 2: Extending the POC Project

Interviews Questions

Suggested Practices

Analyze the AdventureWorks2012 Database Expand the MDS Model

Answers

Lesson 1 Lesson 2 Lesson 3 Case Scenario 1 Case Scenario 2

16. Managing Master Data

Before You Begin Lesson 1: Importing and Exporting Master Data

Creating and Deploying MDS Packages Importing Batches of Data Exporting Data

Practice: Importing and Exporting Master Data

Lesson Summary Lesson Review

Lesson 2: Defining Master Data Security

Users and Permissions Overlapping Permissions

Practice: Defining MDS Security

Lesson Summary Lesson Review

Lesson 3: Using Master Data Services Add-in for Excel

Editing MDS Data in Excel Creating MDS Objects in Excel

Practice: Using the MDS Add-in for Excel

Lesson Summary Lesson Review

Case Scenario

Case Scenario: Editing Batches of MDS Data

Suggested Practices

Analyze the Staging Tables Test Security

Answers

Lesson 1 Lesson 2 Lesson 3 Case Scenario

17. Creating a Data Quality Project to Clean Data

Before You Begin Lesson 1: Creating and Maintaining a Knowledge Base

Building a DQS Knowledge Base Domain Management

Practice: Creating a Knowledge Base

Lesson Summary Lesson Review

Lesson 2: Creating a Data Quality Project

DQS Projects Data Cleansing

Practice: Creating a DQS Project

Lesson Summary Lesson Review

Lesson 3: Profiling Data and Improving Data Quality

Using Queries to Profile Data SSIS Data Profiling Task

Practice: Using the SSIS Data Profiling Task

Lesson Summary Lesson Review

Case Scenario

Case Scenario: Improving Data Quality

Suggested Practices

Create an Additional Knowledge Base and Project

Answers

Lesson 1 Lesson 2 Lesson 3 Case Scenario

VI. Advanced SSIS and Data Quality Topics

18. SSIS and Data Mining

Before You Begin Lesson 1: Data Mining Task and Transformation

What Is Data Mining? SSAS Data Mining Algorithms Using Data Mining Predictions in SSIS

Practice: Using Data Mining Predictions in SSIS

Lesson Summary Lesson Review

Lesson 2: Text Mining

Term Extraction Term Lookup

Practice: Performing Text Mining

Lesson Summary Lesson Review

Lesson 3: Preparing Data for Data Mining

Preparing the Data SSIS Sampling

Practice: Performing Random Sampling

Lesson Summary Lesson Review

Case Scenario

Case Scenario: Preparing Data for Data Mining

Suggested Practices

Test the Row Sampling and Conditional Split Transformations

Answers

Lesson 1 Lesson 2 Lesson 3 Case Scenario

19. Implementing Custom Code in SSIS Packages

Before You Begin Lesson 1: Script Task

Configuring the Script Task Coding the Script Task

Practice: Using the Script Task to Read Data Profiling Results

Lesson Summary Lesson Review

Lesson 2: Script Component

Configuring the Script Component Coding the Script Component

Practice: Using the Data Profiling Results to Process Data Flow Rows

Lesson Summary Lesson Review

Lesson 3: Implementing Custom Components

Planning a Custom Component Developing a Custom Component Design Time and Run Time Design-Time Methods

ProvideComponentProperties Validate ReinitializeMetaData FireError, FireWarning, and FireInformation

Run-Time Methods

AcquireConnections (for Validation) Validate ReleaseConnections (after Validation) PrepareForExecute AcquireConnections (for Execution) PreExecute PrimeOutput ProcessInput PostExecute ReleaseConnections (after Execution) Cleanup Practice: Designing, Deploying, and Using a Custom Data Flow Component

Lesson Summary Lesson Review

Case Scenario

Case Scenario: Data Cleansing

Suggested Practices

Create a Web Service Source

Answers

Lesson 1 Lesson 2 Lesson 3 Case Scenario

20. Identity Mapping and De-Duplicating

Before You Begin Lesson 1: Understanding the Problem

Identity Mapping and De-Duplicating Problems Solving the Problems

Practice: Preparing the Data

Lesson Summary Lesson Review

Lesson 2: Using DQS and the DQS Cleansing Transformation

DQS Cleansing Transformation DQS Matching

Practice: Using the DQS Cleansing Transformation and DQS Matching

Lesson Summary Lesson Review

Lesson 3: Implementing SSIS Fuzzy Transformations

Fuzzy Transformations Algorithm Versions of Fuzzy Transformations

Practice: Using the SSIS Fuzzy Lookup Transformation and MDS Add-in for Excel with the DQS Matching KB

Lesson Summary Lesson Review

Case Scenario

Case Scenario: Improving Data Quality

Suggested Practices

Research More on Matching

Answers

Lesson 1 Lesson 2 Lesson 3 Case Scenario

A. About the Authors Index About the Authors Copyright

← Prev
Back
Next →

← Prev
Back
Next →