heureka ModGen: Getting Started Part V - The Surrogate Key

For the fifth (and last) part of this series we would like to look into the characteristics of the surrogate keys in a more detailed way. I have already mentioned this topic in my previous post (I believe it was part III). Now I would like to dig deeper into this subject to get you to understand the way heureka ModGen is generating those keys and what possibilities it offers for their administration.

Surrogate keys are artificial database keys that cannot be deduced from the data of the tables. For the data vault „standard“, every „hub“ and „link“ must have such a key (By the way, I just learned to be very patient speaking of a „standard“ when it comes to data vault).

Objekt types of a data vault model

This graphic illustrates the surrogate key – „HUB_ZipCode_SID“ of the table „HUB_ZipCode“ (here: dark green). The link however, (bright green) has its own surrogate key in form of the column „LINK_ZipCode_SID“. Sat tables are the only ones that do not need such a key, because it is being inherited by the associated hub.

So, what we will be doing in the following is having a look into how heureka ModGen creates and applies those keys, determines the correct data type and other attributes.

To that end we have two possible methods available, that I would like to look at in the following.

1.    The HKEY
When heureka ModGen is generating a surrogate key it requires a template. We have gone without integrating a default, for it would be very unlikely that this would satisfy the requirements of the users. Instead, heureka ModGen will output a fault for each table that does not contain a template for a surrogate key.

One possibility for providing heureka ModGen with the template is the column type „HKEY“.

Column populated with an HKEY

Here we have a table within our staging model, that has been expanded by the column „SurrogatTemplate“ which is populated with the MODGEN_CLUMN_TYPE „HKEY“. This means, the column is only being used as a template for the hub that is being generated out of that table.
Therefore, the HKEY allows us to determine a separate surrogate key for each hub that is being generated.

Assuming all our surrogate keys dispose of the same attributes in the rawVault model, this method would be a very time consuming one, which is why we have implemented a second option for that purpose.

2.    Domain as template for the surrogate key
Instead of applying a single HKEY field for every table in the source model we can here choose a domain in the advanced options before starting the generation. This domain will act as a template for all surrogate keys in the target model.

This topic has already been addressed in part I and IV.

Advanced Options - heureka ModGen

The here listed domains that are available for generating the surrogate key are being drwan from the template model. The way they can be created and kept up to date has already been described in part IV of this series.

What is important to know is that heureka ModGen is capable of processing both options in one source model. It is first being checked, whether an HKEY exists or not. Only if this shows as false, the advanced option and consequently the selected domain is being accessed. If none of the options applies, the object is being excluded from the generation process. If this is the case, an error will be shown in the data vault compare dialogue.

Now we finally have reached the end of our little series „Getting Started with heureka ModGen“. I deeply hope it helps you to get around as for now. This will definitely not be the last post for addressing the topic data vault, so keep up visiting our blog!

We will be happy to hear any feedback, criticism or questions. Just comment this post or directly contact us via support@heureka.com.

Until then, thanks for reading and cheers!