Dimensional data that change slowly or unpredictably are captured in Slowly Changing Dimensions (SCD) analyses. In a data warehouse environment, a dimension table has a primary key that uniquely identifies each record and other pieces of information that are known as the dimensional data.

All the update methods for the different SCD types are accomplished using the SortCL program in IRI CoSort. In IRI Voracity there is a wizard to aid in creating the SCD job scripts used when running the SortCL program. Most SCD types use a full outer join to match records from the original data source with records in the updated source based on equating a key from each. Type 4, however, accomplishes the update using a sort.

Type 4 SCD

The Type 4 model is similar to that for Type 2. The difference is that there are 2 tables or files that are maintained: one for the current costs and one to hold the history records for the costs. When new current records are added to the master, these new records are usually added to the history at the same time.

The field definitions are the same in the update, the history and the master files or tables. We will sort the records for the update and history together. A new master will be created that only has one record for each ProductCode and that record will be the most current. The history will have all the records for each ProductCode including the ones from the update source.

The starting master table will be the same as the starting master from our example of Type 1 with values as shown below.

ProductCode	Cost	StartDate
C123	125.50	20110228
F112	2365.00	20120101
G101	19.25	20110930
J245	450.50	20110430
S022	98.75	20110515

All the update data will all have the same StartDate. The update.dat source contains records with the following values:

ProductCode	Cost	StartDate
F112	2425.00	20120701
J245	550.50	20120701
M447	101.75	20120701
S022	101.75	20120701

The starting history source below is like the starting master in the Type 2. So Type 4 is a combination of Type 1 and Type 2 where the master is the same as a Type 1 master and the history is the same as a Type 2 master.

ProductCode	Cost	StartDate
C123	125.50	20110228
F112	2365.00	20120101
G101	19.25	20110930
G101	21.25	20110501
J245	450.50	20110430
J245	425.25	20101001
J245	385.25	20100215
S022	98.75	20110515

In IRI Workbench, there is a Voracity wizard to assist in the creation of scripts for updating Dimensional files and tables. This wizard is located in the Voracity dropdown on the navigation bar. First you pick the SCD type. Then the window where you select the sources that are used for processing the update is displayed.

The next screen is where you set up the sort by selecting the keys. There are 2 keys: ProductCode then StartDate. We are grouping by ProductCode, but we also need to sort in descending order by StartDate within each ProductCode grouping. This is so that we can filter the most recent record for each ProductCode group to the new master.

To make the StartDate a key that sorts in descending order, select that key and then select Edit Key. This will bring up the following screen:

In the dropdown for Direction select Descending.

Here is the job script for the sort:

/INFILE=C:/IRI/CoSort95/workbench.orig/workspace/SCD/SCD4/history4.dat
    /PROCESS=DELIMITED
    /ALIAS=history4
    /FIELD=(PRODUCTCODE, TYPE=ASCII, POSITION=1, SEPARATOR=",", FRAME='\"')
    /FIELD=(COST, TYPE=NUMERIC, POSITION=2, SEPARATOR=",", FRAME='\"')
    /FIELD=(STARTDATE, TYPE=ASCII, POSITION=3, SEPARATOR=",", FRAME='\"')
/INFILE=C:/IRI/CoSort95/workbench.orig/workspace/SCD/update.dat
    /PROCESS=DELIMITED
    /ALIAS=update
    /FIELD=(PRODUCTCODE, TYPE=ASCII, POSITION=1, SEPARATOR=",", FRAME='\"')
    /FIELD=(COST, TYPE=NUMERIC, POSITION=2, SEPARATOR=",", FRAME='\"')
    /FIELD=(STARTDATE, TYPE=ASCII, POSITION=3, SEPARATOR=",", FRAME='\"')

/SORT
    /KEY=(PRODUCTCODE, TYPE=ASCII)
    /KEY=(STARTDATE, DESCENDING, TYPE=ASCII)

/OUTFILE=history4.dat
 # This file will contain all the records from both inputs
    /PROCESS=DELIMITED
    /FIELD=(PRODUCTCODE, TYPE=ASCII, POSITION=1, SEPARATOR=",", FRAME='\"')
    /FIELD=(COST, TYPE=NUMERIC, POSITION=2, SEPARATOR=",", FRAME='\"')
    /FIELD=(STARTDATE, TYPE=ASCII, POSITION=3, SEPARATOR=",", FRAME='\"')

/OUTFILE=master4.dat
# Include only one record for each ProductCode
    /PROCESS=DELIMITED
    /FIELD=(PRODUCTCODE, TYPE=ASCII, POSITION=1, SEPARATOR=",", FRAME='\"')
    /FIELD=(COST, TYPE=NUMERIC, POSITION=2, SEPARATOR=",", FRAME='\"')
    /FIELD=(STARTDATE, TYPE=ASCII, POSITION=3, SEPARATOR=",", FRAME='\"')
    /INCLUDE WHERE PRODUCTCODE

Below is the updated master produced by running the above job script using SortCL. The values are the same that are produced in a Type 1 update.

ProductCode	Cost	StartDate
C123	125.50	20110228
F112	2425.00	20120701
G101	19.25	20110930
J245	550.50	20120701
M447	139.25	20120701
S022	101.75	20120701

The history file contains all previous records plus the new records from the update file.

ProductCode	Cost	StartDate
C123	125.50	20110228
F112	2425.00	20120701
F112	2365.00	20120101
G101	19.25	20110930
G101	21.25	20110501
J245	550.50	20120701
J245	450.50	20110430
J245	425.25	20101001
J245	385.25	20100215
M447	101.75	220120701
S022	101.75	20120701
S022	98.75	20110515