Pentaho Data Integration Beginner's Guide

Table of Contents

Pentaho Data Integration Beginner's Guide

Credits

About the Author

About the Reviewers

www.PacktPub.com

Support files, eBooks, discount offers and more

Why Subscribe?

Free Access for Packt account holders

Preface

How to read this book

What this book covers

What you need for this book

Who this book is for

Conventions

Time for action – heading

What just happened?

Pop quiz – heading

Have a go hero – heading

Reader feedback

Customer support

Downloading the example code

Errata

Piracy

Questions

1. Getting Started with Pentaho Data Integration

Pentaho Data Integration and Pentaho BI Suite

Exploring the Pentaho Demo

Pentaho Data Integration

Using PDI in real-world scenarios

Loading data warehouses or datamarts

Integrating data

Data cleansing

Migrating information

Exporting data

Integrating PDI along with other Pentaho tools

Pop quiz – PDI data sources

Installing PDI

Time for action – installing PDI

What just happened?

Pop quiz – PDI prerequisites

Launching the PDI graphical designer – Spoon

Time for action – starting and customizing Spoon

What just happened?

Spoon

Setting preferences in the Options window

Storing transformations and jobs in a repository

Creating your first transformation

Time for action – creating a hello world transformation

What just happened?

Directing Kettle engine with transformations

Exploring the Spoon interface

Designing a transformation

Running and previewing the transformation

Pop quiz – PDI basics

Installing MySQL

Time for action – installing MySQL on Windows

What just happened?

Time for action – installing MySQL on Ubuntu

What just happened?

Have a go hero – installing a visual software for administering and querying MySQL

Summary

2. Getting Started with Transformations

Designing and previewing transformations

Time for action – creating a simple transformation and getting familiar with the design process

What just happened?

Getting familiar with editing features

Using the mouseover assistance toolbar

Working with grids

Understanding the Kettle rowset

Looking at the results in the Execution Results pane

The Logging tab

The Step Metrics tab

Have a go hero – calculating the achieved percentage of work

Have a go hero - calculating the achieved percentage of work (second version)

Running transformations in an interactive fashion

Time for action – generating a range of dates and inspecting the data as it is being created

What just happened?

Adding or modifying fields by using different PDI steps

The Select values step

Getting fields

Date fields

Pop quiz – generating data with PDI

Have a go hero – experiencing different PDI steps

Have a go hero – generating a rowset with dates

Handling errors

Time for action – avoiding errors while converting the estimated time from string to integer

What just happened?

The error handling functionality

Time for action – configuring the error handling to see the description of the errors

What just happened?

Personalizing the error handling

Have a go hero – trying out different ways of handling errors

Summary

3. Manipulating Real-world Data

Reading data from files

Time for action – reading results of football matches from files

What just happened?

Input files

Input steps

Reading several files at once

Time for action – reading all your files at a time using a single text file input step

What just happened?

Time for action – reading all your files at a time using a single text file input step and regular expressions

What just happened?

Regular expressions

Troubleshooting reading files

Have a go hero – exploring your own files

Pop quiz – providing a list of text files using regular expressions

Have a go hero – measuring the performance of input steps

Sending data to files

Time for action – sending the results of matches to a plain file

What just happened?

Output files

Output steps

Have a go hero – extending your transformations by writing output files

Have a go hero – generate your custom matches.txt file

Getting system information

Time for action – reading and writing matches files with flexibility

What just happened?

The Get System Info step

Running transformations from a terminal window

Time for action – running the matches transformation from a terminal window

What just happened?

Have a go hero – finding out system information

XML files

Time for action – getting data from an XML file with information about countries

What just happened?

What is XML?

PDI transformation files

Getting data from XML files

XPath

Configuring the Get data from the XML step

Kettle variables

How and when you can use variables

Have a go hero – exploring XML files

Summary

4. Filtering, Searching, and Performing Other Useful Operations with Data

Sorting data

Time for action – sorting information about matches with the Sort rows step

What just happened?

Have a go hero – listing the last match played by each team

Calculations on groups of rows

Time for action – calculating football match statistics by grouping data

What just happened?

Group by Step

Numeric fields

Have a go hero – formatting 99.55

Pop quiz — formatting output fields

Have a go hero – listing the languages spoken by a country

Filtering

Time for action – counting frequent words by filtering

What just happened?

Time for action – refining the counting task by filtering even more

What just happened?

Filtering rows using the Filter rows step

Have a go hero – playing with filters

Looking up data

Time for action – finding out which language people speak

What just happened?

The Stream lookup step

Have a go hero – selecting the most popular of the official languages

Have a go hero – counting words more precisely

Data cleaning

Time for action – fixing words before counting them

What just happened?

Cleansing data with PDI

Have a go hero – counting words by cleaning them first

Summary

5. Controlling the Flow of Data

Splitting streams

Time for action – browsing new features of PDI by copying a dataset

What just happened?

Copying rows

Have a go hero – recalculating statistics

Distributing rows

Time for action – assigning tasks by distributing

