To homepage To homepage
Solved

Onetime MDW historical table map

  • 2 November 2023
  • 4 replies
  • 64 views
K
Rank

Our client has a question about a historical table in TimeXtender. They would like to know if they can make a onetime update from SSMS to populate historical records. More details on the scenario below.

Scenario:

  • Table in question is in MDW
  • An ID merge has been done to collapse users that have created multiple accounts into on true account. Logic is done in DSA and flows to MDW.
  • The new merged id was inserted as a new field in the MDW table, the old id was renamed.
  • New merged id field is null for historical records where old id is populated. This makes historical queries fail.

Question: Can they run a one time stored procedure in SSMS to fill in the historical records to the new merged id field? What are the potential drawbacks of this solution?

icon

Best answer by Thomas Lind 3 November 2023, 13:10

View original

4 replies

rory.smith
Rank
Userlevel 5
Badge +7

Hi @kgoddard ,

 

you can do this to backfill changes to older history. I would potentially consider using the Customize Code feature to have a look at the cleanse procedure generated by TimeXtender to evaulate whether you want  to also update the technical hashes for Type 1 and Type 2 columns. In practice I don't think you would have to, but you are changing the content of either of those and therefore also changing the hash from the value it used to have.

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi @kgoddard 

You should in general not update fields in your databases outside TimeXtender. Or to say it a different way it is not really something we can fix inside TX if it suddenly looks incorrect or ruins other tables.

In this instance you should start by being sure the fields and table is setup correctly in to work correctly as a history table. So that means you need to use these two settings.

If you haven’t done it yet, but already added the field I don’t know if it would make a difference to add them now, but if you had before or when you added the new field it would have updated all the values so they weren’t null even if it is a type II field.

If you have no other choice be sure to run backups of your repository and the DWH database before running it.

You can also run it inside TimeXtender using a Update query in a script action that is set to run as post data cleansing rules step.

 

rory.smith
Rank
Userlevel 5
Badge +7

Hi @Thomas Lind 

if you use the settings to update historical entries for Type 1 and Type 2, this is a setting you only keep on for the moment you are loading the new column right? Otherwise any future changes within existing columns will also be applied to your history records.

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi @rory.smith 

It should only be added when you want it to behave like so. The Update also historical records with new values on type I change option does as explained.

You can turn them off after I guess.

I don’t see it as a too big worry. If it is an issue that fields are being updated with another value due to this they should be a type II anyway.

This feature only works on Type I fields, the other only works on type II fields that has null values, but once they are without that they don’t affect them.

Both are an option to consider every time you do changes to an history table.

Also it should be mentioned that if your table is running as incremental load as well, it will not do this update before you do a full load of the history table.

Reply


Terms and ConditionsCookie settings