Loading a Type II SDC and verifying how history is kept

In the preceding section, you loaded a dimension with products using a Dimension lookup/update step. You ran the Transformation once, caused the insertion of one record for each product, and a special record with N/A values for the descriptive fields. In order to effectively see how history is kept, we will make some changes in the operational database and run the Transformation again to see how the Dimension lookup/update step stores history:

  1. Among the downloaded material, locate the update_jumbo_products.sql script and run it. This script will change the category to a list of products.
  2. Switch to Spoon.
  3. Open the Transformation created in the last tutorial and run it.
  4. Explore the js_dw database again. Right-click on the lk_puzzles table and click on View SQL. Modify the proposed statement so that it looks like the following:
SELECT   * 
FROM     lk_puzzles 
WHERE    id_js_man = 'JUM'  
ORDER BY id_js_prod 
       , version
  1. You will see this:
Previewing data in a Type II Dimension

In the final preview, you can observe that there are products with more than one version. Take as an example the product with the name A Gondola Ride in Venice. Before 2017/10/01, the puzzle was classified as Famous Landmarks. After that date, the new classification is Caribbean.

Let's analyze how this update process worked.

As explained before, when a record arrives at the Dimension L/U step, a lookup is made by the keys in the first grid, and also using the period of validity. For this particular product, the lookup succeeds. There is a record for which the keys match and the period from start_date to end_date of the record 01/01/1900 to 31/12/2199 contains the value of the Stream Datefield, that is, 01/10/2017.

Once found, the step compares the fields you put in the Fields tab, name and theme, in the dimension table against pro_name and pro_theme in the incoming stream.

As there is a difference in the theme field, the step inserts a new record and modifies the current—it changes the validity dates and sets the current flag to false. Now this puzzle has two versions in the dimension table. These update and insert operations are made for all records that changed.

For the records that didn't change, the step finds records in the dimension table but as nothing changed, nothing is inserted nor updated.