What just happened?

Pop quiz – understanding the difference between copying and distributing

Splitting the stream based on conditions

Time for action – assigning tasks by filtering priorities with the Filter rows step

What just happened?

PDI steps for splitting the stream based on conditions

Time for action – assigning tasks by filtering priorities with the Switch/Case step

What just happened?

Have a go hero – listing languages and countries

Pop quiz – deciding between a Number range step and a Switch/Case step

Merging streams

Time for action – gathering progress and merging it all together

What just happened?

PDI options for merging streams

Time for action – giving priority to Bouchard by using the Append Stream

What just happened?

Have a go hero – sorting and merging all tasks

Treating invalid data by splitting and merging streams

Time for action – treating errors in the estimated time to avoid discarding rows

What just happened?

Treating rows with invalid data

Have a go hero – trying to find missing countries

Summary

6. Transforming Your Data by Coding

Doing simple tasks with the JavaScript step

Time for action – counting frequent words by coding in JavaScript

What just happened?

Using the JavaScript language in PDI

Inserting JavaScript code using the Modified JavaScript Value Step

Adding fields

Modifying fields

Using transformation predefined constants

Testing the script using the Test script button

Reading and parsing unstructured files with JavaScript

Time for action – changing a list of house descriptions with JavaScript

What just happened?

Looping over the dataset rows

Have a go hero – enhancing the houses file

Doing simple tasks with the Java Class step

Time for action – counting frequent words by coding in Java

What just happened?

Using the Java language in PDI

Inserting Java code using the User Defined Java Class step

Adding fields

Modifying fields

Sending rows to the next step

Data types equivalence

Testing the Java Class using the Test class button

Have a go hero – parameterizing the Java Class

Transforming the dataset with Java

Time for action – splitting the field to rows using Java

What just happened?

Avoiding coding by using purpose built steps

Pop quiz – choosing a scripting language for coding inside a transformation

Summary

7. Transforming the Rowset

Converting rows to columns

Time for action – enhancing the films file by converting rows to columns

What just happened?

Converting row data to column data by using the Row Denormaliser step

Have a go hero – houses revisited

Aggregating data with a Row Denormaliser step

Time for action – aggregating football matches data with the Row Denormaliser step

What just happened?

Using Row Denormaliser for aggregating data

Have a go hero – calculating statistics by team

Normalizing data

Time for action – enhancing the matches file by normalizing the dataset

What just happened?

Modifying the dataset with a Row Normaliser step

Summarizing the PDI steps that operate on sets of rows

Have a go hero – verifying the benefits of normalizing

Have a go hero – normalizing the Films file

Generating a custom time dimension dataset by using Kettle variables

Time for action – creating the time dimension dataset

What just happened?

Getting variables

Time for action – parameterizing the start and end date of the time dimension dataset

What just happened?

Using the Get Variables step

Have a go hero – enhancing the time dimension

Summary

8. Working with Databases

Introducing the Steel Wheels sample database

Connecting to the Steel Wheels database

Time for action – creating a connection to the Steel Wheels database

What just happened?

Connecting with Relational Database Management Systems

Pop quiz – connecting to a database in several transformations

Have a go hero – connecting to your own databases

Exploring the Steel Wheels database

Time for action – exploring the sample database

What just happened?

A brief word about SQL

Exploring any configured database with the database explorer

Have a go hero – exploring the sample data in depth

Have a go hero – exploring your own databases

Querying a database

Time for action – getting data about shipped orders

What just happened?

Getting data from the database with the Table input step

Using the SELECT statement for generating a new dataset

Making flexible queries using parameters

Time for action – getting orders in a range of dates using parameters

What just happened?

Adding parameters to your queries

Making flexible queries by using Kettle variables

Time for action – getting orders in a range of dates by using Kettle variables

What just happened?

Using Kettle variables in your queries

Pop quiz – interpreting data types coming from a database

Have a go hero – querying the sample data

Sending data to a database

Time for action – loading a table with a list of manufacturers

What just happened?

Inserting new data into a database table with the Table output step

Inserting or updating data by using other PDI steps

Time for action – inserting new products or updating existing ones

What just happened?

Time for action – testing the update of existing products

What just happened?

Inserting or updating with the Insert/Update step

Have a go hero – populating a films database

Have a go hero – populating the products table

Pop quiz – replacing an Insert/Update step with a Table Output step followed by an Update step

Eliminating data from a database

Time for action – deleting data about discontinued items

What just happened?

Deleting records of a database table with the Delete step

Have a go hero – deleting old orders

Have a go hero – creating the time dimension

Summary

9. Performing Advanced Operations with Databases

Preparing the environment

Time for action – populating the Jigsaw database

What just happened?

Exploring the Jigsaw database model

Looking up data in a database

Doing simple lookups

Time for action – using a Database lookup step to create a list of products to buy

What just happened?

Looking up values in a database with the Database lookup step

Have a go hero – preparing the delivery of the products

Have a go hero – refining the transformation

Performing complex lookups

