Table of Contents

Cover

Title

Dedication

About the Authors

Credits

Acknowledgments

Introduction

The Origins of Kettle

About This Book

How This Book Is Organized

Prerequisites

On the Website

Further Resources

Part I: Getting Started

Chapter 1: ETL Primer
1. OLTP versus Data Warehousing
2. What Is ETL?
3. ETL, ELT, and EII
4. Data Integration Challenges
5. ETL Tool Requirements
6. Summary
Chapter 2: Kettle Concepts
1. Design Principles
2. The Building Blocks of Kettle Design
3. Parameters and Variables
4. Visual Programming
5. Summary
Chapter 3: Installation and Configuration
1. Kettle Software Overview
2. Installation
3. Configuration
4. Summary
Chapter 4: An Example ETL Solution—Sakila
1. Sakila
2. Prerequisites and Some Basic Spoon Skills
3. The Sample ETL Solution
4. Summary

Part II: ETL

Chapter 5: ETL Subsystems
1. Introduction to the 34 Subsystems
2. Summary
Chapter 6: Data Extraction
1. Kettle Data Extraction Overview
2. Working with ERP and CRM Systems
3. Data Profiling
4. CDC: Change Data Capture
5. Delivering Data
6. Summary
Chapter 7: Cleansing and Conforming
1. Data Cleansing
2. Error Handling
3. Auditing Data and Process Quality
4. Deduplicating Data
5. Scripting
6. Summary
Chapter 8: Handling Dimension Tables
1. Managing Keys
2. Loading Dimension Tables
3. Slowly Changing Dimensions
4. More Dimensions
5. Summary
Chapter 9: Loading Fact Tables
1. Loading in Bulk
2. Dimension Lookups
3. Fact Table Handling
4. Summary
Chapter 10: Working with OLAP Data
1. OLAP Benefits and Challenges
2. Working with Mondrian
3. Working with XML/A Servers
4. Working with Palo
5. Summary

Part III: Management and Deployment

Chapter 11: ETL Development Lifecycle
1. Solution Design
2. Agile Development
3. Testing and Debugging
4. Documenting the Solution
5. Summary
Chapter 12: Scheduling and Monitoring
1. Scheduling
2. Monitoring
3. Summary
Chapter 13: Versioning and Migration
1. Version Control Systems
2. Kettle Metadata
3. Managing Repositories
4. Version Migration System
5. Summary
Chapter 14: Lineage and Auditing
1. Batch-Level Lineage Extraction
2. Lineage
3. Logging and Operational Metadata
4. Summary

Part IV: Performance and Scalability

Chapter 15: Performance Tuning
1. Transformation Performance: Finding the Weakest Link
2. Improving Transformation Performance
3. Improving Job Performance
4. Summary
Chapter 16: Parallelization, Clustering, and Partitioning
1. Multi-Threading
2. Using Carte as a Slave Server
3. Clustering Transformations
4. Partitioning
5. Summary
Chapter 17: Dynamic Clustering in the Cloud
1. Dynamic Clustering
2. Cloud Computing
3. EC2
4. Summary
Chapter 18: Real-Time Data Integration
1. Introduction to Real-Time ETL
2. Transformation Streaming
3. Summary

Part V: Advanced Topics

Chapter 19: Data Vault Management
1. Introduction to Data Vault Modeling
2. Do You Need a Data Vault?
3. Data Vault Building Blocks
4. Transforming Sakila to the Data Vault Model
5. Loading the Data Vault: A Sample ETL Solution
6. Updating a Data Mart from a Data Vault
7. Summary
Chapter 20: Handling Complex Data Formats
1. Non-Relational and Non-Tabular Data Formats
2. Non-Relational Tabular Formats
3. Semi- and Unstructured Data
4. Key/Value Pairs
5. Summary
Chapter 21: Web Services
1. Web Pages and Web Services
2. Data Formats
3. XML Examples
4. SOAP Examples
5. JSON Example
6. RSS
7. Summary
Chapter 22: Kettle Integration
1. The Kettle API
2. Executing Existing Transformations and Jobs
3. Embedding Kettle
4. OEM Versions and Forks
5. Summary
Chapter 23: Extending Kettle
1. Plugin Architecture Overview
2. Transformation Step Plugins
3. The User-Defined Java Class Step
4. Job Entry Plugins
5. Partitioning Method Plugins
6. Repository Type Plugins
7. Database Type Plugins
8. Summary

Appendix A: The Kettle Ecosystem

