Follow

How to keep track of historic data

Introduction

Sometimes you would like to retain the information in the data warehouse for your reporting needs even when the records are changed in or deleted from the source system. This can be achieved using the TX history feature.

Scenario

In this scenario, the quotations in the source systems are deleted when they are converted to sales orders or when they are cancelled. We need the information about quotation to calculate a KPI showing us the conversion percentage from Quotations to orders.

The Quotation lines table has the following structure:

Structure

The primary key is a concatenated key based on QuotationID and LineNum.

We will use the history-feature in TX to keep track of the quotation lines, meaning that we would like to insert any new lines created in the source system, update the lines that have been changed and keep the lines that have been deleted.

In other words:

  • A record enters the table with a new combination of QuotationID + LineNum, the record will be inserted.
  • A record enters the table with an existing combination of QuotationID + LineNum, the record will be updated.
  • If a record is deleted in the source table, it will not enter the table and will be kept there either as a historic transaction or as a tombstone.

Step by Step

  1. Make sure that advanced setting are visible in TX. You can change the setting in Tools -> Windows and Menu Settings:

    Windows and Menu Settings

  2. Enable history on the table by right clicking it and click Advanced Settings.

    Table properties

    Enabling history will add a node to the table called History Settings

  3. Click History Settings and use the tree in the right hand side to set up how TX should handle history on the table:

    Tree view

    The Natural Key should match the primary key on the table. This is being used by TX to determine if a record is new or existing.
    If an existing record enters, TX will examine if there are changes in any Type II fields. If there is a change, TX will create a new instance of the record. In this scenario we do not want any type 2 history, so instead TX will do an update of all Type 1 fields on existing records.

  4. Deploy and execute the project. Notice that you will be informed about the deployment of a history enabled table. Click Finish to continue deployment.

    Deployment

    If the table structure is changed at a later time, you will have to consider how TX should handle records already existing in the table.

Notes

History is collected at load time. This means that changes made between loads might not be recorded as history. Consider the following example:

  • An execution of the project is made at 5:00 AM
  • At 8 AM a new Quote is created
  • At 10 AM the quote is turned into an order or is cancelled.
  • At 11 AM another execution of the project is done.

There will be no recording in the data warehouse about the quote created at 8 AM.


This article applies to: timeXtender 4.5, tX2012, TX2014. Last updated: 2014-04-01.

Was this article helpful?
4 out of 4 found this helpful
Have more questions? Submit a request

4 Comments

  • 0
    Avatar
    Morten Søndergaard

    Nice guide.

    How do we handle deleted records? I triede testing it, and it seems that the deleted record stays at the status "SCD is Current" (type II)

    I would like to track changes to my orders on hand. From time to time we delete the orders, how will i ensure that the SCD status wil update in TimeXtender? Will i have to script it?

  • 0
    Avatar
    Tobias Eld

    Thank you for you feedback.

    To track deleted records you click 'Enable Deletes', then the SCD Tombstone field will get the value '1' for records that have been deleted.

    Please note that there is currently a bug regarding this, the bug has been resolved in the beta release of 12.10.0

     

  • 0
    Avatar
    Morten Søndergaard

    Thank you fore quick reply.

    I have tried to test it with the "Enable Deletes" enabled. Stil, Tombstone stays zero for all entries after deleting one line in my source file.

    I'm running version 4.5.19. Is it also a bug in this version?

  • 0
    Avatar
    Tobias Eld

    If it does not work properly in version 4.5.19 I am going to assume that the bug was also present in that version. I would recommend considering upgrading to the latest version when it is released.

Please sign in to leave a comment.