Time for action – using a Database join step to create a list of suggested products to buy

What just happened?

Joining data from the database to the stream data by using a Database join step

Have a go hero – rebuilding the list of customers

Introducing dimensional modeling

Loading dimensions with data

Time for action – loading a region dimension with a Combination lookup/update step

What just happened?

Time for action – testing the transformation that loads the region dimension

What just happened?

Describing data with dimensions

Loading Type I SCD with a Combination lookup/update step

Have a go hero – adding regions to the Region dimension

Have a go hero – loading the manufacturers dimension

Storing a history of changes

Time for action – keeping a history of changes in products by using the Dimension lookup/update step

What just happened?

Time for action – testing the transformation that keeps history of product changes

What just happened?

Keeping an entire history of data with a Type II slowly changing dimension

Loading Type II SCDs with the Dimension lookup/update step

Have a go hero – storing a history just for the theme of a product

Have a go hero – loading the Regions dimension as a Type II SCD

Pop quiz – implementing a Type III SCD in PDI

Have a go hero – loading a mini dimension

Summary

10. Creating Basic Task Flows

Introducing PDI jobs

Time for action – creating a folder with a Kettle job

What just happened?

Executing processes with PDI jobs

Using Spoon to design and run jobs

Pop quiz – defining PDI jobs

Designing and running jobs

Time for action – creating a simple job and getting familiar with the design process

What just happened?

Changing the flow of execution on the basis of conditions

Looking at the results in the Execution results window

The Logging tab

The Job metrics tab

Running transformations from jobs

Time for action – generating a range of dates and inspecting how things are running

What just happened?

Using the Transformation job entry

Have a go hero – loading the dimension tables

Receiving arguments and parameters in a job

Time for action – generating a hello world file by using arguments and parameters

What just happened?

Using named parameters in jobs

Have a go hero – backing up your work

Running jobs from a terminal window

Time for action – executing the hello world job from a terminal window

What just happened?

Have a go hero – experiencing Kitchen

Using named parameters and command-line arguments in transformations

Time for action – calling the hello world transformation with fixed arguments and parameters

What just happened?

Have a go hero – saying hello again and again

Have a go hero – loading the time dimension from a job

Deciding between the use of a command-line argument and a named parameter

Have a go hero – analyzing the use of arguments and named parameters

Summary

11. Creating Advanced Transformations and Jobs

Re-using part of your transformations

Time for action – calculating statistics with the use of a subtransformations

What just happened?

Creating and using subtransformations

Have a go hero – calculating statistics for all subjects

Have a go hero – counting words more precisely (second version)

Creating a job as a process flow

Time for action – generating top average scores by copying and getting rows

What just happened?

Transferring data between transformations by using the copy/get rows mechanism

Have a go hero – modifying the flow

Iterating jobs and transformations

Time for action – generating custom files by executing a transformation for every input row

What just happened?

Executing for each row

Have a go hero – building lists of products to buy

Enhancing your processes with the use of variables

Time for action – generating custom messages by setting a variable with the name of the examination file

What just happened?

Setting variables inside a transformation

Running a job inside another job with a Job job entry

Understanding the scope of variables

Have a go hero – processing several files at once

Have a go hero – enhancing the jigsaw database update process

Have a go hero – executing the proper jigsaw database update process

Pop quiz – deciding the scope of variables

Summary

12. Developing and Implementing a Simple Datamart

Exploring the sales datamart

Deciding the level of granularity

Loading the dimensions

Time for action – loading the dimensions for the sales datamart

What just happened?

Extending the sales datamart model

Have a go hero – loading the dimensions for the puzzle star model

Loading a fact table with aggregated data

Time for action – loading the sales fact table by looking up dimensions

What just happened?

Getting the information from the source with SQL queries

Translating the business keys into surrogate keys

Obtaining the surrogate key for Type I SCD

Obtaining the surrogate key for Type II SCD

Obtaining the surrogate key for the Junk dimension

Obtaining the surrogate key for the Time dimension

Pop quiz – creating a product type dimension

Have a go hero – loading a puzzles fact table

Getting facts and dimensions together

Time for action – loading the fact table using a range of dates obtained from the command line

What just happened?

Time for action – loading the SALES star

What just happened?

Have a go hero – enhancing the loading process of the sales fact table

Have a go hero – loading the puzzle sales star

Have a go hero – loading the facts once a month

Automating the administrative tasks

Time for action – automating the loading of the sales datamart

What just happened?

Have a go hero – creating a backup of your work automatically

Have a go hero – enhancing the automation process by sending an email if an error occurs

Summary

A. Working with Repositories

Creating a database repository

Time for action – creating a PDI repository

What just happened?

Creating a database repository to store your transformations and jobs

Working with the repository storage system

Time for action – logging into a database repository

What just happened?

Logging into a database repository using credentials

Creating transformations and jobs in repository folders

Creating database connections, users, servers, partitions, and clusters

Designing jobs and transformations

Backing up and restoring a repository

Examining and modifying the contents of a repository with the Repository Explorer