Use CDC Code (Cyclic Redundancy Checksum) for Change Data Capture

转载---

http://bisherryli.wordpress.com/2010/08/04/use-cdc-code-cyclic-redundancy-checksum-for-change-data-capture/

Without the CDC features in some newer version RDMSs, we have several other options to capture the changing data.

1) I’ve used the native natural key value alone from the source for comparison. If the value is different, then we need to create a new surrogate key in the dimension table for the new or changed record.

2) I’ve also used the native surrogate key from source plus the last update timestamp from source for comparison. If there is a new native surrogate key or there is a change in the last update timestamp, then we need to create a new surrogate key in the dimension table for the new or changed record.

3) For a very wide dimension table, comparing every field is impractical and my ETL process will soon become messy.

Here is a design tip from Kimball Group using CDC code.

Here’s a technique that accomplishes this comparison step at blinding speeds and has the added bonus of making your ETL program simpler. The technique relies on a simple CRC code that is computed for each record (not each field) in the incoming customer file.

Here are the processing steps:
1. Read each record of today’s new customer file and compute that record’s CRC code.
2. Compare this record’s CRC code with the same record’s CRC code from yesterday’s run,
which you saved. You will need to match on the source system’s native key (customer ID) to make sure you are comparing the right records.
3. If the CRC codes are the same, you can be sure that the entire 100 fields of the two records exactly match. YOU DON’T HAVE TO CHECK EACH FIELD.
4. If the CRC codes differ, you can immediately create a new surrogate customer key and place the updated record in the data warehouse customer dimension. This is a Type 2 slowly changing dimension (SCD). Or, a more elaborate version could search the 100 fields one by one in order to decide what to do. Maybe some of the fields trigger an overwrite of the data warehouse dimension record, which is a Type 1 SCD.

CRC stands for Cyclic Redundancy Checksum and it is a mathematical technique for creating a unique code for every distinguishable input. The CRC code can be implemented in Basic or C. Most introductory computer science textbooks describe the CRC algorithm. Google for "CRC code" or "checksum utility".