In the previous post we started with the generic steps in OSDM to create a historic version of your relational model. This post will explain the next four steps we need to create a Head Version Model.

1. then create a new relational model with one table that contains the ‘default columns’
2. then forward engineer the surrogated logical model to the new relational model and use the table with the ‘default columns’ as template
3. split the tables using the split table wizard to get the correct tables
4. extend the primary key of the ‘version/history’ tables

The new relational model will be named ‘example_head_version’. I will add a ‘defaultcolumns’ template table with the columns:

• dwh_valid_from, the start date of the version validity
• dwh_valid_to, the end date of the validity
• dwh_status, the status of the record. I think it is a kind of a ‘valid’ or ‘deleted’ / ‘voided’ indicator to get a continuous time line
• dwh_source, the original source of the record

The first three columns should land in the ‘Version’ tables and the last one lands in the ‘Head’ tables.
1. Create a new relational model with one table that contains the ‘default columns’

Now that we have a new relational model with the ‘defaultcolumns’ template table we can forward engineer the logical model.
2. then forward engineer the surrogated logical model to the new relational model and use the table with the ‘default columns’ as template

The forward generated relational model now looks like this:

Now we can start with splitting the tables via the spit table wizard. We split of the ‘Version’ tables and the remaining part is then the ‘Head’ table. The ‘Head’ tables contain the business key and the attributes that are not going to change over time. The ‘Version’ table contains the dynamic or changing attributes over time.
3. split the tables using the split table wizard to get the correct tables

Split of the product version table.

The product table has no foreign keys so we go on to the next step in the wizard to move the ‘dynamic’ Price attribute and the ‘defaultcolumns’ needed for the ‘Version’ table.

Split the Interest version table.

The Interest table’s foreign keys are part of the business keys and have to stay in the ‘Head’ table. They are not dynamic and do not change over time! Let’s go on with the attributes.

Now the Interest version table is created we go on with the Employee table.
Split the Employee version table.

In the Employee table the foreign keys are not part of the business key and are qualified as dynamic. We have to add them to the ‘Version’ table to track the changes. Let’s go on and add the dynamic attributes.

As you can see I left the Gender attribute in the ‘Head’ table, because I assume it does not change. The table split of the Department table is similar to the split of the Product table.
Finally we are at the last step.
4. extend the primary key of the ‘version/history’ tables
I’ve also added some classifications with some coloring. Then the resulting model is:

Looks OK to me 🙂 The only thing left is to show the mappings from the ‘source’ model to the ‘history’ model.

The mappings on table level for Employee:

The mappings on attribute level for the business key of Employee:

And finally the mappings for a dynamic attribute of Employee:

The resulting model is stored in the branch head_version in this Github repo.
Nice, we have now mappings from source to target relational model via the logical model. Just some steps away of generating ETL based on these mappings. Next post the ‘regular’ Anchor Vault. Even more splitting of tables 😉