Time for action—keeping a history of product changes with the Dimension lookup/update step
by María Carina Roldán
Pentaho 3.2 Data Integration Beginner's Guide
- Pentaho 3.2 Data Integration Beginner's Guide
- Pentaho 3.2 Data Integration
- Credits
- Foreword
- The Kettle Project
- About the Author
- About the Reviewers
- Preface
- 1. Getting Started with Pentaho Data Integration
- Pentaho Data Integration and Pentaho BI Suite
- Pentaho Data Integration
- Installing PDI
- Time for action—installing PDI
- Launching the PDI graphical designer: Spoon
- Time for action—starting and customizing Spoon
- Time for action—creating a hello world transformation
- Time for action—running and previewing the hello_world transformation
- Installing MySQL
- Time for action—installing MySQL on Windows
- Time for action—installing MySQL on Ubuntu
- Summary
- 2. Getting Started with Transformations
- Reading data from files
- Time for action—reading results of football matches from files
- Time for action—reading all your files at a time using a single Text file input step
- Time for action reading all your files at a time using a single Text file input step and regular expressions
- Sending data to files
- Time for action—sending the results of matches to a plain file
- Getting system information
- Time for action—updating a file with news about examinations
- Time for action—running the examination transformation from a terminal window
- XML files
- Time for action—getting data from an XML file with information about countries
- Summary
- 3. Basic Data Manipulation
- Basic calculations
- Time for action—reviewing examinations by using the Calculator step
- Time for action—reviewing examinations by using the Formula step
- Calculations on groups of rows
- Time for action—calculating World Cup statistics by grouping data
- Filtering
- Time for action—counting frequent words by filtering
- Looking up data
- Time for action—finding out which language people speak
- Summary
- 4. Controlling the Flow of Data
- Splitting streams
- Time for action—browsing new PDI features by copying a dataset
- Time for action—assigning tasks by distributing
- Splitting the stream based on conditions
- Time for action - assigning tasks by filtering priorities with the Filter rows step
- Time for action—assigning tasks by filtering priorities with the Switch/ Case step
- Merging streams
- Time for action—gathering progress and merging all together
- Time for action—giving priority to Bouchard by using Append Stream
- Summary
- 5. Transforming Your Data with JavaScript Code and the JavaScript Step
- Doing simple tasks with the JavaScript step
- Time for action—calculating scores with JavaScript
- Time for action—testing the calculation of averages
- Enriching the code
- Time for action calculating flexible scores by using variables
- Reading and parsing unstructured files
- Time for action—changing a list of house descriptions with JavaScript
- Avoiding coding by using purpose-built steps
- Summary
- 6. Transforming the Row Set
- Converting rows to columns
- Time for action—enhancing a films file by converting rows to columns
- Time for action—calculating total scores by performances by country
- Normalizing data
- Time for action—enhancing the matches file by normalizing the dataset
- Generating a custom time dimension dataset by using Kettle variables
- Time for action—creating the time dimension dataset
- Time for action—getting variables for setting the default starting date
- Summary
- 7. Validating Data and Handling Errors
- Capturing errors
- Time for action—capturing errors while calculating the age of a film
- Time for action—aborting when there are too many errors
- Time for action—treating errors that may appear
- Avoiding unexpected errors by validating data
- Time for action validating genres with a Regex Evaluation step
- Time for action—checking films file with the Data Validator
- Summary
- 8. Working with Databases
- Introducing the Steel Wheels sample database
- Time for action—creating a connection with the Steel Wheels database
- Time for action—exploring the sample database
- Querying a database
- Time for action—getting data about shipped orders
- Time for action—getting orders in a range of dates by using parameters
- Time for action—getting orders in a range of dates by using variables
- Sending data to a database
- Time for action—loading a table with a list of manufacturers
- Time for action—inserting new products or updating existent ones
- Time for action—testing the update of existing products
- Eliminating data from a database
- Time for action—deleting data about discontinued items
- Summary
- 9. Performing Advanced Operations with Databases
- Preparing the environment
- Time for action—populating the Jigsaw database
- Looking up data in a database
- Time for action—using a Database lookup step to create a list of products to buy
- Time for action using a Database join step to create a list of suggested products to buy
- Introducing dimensional modeling
- Loading dimensions with data
- Time for action loading a region dimension with a Combination lookup/update step
- Time for action—testing the transformation that loads the region dimension
- Time for action—keeping a history of product changes with the Dimension lookup/update step
- Time for action—testing the transformation that keeps a history of product changes
- Summary
- 10. Creating Basic Task Flows
- Introducing PDI jobs
- Time for action—creating a simple hello world job
- Receiving arguments and parameters in a job
- Time for action—customizing the hello world file with arguments and parameters
- Running jobs from a terminal window
- Time for action—executing the hello world job from a terminal window
- Using named parameters and command-line arguments in transformations
- Time for action—calling the hello world transformation with fixed arguments and parameters
- Deciding between the use of a command-line argument and a named parameter
- Running job entries under conditions
- Time for action—sending a sales report and warning the administrator if something is wrong
- Summary
- 11. Creating Advanced Transformations and Jobs
- Enhancing your processes with the use of variables
- Time for action—updating a file with news about examinations by setting a variable with the name of the file
- Enhancing the design of your processes
- Time for action—generating files with top scores
- Time for action—calculating the top scores with a subtransformation
- Time for action—splitting the generation of top scores by copying and getting rows
- Time for action—generating the files with top scores by nesting jobs
- Iterating jobs and transformations
- Time for action—generating custom files by executing a transformation for every input row
- Summary
- 12. Developing and Implementing a Simple Datamart
- Exploring the sales datamart
- Loading the dimensions
- Time for action—loading dimensions for the sales datamart
- Extending the sales datamart model
- Loading a fact table with aggregated data
- Time for action—loading the sales fact table by looking up dimensions
- Getting facts and dimensions together
- Time for action—loading the fact table using a range of dates obtained from the command line
- Time for action—loading the sales star
- Getting rid of administrative tasks
- Time for action—automating the loading of the sales datamart
- Summary
- 13. Taking it Further
- A. Working with Repositories
- Creating a repository
- Time for action—creating a PDI repository
- Working with the repository storage system
- Time for action—logging into a repository
- Examining and modifying the contents of a repository with the Repository explorer
- Migrating from a file-based system to a repository-based system and vice-versa
- Summary
- B. Pan and Kitchen: Launching Transformations and Jobs from the Command Line
- C. Quick Reference: Steps and Job Entries
- D. Spoon Shortcuts
- E. Introducing PDI 4 Features
- F. Pop quiz—Answers
Next
Next Chapter
Time for action—testing the transformation that keeps a history of product changes
Let's load a puzzles dimension along with the history of the changes in puzzle attributes:
- Create a new transformation.
- Drag a Table input step to the work area and double-click it.
- Select
jsas Connection. - Type the following query in the SQL area:
SELECT pro_code , man_code , pro_name , pro_theme FROM products WHERE pro_type LIKE 'PUZZLE'
- Click on OK.
- Add an Add constants step, and create a hop from the Table input, step toward it.
- Use the step to add a
Datefield namedchangedate. As Format typedd/MM/yyyy, and as Value, type01/10/2009. - Expand the Data Warehouse category of steps.
- Select the Dimension lookup/update step and drag it to the canvas.
- Create a hop from the Add constants step to this new step.
- Double-click the Dimension lookup/update step.
- As Connection select
dw. - As Target table type
lk_puzzles. - Fill the Key fields as shown:
- Select
idas Technical key field. - In the frame Creation of technical key, leave the default to
Use table maximum + 1. - As Version field, select
version. - As Stream Datefield, select
changedate. - As Date range start field, select
start_date. - As Table daterange end, select
end_date. - Select the Fields tab and fill it like this:
- Close the settings window.
- Save the transformation, and run it.
- Explore the
js_dwdatabase and do a preview of thelk_puzzlestable. - You should see this:
You loaded the puzzle dimension with the name and theme of the puzzles you sell. The dimension table has the usual columns for a dimension—technical id (field id), fields that store the key fields in the table of the operational database (prod_code and man_code), and columns for the puzzle attributes (name and theme). It also has some extra fields specially designed to keep history.
When you ran the transformation, all records were inserted in the dimension table. Also a special record was automatically inserted for unavailable data.
So far, there is nothing new except for a few extra columns with dates. In the next tutorial, you will learn more about those columns.
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.