Understanding the Pentaho Kettle Dimension Insert/Update Step Null Value Behaviour

We will be using a very simple sample transformation to test the null value behaviour:

We use the Data Grid step to provide some sample data. We specified an id and name column and added data for one record. Let’s run the transformation and take a look at your dimension table in a query client:

Notice that Kettle automatically inserted an additional record with a technical key of value 0. This will only happen in the first execution. Below this record you find the one record from our sample dataset.

You can use the Dimension Lookup/Update step as well to perform only a lookup. Let’s test this. Double click on this step and untick Update the dimension?

Add one more data record to the Data Grid and then let’s perform a Preview on the Dimension Lookup/Update step:

Notice how this new record got assigned a technical key of value 0. This is because the id 2 does not exist yet in the dimension table, hence the lookup returned a technical key of 0, the default for unknown values. Remember that every fact record must point to a dimensional record in a Kimball style star schema. The technical key of value 0 is making this possible.

Now let’s test what happens if we have a null value in the natural key in one of our dimension input records. Let’s add one more row to the test dataset:

Notice that we left the id empty for record #3.

Double click on the Dimension Lookup/Update step and enable the update functionality again:

Now run the transformation three times. After this, check the data in your dimensional table:

Hm, the record with the null natural key got inserted 3 times. Not so good!

So let’s quickly delete these records from the dimension table:

DELETE FROM dma.dim_scd2_test WHERE name = 'failure';

Let’s add a filter to our transformation so that these records do not make it to the Dimension Lookup/Update step. The filter checks if the id is not null:

Also create a copy of the Dimension Lookup/Update step. Double click on this copy and disable the update functionality; then connect it directly to the Data Grid step. We will use this copy only for previewing the lookup data.

Ok, now let’s run the transformation again several times and then analyze the data in the dimension table:

As you would have expected, this time we have a clean dimension dataset.

Now, let’s preview the data on the Dimension Lookup/Update step which has the update functionality disabled (so it will only perform a lookup):

Notice that our record with a null natural key got a 0 technical key assigned … just how it should be!

Long story short: Do not insert records with a natural key value of NULL into a dimension table!

Not just Null - handling different nuances

If you have special cases like “Empty”, “Not found” etc, you can manually add these entries to the dimension table (more info).

Let’s consider this for our test dataset. Let’s assume our imaginary client tells us that a natural key of -10000 means that no key is available. We want to track if there was really a -10000 key in the record or if it was null, so what we can do is add an additional record to the dimension manually:

INSERT INTO dma.dim_scd2_test VALUES (-1, 1,'1900-01-01 00:00:00'::timestamp,'2199-12-31 23:59:59'::timestamp,  -10000, 'no key available');

Please note that we set the version, date_from and date_to values as well!

The dimension data looks now like this:

Note: Depending on your DDL, you might not be able to insert a technical key of -1. In this case, just use a positive integer.

Ok, let’s add a new record to our test dataset:

Make sure you change the Filter condition so that it includes the -10000 case:

We don’t want any records with a natural key of -10000 to be inserted into the dimensional table by the Dimension Insert/Update step!

Let’s run the transformation and check the dimension dataset:

All good so far. Now let’s perform a Preview on the Dimension Lookup/Update step which has the update functionality disabled (so it will only perform a lookup):

As you can see, the correct technical keys got assigned.

I do hope that this article gave you a good overview on the null value behaviour of the Kettle Dimension Lookup/Update step. You can find the sample transformation discussed in this article here.

Talend Open Studio: Populating a date dimension

Populating a date dimension

Date dimensions are an essential part of a data warehouse. Usually they are only populated once. Scripts can be created on the database side (as outlined here), but if you are working on various projects involving a variety of databases, it is more efficient to create just one ETL job which can be used to populate any database.

In this tutorial we will have a look at creating such an ETL job with Talend Open Studio for Data Integration. We will create a basic date dimension which you can then extend even further. In order to follow this tutorial, the reader should be familiar with the basic functionality of Talend Open Studio.

Our date dimension will look like this one (partial screenshot):

The primary key of the dimension will be an integer representation of the date, which saves us the hassle of looking up the key when we transform the fact data.

Open Talend Open Studio for Data Integration and create a new job called populate_date_dimension. First we will define a variable called start date, because we will use this job in various projects and we might require a different start date each time:

Click on the Context tab and then on the + button to add a new context variable. Give it the name myStartDate of type Date and define a value for it.

Next add a tRowGenerator component to the design area and double click on it to activate the settings dialog. The idea is to create X amount of rows: The first row will hold our start date and each subsequent row will increment the date by one day.

1. Click the + button to add a new column. Name it date and set the type to Date.
2. Click in the Environment variables cell on the right hand side and then you will see the parameters displayed in the Function parameters tab on the bottom left hand side.
3. Define the number of rows that should be generated in Number of Rows for RowGenerator.
4. In the Function parameters tab set the date parameter value to context.myStartDate. This will ensure that the context variable which we defined earlier will be used.
5. Set the nb parameter to Numeric.sequence(“s1”, 1, 1) - 1. Use the expression builder for a more convenient setup. This will create a sequence which we will use to add days to our start date. The reason why we subtract 1 at the end is because we want to keep our start date.
6. Set the dateType parameter value to “dd”. This ensures that days will be added to our date.
7. Click on the Preview tab and check if the result set looks as expected.
8. Click Ok to close the component settings.

Now add a tMap component and create a row from the tRowGenerator to the tMap component. Double click the tMap component:

1. Click the + button on the right hand side to create a new output table.
2. Add new columns to the output table and for each of them define a specific date format using this approach: Integer.parseInt(TalendDate.formatDate("yyyyMMdd",row1.date)) for integer values and TalendDate.formatDate("MM",row1.date) for string values. Have a look at the Java SimpleDateFormat specs to get an understanding of all the formatting options. You will spend now some time setting all the various date formats up.
3. Java SimpleDateFormat doesn’t provide a quarter format, hence we have to create our own in the form of a ceiled devision / covered quotient: (Integer.parseInt(TalendDate.formatDate("M",row1.date))+3-1) /  3   
4. Click Ok.

Add a database output component of your choice (in my case I used one for PostgreSQL) and create a row from the tMap to the database output component. Double click the database output component and provide all the necessary settings. That’s it: Now you can run the job and examine the data in your table.