Building a Transactional Delta Table with SAP Data Services

What is a transactional delta table?

A transactional delta table is a way to look back at the history of your data and determine what changes have been made to a particular transaction over time. It will show when a transaction was initially created, and then how and when that transaction changed after the initial creation. Building one with SAP Data Services is a simple process that I’ll outline using a fact table containing invoice data.

There are a few important components to a delta table. The first is that nothing is ever deleted from a delta table regardless of what happened in the source transactional table. Instead a “negation” record is inserted into the delta table which effectively zeroes out the sum of the value fields for that record. For example, if I have an invoice for 10 widgets that gets deleted from the source table, I will insert a new record into the delta table that contains the key information for that invoice as well as the negative quantity of the original record. In this case, that will be a record with the same invoice number and line item and a quantity of -10. If I were to perform a query of the delta table that sums up the quantity for that invoice, the sum of the two records will return zero.

Similarly, if a change occurs to a record I need to do two things for the delta table: insert a new record with the updated information of that record and then insert a “negation” record. If in this previous example I changed the quantity of the invoice from 10 widgets to 5, I will need to insert a new record with a quantity of 5 and insert a negation record with a quantity of -10. Performing a query on the delta table to sum up the quantity on that invoice will return the sum of those three records, which is 5.

So what is the point of constructing a delta table? The real beauty comes into play when a record in the source table has been changed multiple times. A transactional table such as an invoice table will commonly carry a “Last Update Date” field, but that doesn’t tell you how many times that record was changed or what exactly was changed on it. A delta table can give you both of these things to a large degree. So, the second component to a delta table is a date field that signifies the insertion date of the delta record into the delta table. Typically a delta table will be appended to about once per day, so either the “Last Update Date” field can be used from the source or the ETL process can generate the date and time that the process was run and use that in the record. It is important to note that the records being inserted into the delta table carry all changes that occurred in the source table since the last time the process was run to append to the delta table. So while the delta table won’t carry every change that occurred in the source, when run on a schedule it will contain the changes that have occurred to the record since the last scheduled run. This is insightful and powerful information when the process runs regularly.

To download full PDF and Continue Reading…

About Rich Hauser 
Rich is a senior business intelligence consultant specializing in Enterprise Information Management. He has delivered customized SAP BusinessObjects solutions for customers of all sizes across a variety of industries. With Decision First Technologies, Rich utilizes SAP Data Services and SAP Information Steward.