Skip to main content
To homepage To homepage
Solved

Onetime MDW historical table map

K
Visitor

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?

Best answer by Thomas Lind

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.

 

View original
Did this topic help you find an answer to your question?

4 replies

rory.smith
TimeXtender Xpert
Forum|alt.badge.img+7
  • rory.smith
  • TimeXtender Xpert
  • 649 replies
  • November 3, 2023

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.

Christian Hauggaard
amartinez
2 people like this
  • Christian Hauggaard
    Christian Hauggaard
  • amartinez
    amartinez
Thomas Lind
Community Manager
Forum|alt.badge.img+5
  • Thomas Lind
  • Community Manager
  • 1015 replies
  • Answer
  • November 3, 2023

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.

 

amartinez
1 person likes this
  • amartinez
    amartinez
rory.smith
TimeXtender Xpert
Forum|alt.badge.img+7
  • rory.smith
  • TimeXtender Xpert
  • 649 replies
  • November 3, 2023

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
Community Manager
Forum|alt.badge.img+5
  • Thomas Lind
  • Community Manager
  • 1015 replies
  • November 3, 2023

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.

rory.smith
amartinez
2 people like this
  • rory.smith
    rory.smith
  • amartinez
    amartinez

Reply


Most helpful members this week

Terms and ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings