ETL Incremental Processing Summary

1 Log Table 1.1 ideas

A log table is used to record the primary key of a table Yw_tablea the changed data in the Business library. Before the data enters the BI Library target table Bi_tablea, delete is based on the primary key recorded by the log table.

1.2 Design 1.2.1 Log table structure

CREATE TABLE  LOG (  varchar),  -- primary key 1  VARCHAR(20 ),  - - primary key 2  VARCHAR,  - - source table   updatedate Date,  -- update date  loaddate-  - Load Date );

1.2.2 ETL Process

• Yw_tablea changes in the data, the primary key deposit log, all the columns into the BI Library temporary table Tmp_bi_tablea (Figure 1);
• Delete the existing data in the BI library Bi_tablea based on the log table (Figure 2);
• Tmp_bi_tablea data into Bi_tablea (Figure 2).

Figure 1 Business data entry log and temp table

Figure 2 target table initialization and staging table data entry targets

1.2.3 attached section SQL

-- target Table Initialization Delete  from where exists (Select1fromwhere= tmp.key1)

2 left correlation (key pair, full table alignment) 2.1 Ideas

Business Library A table Yw_tablea left associated BI Library Bi_tablea, can be associated with the discard, the association does not go into the target table Bi_tablea, and then the same business primary key more than one processing (marked or delete). More suitable for small-dimension table updates.

2.2 Design 2.2.1 Target table structure

 create   Tabel Bi_tablea (Physical_key  int  identity , --  physical key, self-increment  logical_key varchar  ( Span style= "color: #800000; Font-weight:bold; " >20 ),  business key  col1 varchar  (--   other columns  nowstate char  (1 ),  status  loaddate DATE --  load date ) 

2.2.2 ETL Process

• Yw_tablea and Bi_tablea primary key or multi-column comparison, obtains the need to enter the target table the record, enters the target table Bi_tablea;
• The target table Bi_tablea data processing, deletion, or status bits.

Figure 3 Association Design

2.2.3 attached section SQL

--Target table status update (same business key record kept up-to-date)UpdateBi_tableaSetNowstate='0' wherePhysical_key not inch(Select Max(Physical_key) fromBi_tableaGroup  byLogical_key having Count(Physical_key)> 1  andNowstate='1')

ETL Incremental Processing Summary