Creating the Date Dimension using Kettle

Creating the Date Dimension using Kettle

Creating the Date dimension table is perhaps the most important common step to all dimensional modeling projects. According to Ralph Kimball � the author of The Data Warehousing Toolkit


�Data warehouses always need an explicit date dimension table. There are many date attributes not supported by the SQL Date function, including fiscal periods, seasons, holidays, and weekends. Rather than attempting to determine these nonstandard calendar calculations in a query, we should look them up in a date dimension table.�


The first step is to launch Kettle from the desktop. I have GeoKettle installed on my machine, which, for those who dont know, is simply a spatially enabled version of Pentahos Open source ETL platform (Kettle). If you dont have GeoKettle installed, my previous post shows the steps to download and install it on your local machine.

Using the sample Transformation as the starting point.

GeoKettle ships with some sample transformations that allow creation of Date dimensions. We can start with these and augment them based on our needs. The first two dimensions that are needed are Date and Time. According to best practices, Date and Time should be modeled as separate dimensions

Launching GeoKettle

If you have GeoKettle installed, launch it from the Applications menu on your machine.

Once GeoKettle is launched, go to the samples folder under GeoKettle/samples and open the file �General � Populate date dimension.ktr�

The following transformation is loaded on the screen.

Save the file to your local directory for review and possible modification. I saved the transformation with the GenerateDateDimension.ktr under my local work folder.

The default transformation generates dates from 1950 all the way to 2050. I only needed 50 odd years of dates for my purposes, so I needed to modify the start date. Right click and Edit the first step on the transformation to view dialog box for "Generate Rows."

In the dialog box that opens, we can see the default values as follows:

Now lets modify the default values as follows.

Step name: 20000 days: 50+ years

Limit: 20000

Fields > Value: 19900101

Next thing we need to do is to update the Table output parameters to our selected database schema. Right click on the "Table Output" step as shown in the screenshot below.

Define the database connection, by clicking on the "New" button next to the Connection field on the open dialog box.

Select the schema and table name. In my case, it was nydot and dim_date.

Click OK when done. 

The last step is to enable the hop, by right clicking on the arrow connecting to the last step and clicking on �Enable Hop� as shown in the picture below.

Next step is to run the transformation and test to make sure it works correctly. For this we click on the play button on the top menu, as shown in the picture below.

Clicking the button brings up the following dialog box.

Now, click on the Launch button to start the transformation. Once the transformation has executed, we can see the following metrics displayed on the screen.

Open pgAdmin tool to view the data by navigating to the schema and the date_dim table that was just created.

Also, now right clicking and viewing data

.... shows that the table is populated correctly.

We have just seen how to populate a basic Date Dimension table using Kettle open source ETL tool.

{ Read More }

Creating a Kettle Repository

Creating a Kettle Repository

I wanted to create a new set of transformations that I was constructing for a project. For the same, I set up GeoKettle, a spatial ETL tool. Starting Kettle I was always wondered what the Central repository was. As I browsed through some old threads on user forums, I understood that Kettle repository was for centralizing your jobs and transformations across projects and users into a single place, so anyone could execute them.

That seemed like a good idea. However, most older posts had some concerns around its stability. Realizing that these concerns were probably five years or so old, I decided to set it up anyway.

First step is to create a Postgresql database user. For this, we need to first login as the postgres user within the Ubuntu environment and connect to the postgres

> su - postgres

Next we login to the psql environment and create a database level user called kettleadmin.

postgres > psql

psql > create user kettleadmin with password kettleadmin;

We then create a database called kettledb and we will grant all access to this database to user kettleadmin.

psql > create database kettledb;

psql> grant all on database kettledb to kettleadmin;

Now that we have setup the database, we can setup the kettle repository.

First step is to launch the GeoKettle shortcut. That brings the dialog box to choose your repository.

Now click the button for �New� to make a new repository, and click again on the following dialog box to create a new database connection.

On clicking the new button, another dialog box appears. Select Postgresql as the database connection type. Next, we specify the connection parameters to the database we have just created using credentials for kettleadmin as shown below

Click on �Test� to make sure the connection parameters are correct.

Once done, enter the additional details like name and description, and click �Create or Upgrade� to create the repository for the first time.

Once executed, the application will provide a list of commands executed.

Next, access the original command window and enter the default Login name and password for the database, which is admin/admin. Click on OK.

Now, on the dialog that appears, you can click on the top menu item �Repository� and click on link to �Explore Repository�.

This launches a window, where we can make changes to the current repository. The first step would be to add a new user for myself, to ensure I dont corrupt the configuration while working. I can do this by selecting the Users link and right clicking to select �New User� from the context menu.

Now I can configure this the way I want, to add new users. We are all set to move forward.

{ Read More }

Creating the Time Dimension table in Pentaho Data Integration Kettle

Creating the Time Dimension table in Pentaho Data Integration Kettle

