How To Implement Delta Load in ODE v4 - OptimalBI/optimal-data-engine-mssql GitHub Wiki

ODE supports two types of data loads, Full load and Delta load. The difference is there are no deletions in Delta mode. So that Delta loads could be used for quick loads of small datasets as opposed to long overnight Full loads where full datasets are checked for new, updated and deleted records.

One source table could support both types of load by using load type input parameter of the source stored procedure. In usual cases if the switch between load types is not required, source stored procedure doesn’t require input parameter implementation.

USE [ODE_Stage]
GO
 
CREATE PROCEDURE [stage].[usp_Sales_Customer]
    @Load_Type varchar(50)
AS
BEGIN
 
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = object_id(N'[Stage].[Sales_Customer]') AND type IN (N'U'))
    DROP TABLE [Stage].[Sales_Customer]
 
SELECT [CustomerID]
      ,[PersonID]
      ,[StoreID]
      ,[TerritoryID]
      ,[AccountNumber]
      ,[ModifiedDate]
INTO [Stage].[Sales_Customer]
FROM [AdventureWorks].[Sales].[Customer]
WHERE [ModifiedDate] >= CASE WHEN @Load_Type = 'Delta' --Values are Delta and Full
                THEN DATEADD(HOUR, -25, GETDATE()) --load changes for the last 25 hours for Delta
                ELSE '1900-01-01' END
END
GO

Create hub and satellite as usual. Change the default parameter of the current version of the source table:

UPDATE [dbo].[dv_source_version]
SET [pass_load_type_to_proc] = 1
WHERE [source_table_key] = 3 -- your source table key
AND is_current = 1

Now this table could be scheduled in two different loads. Let’s say we have created two schedules already:

USE [ODE_Config]
GO
EXECUTE [dv_scheduler].[dv_schedule_insert]
   @schedule_name          = 'Full_Load'
  ,@schedule_description   = 'Runs all Loads which only need to be refreshed daily'
  ,@schedule_frequency     = 'Daily'
  ,@release_number         = 2017010901
GO
EXECUTE [dv_scheduler].[dv_schedule_insert]
   @schedule_name          = 'Delta_Load'
  ,@schedule_description   = 'Runs all Loads which need to be refreshed hourly'
  ,@schedule_frequency     = 'Hourly'
  ,@release_number         = 2017010901
GO

Add source table to both schedules:

EXEC    [dv_scheduler].[dv_schedule_source_table_insert]
@schedule_name = 'Full_load'
,@source_unique_name = 'Sales_Customer'
,@source_table_load_type = 'Full'
,@priority = 'Low'
,@queue = '001'
,@release_number = 2017010901
GO
 
EXEC    [dv_scheduler].[dv_schedule_source_table_insert]
@schedule_name = 'Delta_load'
,@source_unique_name = 'Sales_Customer'
,@source_table_load_type = 'Delta'
,@priority = 'Low'
,@queue = '001'
,@release_number = 2017010901
GO