Automated Surrogate Key "Unknown Bucket" Transforms from History Enabled Table

Some features have been added recently to automatically add the joins necessary for handling SCD Type 2 lookups from a History Table.

I'd like to suggest another enhancement which would save a ton of time and manual clicking when we're adding Surrogate Keys to fact tables.

Dragging and dropping DW_ID from a history table to a fact table is easy.  But the process of adding the Fixed transformation for a -1 "Unknown Bucket" requires a lot of clicks, is error prone, and is something I despise doing.

I'd like to suggest the following enhancement to the new dialog that appears when dragging a field from a history enabled table.  Note that this option should only be available if dragging DW_ID.


A simple checkbox to add the surrogate key "unknown bucket" transform fixed transformation and also add the Is Empty condition:

There have been several times where developers have clicked on the wrong field for the condition, or forget to add the condition, so this would be a great help.

Also, try adding the transform and the condition 25 times for 10 fact tables.  Your mousing hand will quickly agree with my suggestion.  I promise!



1 comment

Please sign in to leave a comment.