Training Kit (Exam 70-463): Implementing a Data Warehouse with Microsoft SQL Server 2012

Training Kit (Exam 70-463): Implementing a Data Warehouse with Microsoft SQL Server 2012

by Dejan Sarka, Grega Jerkic, Matija Lah
Released December 2012
Publisher(s): Microsoft Press
ISBN: 9780735666092

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Ace your preparation for Microsoft® Certification Exam 70-463 with this 2-in-1 Training Kit from Microsoft Press®. Work at your own pace through a series of lessons and practical exercises, and then assess your skills with online practice tests—featuring multiple, customizable testing options.

Maximize your performance on the exam by learning how to:

  • Design and implement a data warehouse
  • Develop and enhance SQL Server Integration Services packages
  • Manage and maintain SQL Server Integration Services packages
  • Build data quality solutions
  • Implement custom code in SQL Server Integration Services packages

Publisher resources

View/Submit Errata

Table of contents

  1. Exam 70-463: Implementing a Data Warehouse with Microsoft® SQL Server® 2012: Training Kit
  2. Exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012
  3. Introduction
    1. System Requirements
      1. SQL Server and Other Software Requirements
      2. Hardware and Operating System Requirements
      3. Data Requirements
    2. Using the Companion CD
      1. How to Install the Practice Tests
      2. How to Use the Practice Tests
      3. How to Uninstall the Practice Tests
    3. Acknowledgments
    4. Support & Feedback
      1. Errata
      2. We Want to Hear from You
      3. Stay in Touch
    5. Preparing for the Exam
  4. I. Designing and Implementing a Data Warehouse
    1. 1. Data Warehouse Logical Design
      1. Before You Begin
      2. Lesson 1: Introducing Star and Snowflake Schemas
        1. Reporting Problems with a Normalized Schema
        2. Star Schema
        3. Snowflake Schema
        4. Granularity Level
        5. Auditing and Lineage
          1. Practice: Reviewing the AdventureWorksDW2012 Internet Sales Schema
        6. Lesson Summary
        7. Lesson Review
      3. Lesson 2: Designing Dimensions
        1. Dimension Column Types
        2. Hierarchies
        3. Slowly Changing Dimensions
          1. Practice: Reviewing the AdventureWorksDW2012 Dimensions
        4. Lesson Summary
        5. Lesson Review
      4. Lesson 3: Designing Fact Tables
        1. Fact Table Column Types
        2. Additivity of Measures
        3. Additivity of Measures in SSAS
        4. Many-to-Many Relationships
          1. Practice: Reviewing the AdventureWorksDW2012 Fact Tables
        5. Lesson Summary
        6. Lesson Review
      5. Case Scenarios
        1. Case Scenario 1: A Quick POC Project
        2. Case Scenario 2: Extending the POC Project
          1. Interviews
          2. Questions
      6. Suggested Practices
        1. Analyze the AdventureWorksDW2012 Database Thoroughly
        2. Check the SCD and Lineage in the AdventureWorksDW2012 Database
      7. Answers
        1. Lesson 1
        2. Lesson 2
        3. Lesson 3
        4. Case Scenario 1
        5. Case Scenario 2
    2. 2. Implementing a Data Warehouse
      1. Before You Begin
      2. 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
      3. 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
      4. Lesson 3: Loading and Auditing Loads
        1. Using Partitions
        2. Data Lineage
          1. Practice: Performing Table Partitioning
        3. Lesson Summary
        4. Lesson Review
      5. Case Scenarios
        1. Case Scenario 1: Slow DW Reports
        2. Case Scenario 2: DW Administration Problems
      6. Suggested Practices
        1. Test Different Indexing Methods
        2. Test Table Partitioning
      7. Answers
        1. Lesson 1
        2. Lesson 2
        3. Lesson 3
        4. Case Scenario 1
        5. Case Scenario 2
  5. II. Developing SSIS Packages
    1. 3. Creating SSIS Packages
      1. Before You Begin
      2. Lesson 1: Using the SQL Server Import and Export Wizard
        1. Planning a Simple Data Movement
          1. Practice: Creating a Simple Data Movement
        2. Lesson Summary
        3. Lesson Review
      3. Lesson 2: Developing SSIS Packages in SSDT
        1. Introducing SSDT
          1. Practice: Getting Started with SSDT
        2. Lesson Summary
        3. Lesson Review
      4. Lesson 3: Introducing Control Flow, Data Flow, and Connection Managers
        1. Introducing SSIS Development
        2. Introducing SSIS Project Deployment
          1. Practice: Modifying an Existing Data Movement
        3. Lesson Summary
        4. Lesson Review
      5. Case Scenarios
        1. Case Scenario 1: Copying Production Data to Development
        2. Case Scenario 2: Connection Manager Parameterization
      6. Suggested Practices
        1. Use the Right Tool
        2. Account for the Differences Between Development and Production Environments
      7. Answers
        1. Lesson 1
        2. Lesson 2
        3. Lesson 3
        4. Case Scenario 1
        5. Case Scenario 2
    2. 4. Designing and Implementing Control Flow
      1. Before You Begin
      2. Lesson 1: Connection Managers
        1. Connection Manager Scope
        2. 32-Bit and 64-Bit Data Providers
        3. Parameterization
        4. Practice: Creating a Connection Manager
        5. Lesson Summary
        6. Lesson Review
      3. Lesson 2: Control Flow Tasks and Containers
        1. Planning a Complex Data Movement
        2. Tasks
          1. Data Preparation Tasks
          2. Workflow Tasks
          3. Data Movement Tasks
          4. SQL Server Administration Tasks
          5. SQL Server Maintenance Tasks
          6. Analysis Services Tasks
          7. The Script Task
          8. Custom Tasks
        3. Containers
          1. Practice: Determining the Control Flow
        4. Lesson Summary
        5. Lesson Review
      4. Lesson 3: Precedence Constraints
        1. Practice: Determining Precedence Constraints
        2. Lesson Summary
        3. Lesson Review
      5. Case Scenarios
        1. Case Scenario 1: Creating a Cleanup Process
        2. Case Scenario 2: Integrating External Processes
      6. Suggested Practices
        1. A Complete Data Movement Solution
      7. Answers
        1. Lesson 1
        2. Lesson 2
        3. Lesson 3
        4. Case Scenario 1
        5. Case Scenario 2
    3. 5. Designing and Implementing Data Flow
      1. Before You Begin
      2. Lesson 1: Defining Data Sources and Destinations
        1. Creating a Data Flow Task
        2. Defining Data Flow Source Adapters
          1. Adding a Data Flow Source Adapter by Using the Source Assistant
          2. Configuring the Data Flow Source Adapter
        3. Defining Data Flow Destination Adapters
          1. Configuring the Data Flow Destination Adapter
        4. SSIS Data Types
          1. Using Fast Parse
          2. Practice: Implementing Simple Data Flows
        5. Lesson Summary
        6. Lesson Review
      3. Lesson 2: Working with Data Flow Transformations
        1. Selecting Transformations
          1. Logical Row-Level Transformations
          2. Multi-Input and Multi-Output Transformations
          3. Multi-Row Transformations
          4. Advanced Data-Preparation Transformations
        2. Using Transformations
          1. Resolving Column References
          2. Practice: Using Data Flow Transformations
        3. Lesson Summary
        4. Lesson Review
      4. Lesson 3: Determining Appropriate ETL Strategy and Tools
        1. ETL Strategy
          1. ETL Architecture
        2. Lookup Transformations
          1. Using a Lookup Transformation
          2. Using the Cache Transform Transformation with the Lookup Transformation
        3. Sorting the Data
        4. Set-Based Updates
          1. Practice: Enhancing Data Flow Transformations
        5. Lesson Summary
        6. Lesson Review
      5. Case Scenario
        1. Case Scenario: New Source System
      6. Suggested Practices
        1. Create and Load Additional Tables
      7. Answers
        1. Lesson 1
        2. Lesson 2
        3. Lesson 3
        4. Case Scenario
  6. III. Enhancing SSIS Packages
    1. 6. Enhancing Control Flow
      1. Before You Begin
      2. Lesson 1: SSIS Variables
        1. System and User Variables
        2. Variable Data Types
        3. Variable Scope
        4. Property Parameterization
          1. Practice: Creating a User Variable and Parameterizing a Task
        5. Lesson Summary
        6. Lesson Review
      3. Lesson 2: Connection Managers, Tasks, and Precedence Constraint Expressions
        1. Expressions
        2. Property Expressions
        3. Precedence Constraint Expressions
          1. Practice: Using Expressions to Parameterize SSIS Objects
        4. Lesson Summary
        5. Lesson Review
      4. Lesson 3: Using a Master Package for Advanced Control Flow
        1. Separating Workloads, Purposes, and Objectives
        2. Harmonizing Workflow and Configuration
        3. The Execute Package Task
        4. The Execute SQL Server Agent Job Task
        5. The Execute Process Task
          1. Practice: Creating and Configuring a Master Package
        6. Lesson Summary
        7. Lesson Review
      5. Case Scenarios
        1. Case Scenario 1: Complete Solutions
        2. Case Scenario 2: Data-Driven Execution
      6. Suggested Practices
        1. Consider Using a Master Package
      7. Answers
        1. Lesson 1
        2. Lesson 2
        3. Lesson 3
        4. Case Scenario 1
        5. Case Scenario 2
    2. 7. Enhancing Data Flow
      1. Before You Begin
      2. Lesson 1: Slowly Changing Dimensions
        1. Defining Attribute Types
        2. Inferred Dimension Members
        3. Using the Slowly Changing Dimension Task
        4. Effectively Updating Dimensions
          1. Checking Attribute Changes
          2. Set-Based Update Logic
          3. Practice: Implementing Slowly Changing Dimension Logic
        5. Lesson Summary
        6. Lesson Review
      3. Lesson 2: Preparing a Package for Incremental Load
        1. Using Dynamic SQL to Read Data
          1. Using the OLE DB Source Adapter
          2. Using the ODBC or ADO Net Source Adapter
        2. Implementing CDC by Using SSIS
          1. Enabling CDC on the Database
          2. SSIS CDC Components
        3. ETL Strategy for Incrementally Loading Fact Tables
          1. Practice: Implementing Change Data Capture (CDC) by Using SSIS
        4. Lesson Summary
        5. Lesson Review
      4. Lesson 3: Error Flow
        1. Using Error Flows
          1. Practice: Using Error Flow
        2. Lesson Summary
        3. Lesson Review
      5. Case Scenario
        1. Case Scenario: Loading Large Dimension and Fact Tables
      6. Suggested Practices
        1. Load Additional Dimensions
      7. Answers
        1. Lesson 1
        2. Lesson 2
        3. Lesson 3
        4. Case Scenario
    3. 8. Creating a Robust and Restartable Package
      1. Before You Begin
      2. Lesson 1: Package Transactions
        1. Defining Package and Task Transaction Settings
        2. Transaction Isolation Levels
        3. Manually Handling Transactions
          1. Practice: Implementing Transactions
        4. Lesson Summary
        5. Lesson Review
      3. Lesson 2: Checkpoints
        1. Implementing Restartability Checkpoints
          1. Practice: Setting and Observing Checkpoints in a Package
        2. Lesson Summary
        3. Lesson Review
      4. Lesson 3: Event Handlers
        1. Using Event Handlers
          1. Practice: Implementing Event Handlers
        2. Lesson Summary
        3. Lesson Review
      5. Case Scenario
        1. Case Scenario: Auditing and Notifications in SSIS Packages
      6. Suggested Practices
        1. Use Transactions and Event Handlers
      7. Answers
        1. Lesson 1
        2. Lesson 2
        3. Lesson 3
        4. Case Scenario
    4. 9. Implementing Dynamic Packages
      1. Before You Begin
      2. Lesson 1: Package-Level and Project-Level Connection Managers and Parameters
        1. Using Project-Level Connection Managers
        2. Parameters
          1. Using Parameters
          2. Defining Parameters
        3. Build Configurations in SQL Server 2012 Integration Services
          1. Creating Build Configurations
          2. Using Build Configurations
        4. Property Expressions
          1. Practice: Implementing Parameters
        5. Lesson Summary
        6. Lesson Review
      3. Lesson 2: Package Configurations
        1. Implementing Package Configurations
          1. Creating a Configuration
          2. Creating an XML File Configuration
          3. Creating a SQL Server Configuration
          4. Adding Properties to Your Configuration
          5. Sharing, Ordering, and Editing Your Configurations
          6. Practice: Using Package Configurations
        2. Lesson Summary
        3. Lesson Review
      4. Case Scenario
        1. Case Scenario: Making SSIS Packages Dynamic
      5. Suggested Practices
        1. Use a Parameter to Incrementally Load a Fact Table
      6. Answers
        1. Lesson 1
        2. Lesson 2
        3. Case Scenario
    5. 10. Auditing and Logging
      1. Before You Begin
      2. Lesson 1: Logging Packages
        1. Log Providers
          1. Selecting a Log Provider
        2. Configuring Logging
          1. Inheritance of Log Settings
          2. Log Configuration Templates
          3. Practice: Configuring SSIS Logging
        3. Lesson Summary
        4. Lesson Review
      3. Lesson 2: Implementing Auditing and Lineage
        1. Auditing Techniques
          1. Row Count Data Flow Transformation Component
          2. Aggregate Data Flow Transformation Component
          3. Audit Data Flow Transformation Component
        2. Correlating Audit Data with SSIS Logs
        3. Retention
          1. Practice: Implementing Elementary Auditing
        4. Lesson Summary
        5. Lesson Review
      4. Lesson 3: Preparing Package Templates
        1. SSIS Package Templates
          1. Creating an SSIS Package Template
          2. Using an SSIS Package Template
          3. Practice: Creating and Using an SSIS Package Template
        2. Lesson Summary
        3. Lesson Review
      5. Case Scenarios
        1. Case Scenario 1: Implementing SSIS Logging at Multiple Levels of the SSIS Object Hierarchy
        2. Case Scenario 2: Implementing SSIS Auditing at Different Levels of the SSIS Object Hierarchy
      6. Suggested Practices
        1. Add Auditing to an Update Operation in an Existing Execute SQL Task
        2. Create an SSIS Package Template in Your Own Environment
      7. Answers
        1. Lesson 1
        2. Lesson 2
        3. Lesson 3
        4. Case Scenario 1
        5. Case Scenario 2
  7. IV. Managing and Maintaining SSIS Packages
    1. 11. Installing SSIS and Deploying Packages
      1. Before You Begin
      2. Lesson 1: Installing SSIS Components
        1. Preparing an SSIS Installation
          1. Development vs. Production
          2. Hardware and Software Requirements
          3. Security Considerations
          4. 64-Bit Environments vs. 32-Bit Environments
        2. Installing SSIS
          1. Upgrading SSIS
          2. SSIS Tools
          3. Practice: Installing SSIS
        3. Lesson Summary
        4. Lesson Review
      3. Lesson 2: Deploying SSIS Packages
        1. SSISDB Catalog
          1. Configuring SSISDB
        2. SSISDB Objects
          1. Folders
          2. Projects and Packages
          3. Parameters
          4. Server Environments, Server Variables, and Server Environment References
          5. Operations
        3. Project Deployment
          1. Practice: Deploying SSIS Projects
        4. Lesson Summary
        5. Lesson Review
      4. Case Scenarios
        1. Case Scenario 1: Using Strictly Structured Deployments
        2. Case Scenario 2: Installing an SSIS Server
      5. Suggested Practices
        1. Upgrade Existing SSIS Solutions
      6. Answers
        1. Lesson 1
        2. Lesson 2
        3. Case Scenario 1
        4. Case Scenario 2
    2. 12. Executing and Securing Packages
      1. Before You Begin
      2. Lesson 1: Executing SSIS Packages
        1. On-Demand SSIS Execution
          1. SQL Server Management Studio
          2. DTExecUI
          3. Transact-SQL, Windows PowerShell, the SSIS Managed API, and DTExec
        2. Automated SSIS Execution
          1. SQL Server Agent
            1. Jobs and Job Steps
            2. Schedules
            3. SQL Server Agent Jobs vs. the Master Package Concept
        3. Monitoring SSIS Execution
          1. Operations
          2. Validations
          3. Executions
          4. Logging Levels
          5. SSIS Monitoring in SQL Server Management Studio
          6. Practice: Starting and Monitoring SSIS Processes
        4. Lesson Summary
        5. Lesson Review
      3. Lesson 2: Securing SSIS Packages
        1. SSISDB Security
          1. Principals
          2. Securables
          3. Permissions
            1. Permission Inheritance
            2. Default Permissions
          4. Practice: Managing SSISDB Permissions
        2. Lesson Summary
        3. Lesson Review
      4. Case Scenarios
        1. Case Scenario 1: Deploying SSIS Packages to Multiple Environments
        2. Case Scenario 2: Remote Executions
      5. Suggested Practices
        1. Improve the Reusability of an SSIS Solution
      6. Answers
        1. Lesson 1
        2. Lesson 2
        3. Case Scenario 1
        4. Case Scenario 2
    3. 13. Troubleshooting and Performance Tuning
      1. Before You Begin
      2. Lesson 1: Troubleshooting Package Execution
        1. Design-Time Troubleshooting
          1. Debugging the Control Flow with Breakpoints
          2. Using Data Viewers in the Data Flow
          3. Using Other Methods for Debugging
        2. Production-Time Troubleshooting
          1. Troubleshooting Packages by Using the SSISDB Catalog
          2. Using Data Taps
          3. Practice: Troubleshooting Packages
        3. Lesson Summary
        4. Lesson Review
      3. Lesson 2: Performance Tuning
        1. SSIS Data Flow Engine
          1. Data Buffer Architecture
          2. Transformation Types
          3. Execution Trees
          4. Backpressure Mechanism
        2. Data Flow Tuning Options
          1. Buffer Optimization
          2. Data Flow Tips for Performance
        3. Parallel Execution in SSIS
        4. Troubleshooting and Benchmarking Performance
          1. Using the SSISDB Catalog to Benchmark Package Performance
          2. Monitoring SSIS Performance with Performance Counters
          3. Practice: Observing Package Execution
        5. Lesson Summary
        6. Lesson Review
      4. Case Scenario
        1. Case Scenario: Tuning an SSIS Package
      5. Suggested Practice
        1. Get Familiar with SSISDB Catalog Views
      6. Answers
        1. Lesson 1
        2. Lesson 2
        3. Case Scenario
  8. V. Building Data Quality Solutions
    1. 14. Installing and Maintaining Data Quality Services
      1. Before You Begin
      2. Lesson 1: Data Quality Problems and Roles
        1. Data Quality Dimensions
          1. Completeness
          2. Accuracy
          3. Information
          4. Consistency
          5. Data Quality Soft Dimensions
          6. Data Quality Schema Dimensions
        2. Data Quality Activities and Roles
          1. Practice: Checking for Data Quality Issues
        3. Lesson Summary
        4. Lesson Review
      3. Lesson 2: Installing Data Quality Services
        1. DQS Architecture
        2. DQS Installation
          1. Practice: Installing Data Quality Services
        3. Lesson Summary
        4. Lesson Review
      4. Lesson 3: Maintaining and Securing Data Quality Services
        1. Performing Administrative Activities with Data Quality Client
        2. Performing Administrative Activities with Other Tools
          1. Practice: Monitoring DQS Activity
        3. Lesson Summary
        4. Lesson Review
      5. Case Scenario
        1. Case Scenario: Data Warehouse Not Used
      6. Suggested Practices
        1. Analyze the AdventureWorksDW2012 Database
        2. Review Data Profiling Tools
      7. Answers
        1. Lesson 1
        2. Lesson 2
        3. Lesson 3
        4. Case Scenario
    2. 15. Implementing Master Data Services
      1. Before You Begin
      2. Lesson 1: Defining Master Data
        1. What Is Master Data?
        2. Master Data Management
        3. MDM Challenges
          1. Practice: Defining Master Data
        4. Lesson Summary
        5. Lesson Review
      3. Lesson 2: Installing Master Data Services
        1. Master Data Services Architecture
        2. MDS Installation
          1. Practice: Installing Master Data Services
        3. Lesson Summary
        4. Lesson Review
      4. Lesson 3: Creating a Master Data Services Model
        1. MDS Models and Objects in Models
        2. MDS Objects
          1. Practice: Creating an MDS Model
        3. Lesson Summary
        4. Lesson Review
      5. Case Scenarios
        1. Case Scenario 1: Introducing an MDM Solution
        2. Case Scenario 2: Extending the POC Project
          1. Interviews
          2. Questions
      6. Suggested Practices
        1. Analyze the AdventureWorks2012 Database
        2. Expand the MDS Model
      7. Answers
        1. Lesson 1
        2. Lesson 2
        3. Lesson 3
        4. Case Scenario 1
        5. Case Scenario 2
    3. 16. Managing Master Data
      1. Before You Begin
      2. Lesson 1: Importing and Exporting Master Data
        1. Creating and Deploying MDS Packages
        2. Importing Batches of Data
        3. Exporting Data
          1. Practice: Importing and Exporting Master Data
        4. Lesson Summary
        5. Lesson Review
      3. Lesson 2: Defining Master Data Security
        1. Users and Permissions
        2. Overlapping Permissions
          1. Practice: Defining MDS Security
        3. Lesson Summary
        4. Lesson Review
      4. Lesson 3: Using Master Data Services Add-in for Excel
        1. Editing MDS Data in Excel
        2. Creating MDS Objects in Excel
          1. Practice: Using the MDS Add-in for Excel
        3. Lesson Summary
        4. Lesson Review
      5. Case Scenario
        1. Case Scenario: Editing Batches of MDS Data
      6. Suggested Practices
        1. Analyze the Staging Tables
        2. Test Security
      7. Answers
        1. Lesson 1
        2. Lesson 2
        3. Lesson 3
        4. Case Scenario
    4. 17. Creating a Data Quality Project to Clean Data
      1. Before You Begin
      2. Lesson 1: Creating and Maintaining a Knowledge Base
        1. Building a DQS Knowledge Base
        2. Domain Management
          1. Practice: Creating a Knowledge Base
        3. Lesson Summary
        4. Lesson Review
      3. Lesson 2: Creating a Data Quality Project
        1. DQS Projects
        2. Data Cleansing
          1. Practice: Creating a DQS Project
        3. Lesson Summary
        4. Lesson Review
      4. Lesson 3: Profiling Data and Improving Data Quality
        1. Using Queries to Profile Data
        2. SSIS Data Profiling Task
          1. Practice: Using the SSIS Data Profiling Task
        3. Lesson Summary
        4. Lesson Review
      5. Case Scenario
        1. Case Scenario: Improving Data Quality
      6. Suggested Practices
        1. Create an Additional Knowledge Base and Project
      7. Answers
        1. Lesson 1
        2. Lesson 2
        3. Lesson 3
        4. Case Scenario
  9. VI. Advanced SSIS and Data Quality Topics
    1. 18. SSIS and Data Mining
      1. Before You Begin
      2. Lesson 1: Data Mining Task and Transformation
        1. What Is Data Mining?
        2. SSAS Data Mining Algorithms
        3. Using Data Mining Predictions in SSIS
          1. Practice: Using Data Mining Predictions in SSIS
        4. Lesson Summary
        5. Lesson Review
      3. Lesson 2: Text Mining
        1. Term Extraction
        2. Term Lookup
          1. Practice: Performing Text Mining
        3. Lesson Summary
        4. Lesson Review
      4. Lesson 3: Preparing Data for Data Mining
        1. Preparing the Data
        2. SSIS Sampling
          1. Practice: Performing Random Sampling
        3. Lesson Summary
        4. Lesson Review
      5. Case Scenario
        1. Case Scenario: Preparing Data for Data Mining
      6. Suggested Practices
        1. Test the Row Sampling and Conditional Split Transformations
      7. Answers
        1. Lesson 1
        2. Lesson 2
        3. Lesson 3
        4. Case Scenario
    2. 19. Implementing Custom Code in SSIS Packages
      1. Before You Begin
      2. Lesson 1: Script Task
        1. Configuring the Script Task
        2. Coding the Script Task
          1. Practice: Using the Script Task to Read Data Profiling Results
        3. Lesson Summary
        4. Lesson Review
      3. Lesson 2: Script Component
        1. Configuring the Script Component
        2. Coding the Script Component
          1. Practice: Using the Data Profiling Results to Process Data Flow Rows
        3. Lesson Summary
        4. Lesson Review
      4. Lesson 3: Implementing Custom Components
        1. Planning a Custom Component
        2. Developing a Custom Component
        3. Design Time and Run Time
        4. Design-Time Methods
          1. ProvideComponentProperties
          2. Validate
          3. ReinitializeMetaData
          4. FireError, FireWarning, and FireInformation
        5. Run-Time Methods
          1. AcquireConnections (for Validation)
          2. Validate
          3. ReleaseConnections (after Validation)
          4. PrepareForExecute
          5. AcquireConnections (for Execution)
          6. PreExecute
          7. PrimeOutput
          8. ProcessInput
          9. PostExecute
          10. ReleaseConnections (after Execution)
          11. Cleanup
          12. Practice: Designing, Deploying, and Using a Custom Data Flow Component
        6. Lesson Summary
        7. Lesson Review
      5. Case Scenario
        1. Case Scenario: Data Cleansing
      6. Suggested Practices
        1. Create a Web Service Source
      7. Answers
        1. Lesson 1
        2. Lesson 2
        3. Lesson 3
        4. Case Scenario
    3. 20. Identity Mapping and De-Duplicating
      1. Before You Begin
      2. Lesson 1: Understanding the Problem
        1. Identity Mapping and De-Duplicating Problems
        2. Solving the Problems
          1. Practice: Preparing the Data
        3. Lesson Summary
        4. Lesson Review
      3. Lesson 2: Using DQS and the DQS Cleansing Transformation
        1. DQS Cleansing Transformation
        2. DQS Matching
          1. Practice: Using the DQS Cleansing Transformation and DQS Matching
        3. Lesson Summary
        4. Lesson Review
      4. Lesson 3: Implementing SSIS Fuzzy Transformations
        1. Fuzzy Transformations Algorithm
        2. Versions of Fuzzy Transformations
          1. Practice: Using the SSIS Fuzzy Lookup Transformation and MDS Add-in for Excel with the DQS Matching KB
        3. Lesson Summary
        4. Lesson Review
      5. Case Scenario
        1. Case Scenario: Improving Data Quality
      6. Suggested Practices
        1. Research More on Matching
      7. Answers
        1. Lesson 1
        2. Lesson 2
        3. Lesson 3
        4. Case Scenario
  10. A. About the Authors
  11. Index
  12. About the Authors
  13. Copyright

Product information

  • Title: Training Kit (Exam 70-463): Implementing a Data Warehouse with Microsoft SQL Server 2012
  • Author(s): Dejan Sarka, Grega Jerkic, Matija Lah
  • Release date: December 2012
  • Publisher(s): Microsoft Press
  • ISBN: 9780735666092