Change data capture. Practice experience. Part 2.

Continue speaking about Change data capture I would like to start from the detailed description of the tables with business data database structure.

The structure looks like:

Just few words about each table:

  • ABSMainRate– for every change in users’ database cause new record in this table.
    • REV – is the version number of each record. E.g. – if users made three changes to one record, in the OLAP ABSMainRate table would be three records, showing the history of all changes. REV would be 1, 2, and 3.
  • ABSMainRate_History– history tables with all needed for reports fields and calculations.
    • For each record in ABSMainRate (except record with REV=1) there are two records in ABSMainRate_History. RecordCount has value 1 for history record with current values, and -1 for record with previous values.
    • RatePlan and Rate Fact – for current record (RecordCount=1) has current values of rates, for previous record (RecordCount=-1), minus values of previous rates.

Just simple example of data updates to illustrate how history table must be filled:

Changes in users’ database RecordCount in OLAP database RateFace in OLAP database RateID in OLAP database
New record:

RateFact = 10, RateID = 3

1 10 3
Change:

RateFact = 15, RateID = 5

-1 -10 3
1 15 5
  • Current_ABSMainRate – the same structure as History table, but contains only current state of each record.

So, I need to fill all these three tables from users’ database with the help of Change data capture. In users’ database I have only one table in which all information is updated.

The beginning of the update OLAP procedure for Main tables will be the same as for ref. books:

— Get the LSN till synchronization was made the last time
      select @save_to_lsn = t.LastSLN from BIWarehouse.dbo.Service_WarehouseConfig t with(xlock) where ID = ‘Doc_ABSMainRates table’

      — If the parameter is not defined, the synchronization is made for the first time and we take the minimum value.
  if @save_to_lsn is null begin
            select @from_lsn = sys.fn_cdc_get_min_lsn(‘dbo_Doc_ABSMainRates’)
      end
      — In other case, we the the LSN value following the saved one
      else begin
            set @from_lsn = sys.fn_cdc_increment_lsn(@save_to_lsn)
      end

      — Get the maximum LSN value.
      SET @to_lsn = sys.fn_cdc_get_max_lsn()

Next, as for ref. books I start processing:

— Start processings only if FROM values less then TO value
if @from_lsn < @to_lsn begin
            — Create table varaible to insert into it data from Change data capture.
            Declare @TableAnalysis table (
                  Operation int,
                  ID int,
                  LocationID int,
                  BusinessUnitID int,
                  RateID int,
                  RateDate datetime,
                  RatePlan int,
                  RateFact int
            )

From here the procedure will change from one we have in previous post. The procedure will be divided into two parts: first we have to perform actions for new and deleted records and then in SQL cursor create records for update operations.

In will be quite simple for new records as we have just to insert new records in each of three tables in correct order (not to break foreign keys).

Declare @IsError bit = 0
 
            begin try
                  — Insert only new and deleted records.
insert into @TableAnalysis (
                        Operation, ID, LocationID, BusinessUnitID, RateID, RateDate, RatePlan, RateFact
                  )    
                  select __$operation, ID, LocationID, BusinessUnitID, RateID, RateDate, RatePlan, RateFact
                  from  cdc.fn_cdc_get_all_changes_dbo_Doc_ABSMainRates (@from_lsn, @to_lsn, ‘all update old’)
                  where __$operation in (1, 2)
            end try
            begin catch
                  select @IsError = 1
            end catch
           
            If (@IsError = 0) begin

                  ———————————————————-
                  — INSERT NEW RECORDS INTO OLAP TABLES
                  ———————————————————-
                  — Insert records into ABSMainRates table
insert into BIWarehouse.dbo.ABSMainRates (
                        __LastUpdatedTime,
                        ABSMainRateID,
                        Rev
                  )
                  select GetDate(), ID, 1
                  from @TableAnalysis t
                  where t.Operation = 2
 
— Insert new records into Current_ABSMainRates
insert into BIWarehouse.dbo.Current_ABSMainRates (
                        __LastUpdatedTime,
                        ABSMainRate,
                        Date,
                        Location,
                        BusinessUnit,
                        Rate,
                        ChangedBy,
                        RatePlan,
                        RateFact,
                        RateDate
                  )
                  select GetDate(), BIabs.__ID, convert(date, GetDate()), loc.__ID, bu.__ID, rate.__ID, users.__ID, docABS.RatePlan, docABS.RateFact, docABS.RateDate
                  from @TableAnalysis docABS
                  left join BIWarehouse.dbo.ABSMainRates BIabs on BIabs.ABSMainRateID = docABS.ID –- Get ID’s of just inserted records
                  left join BIWarehouse.dbo.Ref_Location loc on loc.LocationID = docABS.LocationID
                  left join BIWarehouse.dbo.Ref_BusinessUnit bu on bu.BusinessUnitID = docABS.BusinessUnitID
                  left join BIWarehouse.dbo.Ref_Rate rate on rate.RateID = docABS.RateID
                  left join BIWarehouse.dbo.Ref_User users on users.Name = ‘System’
                  where docABS.Operation = 2

          — Insert new records into ABSMainRates_History table
insert into BIWarehouse.dbo.ABSMainRates_History (
                        __LastUpdatedTime,
                        RecordCount,
                        ABSMainRate,
                        Date,
                        Location,
                        BusinessUnit,
                        Rate,
                        ChangedBy,
                        RatePlan,
                        RateFact,
                        RateDate
                  )
                  select GetDate(), 1, BIabs.__ID, convert(date, GetDate()), loc.__ID, bu.__ID, rate.__ID, users.__ID, docABS.RatePlan, docABS.RateFact, docABS.RateDate
                  from @TableAnalysis docABS
                  left join BIWarehouse.dbo.ABSMainRates BIabs on BIabs.ABSMainRateID = docABS.ID –- Get ID’s of just inserted records
                  left join BIWarehouse.dbo.Ref_Location loc on loc.LocationID = docABS.LocationID
                  left join BIWarehouse.dbo.Ref_BusinessUnit bu on bu.BusinessUnitID = docABS.BusinessUnitID
                  left join BIWarehouse.dbo.Ref_Rate rate on rate.RateID = docABS.RateID
                  left join BIWarehouse.dbo.Ref_User users on users.Name = ‘System’                
                  where docABS.Operation = 2

Deletion will be much easier.  Thanks to cascade delete, I just have to delete records in ABSMainRates table.

                  ———————————————————-
                  — Delete records from OLAP
                  ———————————————————-
                  delete from BIWarehouse.dbo.ABSMainRates
                  where ABSMainRateID in (
                        select ID
                        from @TableAnalysis t
                        where  t.Operation = 1)

I think that this is enough for today. So I’ll concentrate on processing updated records in OLAP database in the next post. It will be the last post for Changes data capture practice.

Advertisements
This entry was posted in Database development, SQL Server, Technology and tagged , . Bookmark the permalink.

One Response to Change data capture. Practice experience. Part 2.

  1. Pingback: Change data capture. Practice experience. Part 3. | Boris Frolov SQL Server Tips blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s