To homepage To homepage
Solved

Changing PK on history table not affecting surrogate hashkey?

  • 8 April 2024
  • 7 replies
  • 47 views
J
Rank

Good morning All,

We are encountering the following:
We have history tables that use the DW_SourceCode as part of the primary key. This is because we have multiple sources with the same structure which are merged together in one table after ODX. As primary keys can overlap in the different sources, we need the DW_SourceCode to make distinguish the data coming from one or other source. Also we need history as the sources have a tight cleanup system and we need to keep track of information ourselves.

 

While developing some of the tables, the DW_SourceCode was somehow not included in the primary key in the first deployment and this was corrected afterwards. TimeXtender also reflects this:
 

Although deployment was successful, the computed column definition of the surrogate hashkey was not altered:

 

The problem we're having now is that the history is matched based on the surrogate hashkey. Now wrong records are updated (type 1 history) because the hashkey matches but actually shouldn't because the DW_SourceCode is different. Unfortunately i'm not able to share any actual data.

Is this known, and expected behavior? And what would be the best solution to solve this? I'm not able to re-deploy the tables as it doesn't update the surrogate hashkey definition.
 

icon

Best answer by rory.smith 8 April 2024, 12:00

View original

7 replies

rory.smith
Rank
Userlevel 5
Badge +7

Hi @JogchumSiR ,

if you only changed the PK definition and not the Natural key definition then this is expected behaviour. Did you add the field to your Natural key for history?

J
Rank

Hi @rory.smith ,

That's the strange thing. At first i thought this was the problem. But system fields are not available to include in the natural key. But still we have other tables that have the DW_SourceCode included in the surrogate hashkey definition although the natural key doesn't include it. See below for another example:
 

History settings:
 

Surrogate Hashkey Definition:
 

[SCD Surrogate Hash Key]  AS (CONVERT([varbinary](20),hashbytes('SHA1',((coalesce(CONVERT([varbinary](20),[_pk_Accountmanager]),0x00)+0x0001)+coalesce(CONVERT([varbinary](15),[DW_SourceCode]),0x00))+0x0002))) PERSISTED NOT NULL,

 

rory.smith
Rank
Userlevel 5
Badge +7

Hi,

this is 20.10.x I take it?

Perhaps it is useful to make a new field with a Custom Transformation to copy the value of DW_SourceCode there. I agree this behaviour sounds incorrect (perhaps log a support ticket) but that should allow you hopefully to apply the “normal” setp of steps.

J
Rank

I'm not sure if that's the cloud version. The version i know is installed is 6536.1
 

I know we've had issues before with the DW_SourceCode in the natural key as described in this topic:

https://support.timextender.com/topic/show?tid=1599&fid=90.

Your suggestion does indeed work, but i don't like it. It means adding another field with the same value, and altering all our history table (with many records in it so that will take very long).

@Thomas Lind i see you writing in your answer in the above mentioned forum post that the ability to add the DW_SourceCode in the natural key of the history settings will ‘likely a feature change we could have’. Is that something that is on any backlog somewhere to develop? Since the current behavior is somewhere in between.

rory.smith
Rank
Userlevel 5
Badge +7

Hi,

new / cloud release is 6xxx - previous is 20.10.x.

I guess that fix has not materialised yet. I think you would always need to redeploy to include that field in the PK and natural key as the field was not part of the key. I think there are three work-arounds (ordered from least desirable to most):

  • override the cleanse code to change the natural key, this could work but means you need to maintain all the cleanse code into the future. I would avoid this option at all costs but may be neccessary under extreme pressure in other situations.
  • table query in the ODX: no extra copy of the system field data, but structure changes need to be maintained by hand.As this is a history table, you would want to keep tight control anyway so I don't think this is as much of an issue. This option is more hidden and I would avoid it for that reason.
  • extra field to hold the system field data, this copies the data and is therefore less efficient. It is the easiest to maintain and understand.

I think it would be good to create an Idea to hold the ‘proper’ solution to this so that we can upvote and get it included in the near fuiture.

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi @JogchumSiR 

Did you see this community post.

I tried to use a supernatural key as the natural key and it had the DW_SourceCode as part of it.

J
Rank

@rory.smith : thank for your workout! I will discuss with our TimeXtender partner. In my current insights in the cleansing code is that the build up of the natural key resides in the definition of the computed column ‘SCD Surrogate Hash Key’. The cleansing code uses this surrogate key.

We can fix it by dropping and creating the column with the right definition, but that will take some figuring and, and still relying on the software to create the surrogate key as we want it ‘by accident’. Adding an extra column and include that in the natural key would be the best i think. 

I will create an idea for my ‘perfect world solution’.

@Thomas Lind yes I've seen it, I mentioned it myself. In that specific case it was only to mitigate a coding bug with the DW_SourceCode and the checkbox ‘Create a new record when a deleted record reappears in the source’. This time it is not specifically a problem with the type 2 history, but with history in general.

In short; If we add the DW_SourceCode to the primary key collection after the first deployment, the change is not included in the definition of the natural key (which is stored in the database column ‘SCD Surrogate Hash Key’ in my understanding. Maybe it is not designed to be included at all, but then the software still has a bug in my opinion. If you want me to show it, we can have a call maybe?

Reply


Terms and ConditionsCookie settings