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, | -1 | -10 | 3 |
| RateID = 5 | 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.