Pentaho Data Integration Quick Start Guide

Loading a fact table

We already have the dimension tables loaded. It's time to load the fact table. The following is the DDL for the Injuries fact table:

CREATE TABLE FT_INJURIES ( date integer NOT NULL, id_bodypart integer NOT NULL, id_person integer NOT NULL, quantity integer DEFAULT 0 NOT NULL );

This is a cumulative fact table that we will load in an incremental way. Suppose that the fact table is already loaded with injuries having occurred up until 2007-07-31. Now, we have to load the new data, as follows:

1. Create a transformation, and drag in a Table input step.
2. Double-click on the step. Select the Sports connection. As the SQL, type the following statement:

      SELECT        i.person_id       , injury_type , cast(to_char(start_date_time, 'yyyymmdd') ...