Kettle Development and Versions
The Pentaho Community Wiki
Using the Forums
Jira
##pentaho

Appendix B: Kettle Enterprise Edition Features

Appendix C: Built-in Variables and Properties Reference

Internal Variables
Kettle Variables
Variables for Configuring VFS
Noteworthy JRE Variables

Index

End User License Agreement

List of Tables

Chapter 1: ETL Primer

Table 1-1: OLTP versus Data Warehouse
Table 1-2: Virtual versus Physical Data Integration

Chapter 2: Kettle Concepts

Table 2-1: Date Conversion Examples
Table 2-2: A Few Numeric Conversion Mask Examples
Table 2-3: Other Data Type Conversions
Table 2-4: Expressions You Can Use in the JavaScript Job Entry
Table 2-5: Examples of VFS File Specifications

Chapter 6: Data Extraction

Table 6-1: CDC Option Comparison

Chapter 7: Cleansing and Conforming

Table 7-1: Gender Code Reference Table
Table 7-2: Duplicate Entries Example

Chapter 9: Loading Fact Tables

Table 9-1: A Slowly Changing Dimension
Table 9-2: A State-Oriented Fact Table
Table 9-3: Month Table
Table 9-4: Periodic Snapshot View

Chapter 12: Scheduling and Monitoring

Table 12-1: Generic Command-Line Parameters for Kitchen and Pan
Table 12-2: Command-Line Parameters Specific to Kitchen
Table 12-3: Command-Line Parameters Specific to Kitchen

Chapter 13: Versioning and Migration

Table 13-1: ETL Parameter Table
Table 13-2: Parameter Query Results
Table 13-3: Required Environment Variables Data Structure

Chapter 15: Performance Tuning

Table 15-1: Network Condition Effects on Latency

Chapter 16: Parallelization, Clustering, and Partitioning

Table 16-1: Slave Server Services

Chapter 19: Data Vault Management

Table 19-1: Hub Attributes
Table 19-2: Example hub_customer
Table 19-3: Link Attributes
Table 19-4: Example Link Table lnk_customer_store
Table 19-5: Satellite Attributes
Table 19-6: Example Satellite Table sat_customer
Table 19-7: Sakila Hubs
Table 19-8: Sakila Links
Table 19-9: Sakila Satellites

Chapter 22: Kettle Integration

Table 22-1: The Result Object

Chapter 23: Extending Kettle

Table 23-1: Java Primitives to Correspond with Metadata Types

Appendix C: Built-in Variables and Properties Reference

Table C-1: Internal Variables
Table C-2: Kettle Variables
Table C-3: Kettle Log Table Variables
Table C-4: Noteworthy JRE Variables

List of Illustrations

Introduction

Figure 1: ETL tool generations

Chapter 1: ETL Primer

Figure 1-1: Classic data warehouse architecture

Chapter 2: Kettle Concepts

Figure 2-1: A simple transformation example
Figure 2-2: A typical job that updates a data warehouse
Figure 2-3: Serial execution of multi-paths using back-tracking
Figure 2-4: Parallel execution of job entries
Figure 2-5: Two simultaneous sequences of job entries
Figure 2-6: Parallel load as part of a larger job
Figure 2-7: The Database Connection dialog
Figure 2-8: Reading information from a database cluster
Figure 2-9: Defining and using variables in a job
Figure 2-10: Defining named parameters
Figure 2-11: Using variables in Spoon
Figure 2-12: Creating a new transformation
Figure 2-13: An empty canvas
Figure 2-14: Finding CVS-related steps
Figure 2-15: A brand new step
Figure 2-16: The graphical slide-out menu
Figure 2-17: Configuring the “CSV file input” step
Figure 2-18: Adding the “Table output” step
Figure 2-19: Creating a new hop
Figure 2-20: Selecting the type of output
Figure 2-21: Configuring the “Table output” step

Chapter 3: Installation and Configuration

Figure 3-1: Overview of Kettle programs
Figure 3-2: Spoon
Figure 3-3: The tree view in View mode
Figure 3-4: Referencing variables set in the kettle.properties file

Chapter 4: An Example ETL Solution—Sakila