In my previous post, we had seen how to create the Date Dimension table using Pentaho Data Integration (Kettle). In this post, we will see how to create a Time dimension in Kettle.

It is recommended that Time dimension be a separate dimension from the Date. In that spirit, I created the following transformation in Kettle. It contains the following 10 steps.

1. Launch Kettle

2. Generate Rows for processing and setup variables

3. Convert Date to Epoch Time

4. Create sequence for incrementing time in milliseconds

5. Create sequence for incrementing identifier

6. Increment time and identifier with newly created sequences

7. Convert time in milliseconds back to Date

8. Output contents to a text file

9. Extract time out of the incremented date

Step 1: Launch Kettle

Launch Pentaho Data Integration (Kettle or GeoKettle) on your local machine. Create a new transformation by clicking on File > New Transformation.

Step 2: Generate Rows for processing and setup variables

Make sure you are in design mode. Expand the Input section on Steps menu on the side and navigate to Generate Rows step.

Drag the Generate Rows step across to the work area as shown below, and drop it in the work area.

Right click on the Generate Rows step and enter the following information

1. Step Name: Generate Rows
2. Limit : 1440

In the fields column, add the following field

1. Name: START_TIME
2. Type: Integer
3. Format: ####
4. Value: 0000

Click on the row to add a second field. Add the following parameters for this field

1. Name: REAL_TIME
2. Type: Date
3. Format: yyyy/MM/dd HH:mm:ss
4. Value: 1970/01/01 00:00:00

Step 3: Convert Date to Epoch Time

Next add �Select Values� from the Transform section of the Design tab. Select Values control allows us to change and modify the datatype of any variable in the flow. After dropping the step in the workspace, connect it to the preceding step using a hop as shown in the adjacent figure.

Now Edit the step to enter the following configuration information to convert Real time to an Integer. Doing so, converts the time to its Epoch time in milliseconds. To do so, we enter the following information.

1. Fieldname: REAL_TIME
2. Type: Integer

Step 4: Create sequence for incrementing time in milliseconds

Next we add a Sequence to our flow, and connect it to the preceding step as shown in the screenshots below. The sequence will increment our time in milliseconds derived in the previous step, by one minute intervals.

Edit the step in the transformation to enter the following information.

1. Step name: Millisecond Sequence
2. Name of value: MS_Increment
3. Use counter to calculate sequence:
4. Start at value: 0
5. Increment by: 60000
6. Maximum value: 86400000

Click OK to continue.

Step 5: Create sequence for incrementing identifier

Now add another sequence to the transformation as shown below and connect it to the preceding hop as shown below.

We can enter the following configuration for the newly added sequence.

1. Step name: Time_Since
2. Name of value: Time_Since
3. Use counter to calculate sequence:
4. Start at value: 0
5. Increment by: 1
6. Maximum value: 1440

Step 6: Increment time and identifier with newly created sequences

Next, we need to add a calculator to the flow.

We need to configure the calculator by adding two calculations. First calculation adds the numeric sequence to our identifier, and second sequence adds Millisecond increments to our time.

For the first field, enter the following details.

New field: INC_TIME
Calculation: A+B

Field A: INC_TIME

Field B: Time_Since

Remove: N

For the next field, add the following parameters.

New field: CALC_TIME

Calculation: A+B

Field A: REAL_TIME

Field B: MS_Increment

Value type: Integer

Remove: N

Here is how the dialog box looks on my screen.

Click OK to continue.

Step 7: Convert time in milliseconds back to Date

Next step is to convert the calculated time from integer back to Date. To do this, we add another Select Values step to our transformation.

Make the following configuration changes in the newly added step.

Fieldname: CALC_TIME

Type: Date

Binary to Normal?: N

Format: yyyy/MM/dd HH:mm:ss

Click OK to continue

Step 8: Output contents to a text file

The final step is to output the contents to a text file

For this we add a �File Text Output� step to our transformation.

The Text File Output step, as the name suggests outputs the results to a text file.

Edit the �Text File Ouput� step, and make the following changes.

On the File tab, set the name of the output file. I have set it to simply �log� and the Extension to txt. This will create a file called log.txt in the output folder.

Step 9: Extract time out of the incremented date

Before clicking OK, switch to the Fields tab, and make the following entries.

Retain the following two fields.

1. Field 1 details are as follows

• Name: Time_Since
• Type: Integer
• Precision: 0

2. Field 2 details are as follows

• Name: CALC_TIME
• Type: Date
• Format: HH:mm:ss

The following screenshot shows these changes as on my screen.

Click Ok. 

Step 10: Save and Test the Transformation

Save the transformation using the floppy icon on toolbar or File > Save on drop down menu.

Now, we need to run it using the Launch button.

Clicking this button launches the following screen.

Click the Launch button and the transformation starts executing.

The Execution Results window shows the results in a bottom pane.

Opening the log file, we can see the following results.

Thats it, we are done.

{ Read More }