Pentaho Data Integration Quick Start Guide

Loading a dimension with a combination lookup/update step

The BODYPARTS dimension is meant to hold the name of the body part where the injury occurred. This table has no corresponding table in the source database. Because of that, there is no business key. The content is just a list of the different body parts and a surrogate key. The following is the DDL for the dimension table:

CREATE TABLE DIM_BODYPARTS ( id integer NOT NULL, bodypart CHAR(50) NOT NULL, PRIMARY KEY (id));

 Loading a dimension like this one is simple:

1. Create a transformation.
2. Drag and drop a Table input step. We will use it to get the data to load into the table.
3. Double-click on the step. As the Connection, select Sports. In the SQL box, type the following query:

 SELECT ...