Figure 4-1: The Sakila sample database
Figure 4-2: The rental star schema
Figure 4-3: Examining the layout of incoming and outgoing streams
Figure 4-4: The load_dim_date transformation
Figure 4-5: The load_dim_time Transformation
Figure 4-6: The load_rentals job
Figure 4-7: The configuration dialog for the load_dim_store transformation job entry
Figure 4-8: The load_dim_staff transformation
Figure 4-9: The database connections in the sidebar
Figure 4-10: The Database Connection dialog for the sakila connection
Figure 4-11: The load_dim_customer transformation
Figure 4-12: The configuration of the Fetch Customer Address step
Figure 4-13: The load_dim_store transformation
Figure 4-14: The fetch_address transformation
Figure 4-15: The fetch_address transformation
Figure 4-16: The load_dim_film transformation
Figure 4-17: Configuration of the Denormalize Special Features to ‘Yes’ Columns step
Figure 4-18: The load_fact_rental transformation

Chapter 6: Data Extraction

Figure 6-1: Get customer files
Figure 6-2: Preview of Get File Names
Figure 6-3: Data Grid with directory name
Figure 6-4: Get file names
Figure 6-5: Parameterized query
Figure 6-6: Set Variables transformation
Figure 6-7: Table input with variable replacement
Figure 6-8: Variable-based “Table input” job
Figure 6-9: HTTP client preview
Figure 6-10: Business data layer
Figure 6-11: SAP/R3 connection
Figure 6-12: SAP Function Browser
Figure 6-13: SAP Input step
Figure 6-14: Generate Rows for SAP Input step parameters
Figure 6-15: Kettle transformation reading SAP data
Figure 6-16: SAP Billingdoc data
Figure 6-17: SAP Input step using RFC_READ_TABLE
Figure 6-18: SAP RFC_READ_TABLE input specification
Figure 6-19: Using the “Field splitter” to divide output fields into columns
Figure 6-20: RFC_READ_TABLE output showing G/L Accounts
Figure 6-21: Profiling task
Figure 6-22: Regex match of phone number
Figure 6-23: Profiler results
Figure 6-24: Profiler results
Figure 6-25: Snapshot versions
Figure 6-26: Snapshot compare result
Figure 6-27: Merge Rows settings
Figure 6-28: Kettle “Synchronize after merge” step
Figure 6-29: Kettle snapshot CDC solution
Figure 6-30: CDC results Preview

Chapter 7: Cleansing and Conforming

Figure 7-1: Source data to cleanse
Figure 7-2: Reference data
Figure 7-3: Stream lookup
Figure 7-4: Preview source and reference data
Figure 7-5: System name as constant
Figure 7-6: Passing a variable
Figure 7-7: Using the passed variable
Figure 7-8: Generic “Stream lookup”
Figure 7-9: Data to validate
Figure 7-10: Data validation transform
Figure 7-11: Date rule data validation
Figure 7-12: Data validation results
Figure 7-13: A running job
Figure 7-14: A failed job
Figure 7-15: Failed job metrics
Figure 7-16: Forcing a Failure return
Figure 7-17: Forcing a transformation failure
Figure 7-18: Aborting a job
Figure 7-19: Define error handling
Figure 7-20: Validation errors
Figure 7-21: Confusing error descriptions
Figure 7-22: Validation and correction
Figure 7-23: Preparing case statements
Figure 7-24: Data quality lifecycle
Figure 7-25: Creating error event data
Figure 7-26: Adding batch ID columns
Figure 7-27: Unique rows
Figure 7-28: “Unique rows” step
Figure 7-29: Unique rows preview
Figure 7-30: Fuzzy match basics
Figure 7-31: Preview multiple match data
Figure 7-32: “Fuzzy match” return fields
Figure 7-33: Possible duplicates
Figure 7-34: Possible duplicates
Figure 7-35: Possible duplicates
Figure 7-36: Determine file age using Formula steps
Figure 7-37: File age output
Figure 7-38: JavaScript for week calculation
Figure 7-39: Java Expressions in data cleansing
Figure 7-40: Regex Evaluation step
Figure 7-41: Regex Evaluation result

Chapter 8: Handling Dimension Tables

