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