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:

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:
   
   
   
   
2. Enable history on the table by right clicking it and click Advanced Settings.
   
   
   
   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:
   
   
   
   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.
   
   
   
   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.