Figure 8-1: Configuring the “Add sequence” step to generate values within the transformation
Figure 8-2: Generating surrogate key values using the “Add Sequence” step and internal counters
Figure 8-3: Using the Calculator step to actually compute the new key values
Figure 8-4: Dynamically configuring the offset of the “Add sequence” step
Figure 8-5: The configuration of the Set Variables step
Figure 8-6: Using a variable reference to parameterize the sequence of the “Add sequence” step
Figure 8-7: Snowflaked dimensions
Figure 8-8: The (partial) sakila_snowflake schema
Figure 8-9: Loading an individual dimension table of a snowflaked dimension
Figure 8-10: The configuration of the “Database Lookup” step
Figure 8-11: The load_dim_location.kjb job ensures top-down level-wise loading of the snowflaked location dimension tables
Figure 8-12: The configuration of the Insert / Update step
Figure 8-13: The mapping dialog offers a simple wizard to specify from which fields values should be assigned to the database columns.
Figure 8-14: The configuration of the “Load dim_customer SCD” step of the load_dim_customer transformation
Figure 8-15: The configuration of the Fields tab page of the “Load dim_customer SCD” step
Figure 8-16: Generating a demography mini-dimension
Figure 8-17: A table with a recursive relationship and its closure table
Figure 8-18: The configuration of the Closure Generator step

Chapter 9: Loading Fact Tables

Figure 9-1: The Surrogate Key Pipeline
Figure 9-2: Using a “Stream lookup” step
Figure 9-3: Inside the “Stream lookup” step
Figure 9-4: “Database lookup” step
Figure 9-5: Late-arriving facts performance
Figure 9-6: Default lookup return value
Figure 9-7: Default lookup return value
Figure 9-8: Sample sales data
Figure 9-9: Output error fact rows
Figure 9-10: Add unknown customers
Figure 9-11: Adding N/A values
Figure 9-12: New customer added
Figure 9-13: Partitioned Accumulated Snapshot Table
Figure 9-14: Example SOF loading workflow

Chapter 10: Working with OLAP Data

Figure 10-1: 3-dimensional cube and time dimension
Figure 10-2: Positioning OLAP
Figure 10-3: Mondrian Input step
Figure 10-4: Mondrian output preview
Figure 10-5: Completed Mondrian transformation
Figure 10-6: Split Time step
Figure 10-7: Cleaned Mondrian output
Figure 10-8: OLAP Input step
Figure 10-9: XML/A query result set
Figure 10-10: MSAS query result set
Figure 10-11: MSAS cleaned results
Figure 10-12: Palo Modeler with Demo database
Figure 10-13: Palo Data files
Figure 10-14: Palo Dimension Input configuration
Figure 10-15: Palo Dimension Input preview
Figure 10-16: Palo Cells Input definition and preview
Figure 10-17: Palo data denormalised
Figure 10-18: Denormalised result set
Figure 10-19: Updating a Palo database
Figure 10-20: Update results in Open Office Calc
Figure 10-21: Adding a new Product category and Products
Figure 10-22: Adding budget values for new products

Chapter 11: ETL Development Lifecycle

Figure 11-1: Table output alter script
Figure 11-2: Sakila_dwh metadata
Figure 11-3: Job design
Figure 11-4: Agile BI initiative
Figure 11-5: Reservoir Sampling
Figure 11-6: Example Analyzer model
Figure 11-7: Example Analyzer report
Figure 11-8: Transformation log settings
Figure 11-9: Preview and Debug buttons
Figure 11-10: Using breakpoints
Figure 11-11: Paused transformation
Figure 11-12: Sniffing a running transformation
Figure 11-13: Using descriptive fields to document a job

Chapter 12: Scheduling and Monitoring

Figure 12-1: Capturing command-line parameters with the Get System Info step
Figure 12-2: The PDI_Inputs.xaction sample action sequence
Figure 12-3: The Scheduler tab in the Pentaho BI Server Administration Console
Figure 12-4: The Schedule Creator dialog
Figure 12-5: Inspecting the log from within the Spoon program
Figure 12-6: Configuring the log file location per job entry
Figure 12-7: Controlling the log level in Spoon
Figure 12-8: The Addresses tab page in the configuration dialog of the Mail job entry
Figure 12-9: The Server tab page in the configuration dialog of the Mail job entry
Figure 12-10: The EMail Message tab page in the configuration dialog of the Mail job entry
Figure 12-11: The Attached Files tab in the configuration dialog of the Mail job entry

Chapter 13: Versioning and Migration

Figure 13-1: Connecting to a repository
Figure 13-2: Managing repositories
Figure 13-3: The Kettle database repository dialog
Figure 13-4: Exporting a repository in a job
Figure 13-5: Row denormalizer step
Figure 13-6: Completed set variables transformation

Chapter 14: Lineage and Auditing

Figure 14-1: Extracting lineage information from a transformation
Figure 14-2: Retrieving the output fields of a step
Figure 14-3: Displaying database impact information
Figure 14-4: Externalizing database impact analysis
Figure 14-5: Setting the logging parameters
Figure 14-6: The transformation execution history
Figure 14-7: The transformation log table settings
Figure 14-8: The step logging table
Figure 14-9: Configuring the performance log table
Figure 14-10: Configuring the logging channels log table

Chapter 15: Performance Tuning

Figure 15-1: Reading and writing customer data
Figure 15-2: Taking a step out of the equation
Figure 15-3: The execution results showing improved performance
Figure 15-4: The step metrics 22 seconds into the execution of the transformation
Figure 15-5: The evolution of the speed of a step as a graph
Figure 15-6: The evolution of the output buffer size of a step
Figure 15-7: Copying rows of data to multiple target steps
Figure 15-8: A transformation that can stall
Figure 15-9: Solving a dead-lock by reading data twice
Figure 15-10: Distributing files to multiple step copies
Figure 15-11: Performance degradation caused by a missing index
Figure 15-12: The proper index ensures consistent performance
Figure 15-13: Sorting rows in parallel
Figure 15-14: Clustered sorting of data
Figure 15-15: Partitioning a Memory Group By step to spread the workload over multiple processors
Figure 15-16: Calculating a constant value many times
Figure 15-17: Calculating a constant value only once
Figure 15-18: A simple but slow job loop
Figure 15-19: The proper way to loop in a job
Figure 15-20: Enabling database connection pooling

Chapter 16: Parallelization, Clustering, and Partitioning

Figure 16-1: A simple transformation
Figure 16-2: Running a step in multiple copies
Figure 16-3: A warning dialog
Figure 16-4: Copying data to multiple target steps
Figure 16-5: Merging rows of data
Figure 16-6: Row redistribution
Figure 16-7: Row redistribution expanded
Figure 16-8: Redistribution can allocate a lot of buffers
Figure 16-9: Data pipelining
Figure 16-10: Data pipelining expanded
Figure 16-11: Making a transformation transactional
Figure 16-12: Execute SQL statements dialog
Figure 16-13: The Blocking Step
Figure 16-14: Updating dimensions in parallel
Figure 16-15: Defining a slave server
Figure 16-16: Creating a new cluster schema
Figure 16-17: The “Clustering schema” dialog
Figure 16-18: A regular transformation
Figure 16-19: A clustered transformation
Figure 16-20: Executing a clustered transformation from the “Execute a transformation” dialog
Figure 16-21: Execute clustered transformations with a job entry
Figure 16-22: A master transformation
Figure 16-23: A slave transformation
Figure 16-24: Tooltip information showing remote steps
Figure 16-25: Delivering data to a clustered step
Figure 16-26: A slave transformation with a reader
Figure 16-27: A master distributing data to slaves
Figure 16-28: Copying data to the slaves
Figure 16-29: Acquiring data on the slaves
Figure 16-30: The “Partitioning schema” dialog
Figure 16-31: Selecting the partitioning method
Figure 16-32: Selecting a partitioning schema
Figure 16-33: Specifying the field to partition on
Figure 16-34: A partitioning sample
Figure 16-35: Defining database partitions
Figure 16-36: Reading database partitions

Chapter 17: Dynamic Clustering in the Cloud

Figure 17-1: Creating a dynamic cluster schema
Figure 17-2: Steps to be executed by a dynamic cluster are marked as CxN
Figure 17-3: Passing a job export to a slave
Figure 17-4: Pass the export of a job to a slave.
Figure 17-5: Remote step row sniffing

Chapter 18: Real-Time Data Integration

Figure 18-1: Interval logging option
Figure 18-2: Continuously storing tweets in a database
Figure 18-3: Delay parameter
Figure 18-4: Sniff rows on a slave server

Chapter 19: Data Vault Management

Figure 19-1: The Sakila data skeleton
Figure 19-2: The complete Sakila data vault
Figure 19-3: The hub_actor transformation
Figure 19-4: The link_customer_store transformation
Figure 19-5: Load all hubs in parallel
Figure 19-6: The sat_actor transformation
Figure 19-7: The dim_actor transformation
Figure 19-8: The Insert / Update step
Figure 19-9: The dim_customer transformation
Figure 19-10: Calculate the version number
Figure 19-11: customer_version_number
Figure 19-12: The dim_film transformation
Figure 19-13: The dim_film_actor_bridge transformation
Figure 19-14: The fact_rental transformation

Chapter 20: Handling Complex Data Formats

Figure 20-1: The “Split field to rows” step
Figure 20-2: Using the Row Normaliser step to normalize repeating groups
Figure 20-3: Loading data from the IMDB movies list using regular expressions
Figure 20-4: The Settings tab page and Fields grid of the Regex Evaluation step
Figure 20-5: The Content tab page of the “Regex evaluation” step
Figure 20-6: Processing key/value pairs from IMDB’s laserdisc list
Figure 20-7: The “Mark Attribute rows with Id of header row” step
Figure 20-8: The “Row denormaliser” step

Chapter 21: Web Services

Figure 21-1: The mapping between the videos.xml file and the sakila sample database
Figure 21-2: The import_xml_into_db transformation
Figure 21-3: Validating the videos.xml document against the videos.xsd XML Schema
Figure 21-4: The File tab of the “Get data from XML” step
Figure 21-5: The Content tab of the “Get data from XML” step
Figure 21-6: The picklist invoked by the Get XPath Nodes button
Figure 21-7: The Fields tab of the “Get data from XML” step
Figure 21-8: Generating XML
Figure 21-9: The Content tab of the Video XML step
Figure 21-10: The Fields tab of the Video XML step
Figure 21-11: The configuration of the Join Actor XML step
Figure 21-12: Correlating source and target elements using complex joins in the “Join actorRef XML” step
Figure 21-13: The Web Services configuration dialog
Figure 21-14: The “in” tab
Figure 21-15: The results tab
Figure 21-16: Generate Rows with URL
Figure 21-17: Call web service
Figure 21-18: Examine preview data
Figure 21-19: Get XML data
Figure 21-20: Country code and name preview
Figure 21-21: The MQL query and result envelopes
Figure 21-22: The freebase-films transformation
Figure 21-23: Generate Rows
Figure 21-24: Invoke the Freebase read service
Figure 21-25: General tab of RSS Input
Figure 21-26: Content tab of RSS Input
Figure 21-27: Select fields
Figure 21-28: Transformation demonstrating RSS output
Figure 21-29: RSS Output source channel
Figure 21-30: RSS Output source item
Figure 21-31: RSS Output Channel tab
Figure 21-32: RSS Output Item tab
Figure 21-33: RSS Output File tab

Chapter 22: Kettle Integration

Figure 22-1: Pentaho Reporting using Kettle
Figure 22-2: Using the Injector step
Figure 22-3: A dynamically generated preview transformation

Chapter 23: Extending Kettle

Figure 23-1: Creating a new Java package
Figure 23-2: Selecting the .jar files for the project

Appendix A: The Kettle Ecosystem

Figure A-1: Forum feed subscription
Figure A-2: Jira Task Board
Figure A-3: Chatting with the community

Pentaho® Kettle Solutions: Building Open Source ETL Solutions with Pentaho Data Integration

Published by
Wiley Publishing, Inc.
10475 Crosspoint Boulevard
Indianapolis, IN 46256
www.wiley.com

Published simultaneously in Canada

ISBN: 978-0-470-63517-9

ISBN: 9780470942420 (ebk)

ISBN: 9780470947524 (ebk)

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at http://www.wiley.com/go/permissions.

Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or warranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including without limitation warranties of fitness for a particular purpose. No warranty may be created or extended by sales or promotional materials. The advice and strategies contained herein may not be suitable for every situation. This work is sold with the understanding that the publisher is not engaged in rendering legal, accounting, or other professional services. If professional assistance is required, the services of a competent professional person should be sought. Neither the publisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Web site is referred to in this work as a citation and/or a potential source of further information does not mean that the author or the publisher endorses the information the organization or Web site may provide or recommendations it may make. Further, readers should be aware that Internet Web sites listed in this work may have changed or disappeared between when this work was written and when it is read.

For general information on our other products and services please contact our Customer Care Department within the United States at (877) 762-2974, outside the United States at (317) 572-3993 or fax (317) 572-4002.

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books.

Library of Congress Control Number: 2010932421

Trademarks: Wiley and the Wiley logo are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other countries, and may not be used without written permission. Pentaho is a registered trademark of Pentaho, Inc. All other trademarks are the property of their respective owners. Wiley Publishing, Inc. is not associated with any product or vendor mentioned in this book.

About the Authors

Matt Casters has been an independent business intelligence consultant for many years and has implemented numerous data warehouses and BI solutions for large companies. For the last 8 years, Matt kept himself busy with the development of an ETL tool called Kettle. This tool was open sourced in December 2005 and acquired by Pentaho early in 2006. Since then, Matt took up the position of Chief Data Integration at Pentaho. His responsibility is to continue to be lead developer for Kettle. Matt tries to help the Kettle community in any way possible; he answers questions on the forum and speaks occasionally at conferences all around the world. He has a blog at http://www.ibridge.be and you can follow his @mattcasters account on Twitter.

Roland Bouman has been working in the IT industry since 1998 and is currently working as a web and business intelligence developer. Over the years he has focused on open source software, in particular database technology, business intelligence, and web development frameworks. He’s an active member of the MySQL and Pentaho communities, and a regular speaker at international conferences, such as the MySQL User Conference, OSCON and at Pentaho community events. Roland co-authored the MySQL 5.1. Cluster Certification Guide and Pentaho Solutions, and was a technical reviewer for a number of MySQL and Pentaho related book titles. He maintains a technical blog at http://rpbouman.blogspot.com and tweets as @rolandbouman on Twitter.

Jos van Dongen is a seasoned business intelligence professional and well-known author and presenter. He has been involved in software development, business intelligence, and data warehousing since 1991. Before starting his own consulting practice, Tholis Consulting, in 1998, he worked for a top tier systems integrator and a leading management consulting firm. Over the past years, he has successfully implemented BI and data warehouse solutions for a variety of organizations, both commercial and non-profit. Jos covers new BI developments for the Dutch Database Magazine and speaks regularly at national and international conferences. He authored one book on open source BI and is co-author of the book Pentaho Solutions. You can find more information about Jos on http://www.tholis.com or follow @josvandongen on Twitter.

Acknowledgments

This book is the result of the efforts of many individuals. By convention, authors receive explicit credit, and get to have their names printed on the book cover. But creating this book would not have been possible without a lot of hard work behind the scenes. We, the authors, would like to express our gratitude to a number of people that provided substantial contributions, and thus help define and shape the final result that is Pentaho Kettle Solutions.

First, we’d like to thank those individuals that contributed directly to the material that appears in the book:

Ingo Klose suggested an elegant solution to generate keys starting from a given offset within a single transformation (this solution is discussed in Chapter 8, “Handling Dimension Tables,” subsection “Generating Surrogate Keys Based on a Counter,” shown in Figure 8-2).
Samatar Hassan provided text as well as working example transformations to demonstrate Kettle’s RSS capabilities. Samatar’s contribution is included almost completely and appears in the RSS section of Chapter 21, “Web Services.”
Thanks to Mike Hillyer and the MySQL documentation team for creating and maintaining the Sakila sample database, which is introduced in Chapter 4 and appears in many examples throughout this book.
Although only three authors appear on the cover, there was actually a fourth one: We cannot thank Kasper de Graaf of DIKW-Academy enough for writing the Data Vault chapter, which has benefited greatly from his deep expertise on this subject. Special thanks also to Johannes van den Bosch who did a great job reviewing Kasper’s work and gave another boost to the overall quality and clarity of the chapter.
Thanks to Bernd Aschauer and Robert Wintner, both from Aschauer EDV (http://www.aschauer-edv.at/en), for providing the examples and screenshots used in the section dedicated to SAP of Chapter 6, “Data Extraction.”
Daniel Einspanjer of the Mozilla Foundation provided sample transformations for Chapter 7, “Cleansing and Conforming.”

Thanks for your contributions. This book benefited substantially from your efforts.

Much gratitude goes out to all of our technical reviewers. Providing a good technical review is hard and time-consuming, and we have been very lucky to find a collection of such talented and seasoned Pentaho and Kettle experts willing to find some time in their busy schedules to provide us with the kind of quality review required to write a book of this size and scope.

We’d like to thank the Kettle and Pentaho communities. During and before the writing of this book, individuals from these communities provided valuable suggestions and ideas to all three authors for topics to cover in a book that focuses on ETL, data integration, and Kettle. We hope this book will be useful and practical for everybody who is using or planning to use Kettle. Whether we succeeded is up to the reader, but if we did, we have to thank individuals in the Kettle and Pentaho communities for helping us achieve it.

We owe many thanks to all contributors and developers of the Kettle software project. The authors are all enthusiastic users of Kettle: we love it, because it solves our daily data integration problems in a straightforward and efficient manner without getting in the way. Kettle is a joy to work with, and this is what provided much of the drive to write this book.

Finally, we’d like to thank our publisher, Wiley, for giving us the opportunity to write this book, and for the excellent support and management from their end. In particular, we’d like to thank our Project Editor, Sara Shlaer. Despite the often delayed deliveries from our end, Sara always kept her cool and somehow managed to make deadlines work out. Her advice, patience, encouragement, care, and sense of humor made all the difference and form an important contribution to this book. In addition, we’d like to thank our Executive Editor Robert Elliot. We appreciate the trust he put into our small team of authors to do our job, and his efforts to realize Pentaho Kettle Solutions.

—The authors

Writing a technical book like the one you are reading right now is very hard to do all by yourself. Because of the extremely busy agenda caused by the release process of Kettle 4, I probably should never have agreed to co-author. It’s only thanks to the dedication and professionalism of Jos and Roland that we managed to write this book at all. I thank both friends very much for their invitation to co-author. Even though writing a book is a hard and painful process, working with Jos and Roland made it all worthwhile.

When Kettle was not yet released as open source code it often received a lukewarm reaction. The reason was that nobody was really waiting for yet another closed source ETL tool. Kettle came from that position to being the most widely deployed open source ETL tool in the world. This happened only thanks to the thousands of volunteers who offered to help out with various tasks. Ever since Kettle was open sourced it became a project with an every growing community. It’s impossible to thank this community enough. Without the help of the developers, the translators, the testers, the bug reporters, the folks who participate in the forums, the people with the great ideas, and even the folks who like to complain, Kettle would not be where it is today. I would like to especially thank one important member of our community: Pentaho. Pentaho CEO Richard Daley and his team have done an excellent job in supporting the Kettle project ever since they got involved with it. Without their support it would not have been possible for Kettle to be on the accelerated growth path that it is on today. It’s been a pleasure and a privilege to work with the Pentaho crew.

A few select members of our community also picked up the tough job of reviewing the often technical content of this book. The reviewers of my chapters, Nicholas Goodman, Daniel Einspanjer, Bryan Senseman, Jens Bleuel, Samatar Hassan, and Mark Hall had the added disadvantage that this was the first time that I was going through the process of writing a book. It must not have been pretty at times. All the same they spent a lot of time coming up with insightful additions, spot-on advice, and to the point comments. I do enormously appreciate the vast amount of time and effort that they put into the reviewing. The book wouldn’t have been the same without you guys!

—Matt Casters

I’d like to thank both my co-authors, Jos and Matt. It’s an honor to be working with such knowledgeable and skilled professionals, and I hope we will collaborate again in the future. I feel our different backgrounds and expertise have truly complemented each other and helped us all to cover the many different subjects covered in this book.

I’d also like to thank the reviewers of my chapters: Benjamin Kallman, Bryan Senseman, Daniel Einspanjer, Sven Boden, and Samatar Hassan. Your comments and suggestions made all the difference and I thank you for your frank and constructive criticism.

Finally, I’d like to thank the readers of my blog at http://rpbouman.blogspot.com/. I got a lot of inspiration from the comments posted there, and I got a lot of good feedback in response to the blog posts announcing the writing of Pentaho Kettle Solutions.

—Roland Bouman

Back in October 2009, when Pentaho Solutions had only been on the shelves for two months and Roland and I agreed never to write another book, Bob Elliot approached us asking us to do just that. Yes, we had been discussing some ideas and already concluded that if there were to be another book, it would have to be about Kettle. And this was exactly what Bob asked us to do: write a book about data integration using Kettle. We quickly found out that Matt Casters was not only interested in reviewing, but in actually becoming a full author as well, an offer we gladly accepted. Looking back, I can hardly believe that we pulled it off, considering everything else that was going on in our lives. So many thanks to Roland and Matt for bearing with me, and thank you Bob and especially Sara for your relentless efforts of keeping us on track.

A special thank you is also warranted for Ralph Kimball, whose ideas you’ll find throughout this book. Ralph gave us permission to use the Kimball Group’s 34 ETL subsystems as the framework for much of the material presented in his book. Ralph also took the time to review Chapter 5, and thanks to his long list of excellent comments the chapter became a perfect foundation for Parts II, III, and IV of the book.

Finally I’d like to thank Daniel Einspanjer, Bryan Senseman, Jens Bleuel, Sven Boden, Samatar Hassan, and Benjamin Kallmann for being an absolute pain in the neck and thus doing a great job as technical reviewers for my chapters. Your comments, questions and suggestions definitely gave a big boost to the overall quality of this book.

—Jos van Dongen